首页新闻找找看学习计划

sql创建视图的索引出错了

0
悬赏园豆:40 [已解决问题] 解决于 2015-10-22 09:38

GO
SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,
    QUOTED_IDENTIFIER, ANSI_NULLS ON;
GO
--Create view with schemabinding.
IF OBJECT_ID ('dbo.mtlevel1', 'view') IS NOT NULL
DROP VIEW dbo.mtlevel1;
GO
CREATE VIEW dbo.mtlevel1
WITH SCHEMABINDING AS
SELECT
COUNT_BIG(*) AS TotalNumber,
Sum(dbo.TT_MT_LOG.MT_TOTAL) AS Demolition,
Sum(CASE dbo.TT_MT_LOG.MT_STATE WHEN '1' THEN 1 END) AS SendSuccess,
Sum(CASE dbo.TT_MT_LOG.MT_STATE WHEN '2' THEN 1 END) AS SendFailed,
Sum(CASE dbo.TT_MT_LOG.MT_STATE WHEN '3' THEN 1 END) AS ReceiveSuccess,
Sum(CASE dbo.TT_MT_LOG.MT_STATE WHEN '4' THEN 1 END) AS ReceiveFailed,
Sum(CASE dbo.TT_MT_LOG.MT_STATE WHEN '5' THEN 1 END) AS ReceiveNoState,
dbo.TT_MT_LOG.APP_PARAMETER1 AS BizCode,
CONVERT (CHAR (10),dbo.TT_MT_LOG.SEND_TIME,120) AS [DAY]

FROM
dbo.TT_MT_LOG
GROUP BY
dbo.TT_MT_LOG.APP_PARAMETER1,
dbo.TT_MT_LOG.SEND_TIME
GO
CREATE UNIQUE CLUSTERED INDEX mt_index ON mlk.dbo.mtlevel1([DAY] DESC,BizCode);
GO

最后一句报错:[SQL]
CREATE UNIQUE CLUSTERED INDEX mt_index ON mlk.dbo.mtlevel1([DAY] DESC,BizCode)
[Err] 42000 - [SQL Server]Cannot create the clustered index 'mt_index' on view 'mlk.dbo.mtlevel1' because the select list of the view contains an expression on result of aggregate function or grouping column. Consider removing expression on result of aggregate function or grouping column from select list.

狗霸人间的主页 狗霸人间 | 初学一级 | 园豆:4
提问于:2015-10-21 16:03
< >
分享
最佳答案
0

说你的视图有聚合函数之类的,无法创建聚集索引。因为你的Day这个列是计算出来的,所以无法索引。

收获园豆:40
幻天芒 | 高人七级 |园豆:36522 | 2015-10-21 16:20

CREATE UNIQUE CLUSTERED INDEX mt_index ON mlk.dbo.mtlevel1(BizCode);

我把它去掉一样的错。

[Err] 42000 - [SQL Server]Cannot create the clustered index 'mt_index' on view 'mlk.dbo.mtlevel1' because the select list of the view contains an expression on result of aggregate function or grouping column. Consider removing expression on result of aggregate function or grouping column from select list.

大概是sum里的值不可以为空 的原因,那一列还不能修改成可以为空,不知道怎么办

狗霸人间 | 园豆:4 (初学一级) | 2015-10-21 16:30

@狗霸人间: 你把Unique关键字去掉哇。

幻天芒 | 园豆:36522 (高人七级) | 2015-10-21 17:24

@幻天芒:
GO
SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,
    QUOTED_IDENTIFIER, ANSI_NULLS ON;
GO
--Create view with schemabinding.
IF OBJECT_ID ('dbo.mtlevel1', 'view') IS NOT NULL
DROP VIEW dbo.mtlevel1;
GO
CREATE VIEW dbo.mtlevel1
WITH SCHEMABINDING AS
SELECT
COUNT_BIG(*) AS TotalNumber,
Sum(dbo.TT_MT_LOG.MT_TOTAL) AS Demolition,
Sum(CASE dbo.TT_MT_LOG.MT_STATE WHEN '1' THEN 1 END) AS SendSuccess,
Sum(CASE dbo.TT_MT_LOG.MT_STATE WHEN '2' THEN 1 END) AS SendFailed,
Sum(CASE dbo.TT_MT_LOG.MT_STATE WHEN '3' THEN 1 END) AS ReceiveSuccess,
Sum(CASE dbo.TT_MT_LOG.MT_STATE WHEN '4' THEN 1 END) AS ReceiveFailed,
Sum(CASE dbo.TT_MT_LOG.MT_STATE WHEN '5' THEN 1 END) AS ReceiveNoState,
dbo.TT_MT_LOG.APP_PARAMETER1 AS BizCode,
CONVERT (CHAR (10),dbo.TT_MT_LOG.SEND_TIME,120) AS days

FROM
dbo.TT_MT_LOG
GROUP BY
dbo.TT_MT_LOG.APP_PARAMETER1,
dbo.TT_MT_LOG.SEND_TIME
GO
CREATE CLUSTERED INDEX mt_index ON mlk.dbo.mtlevel1(days DESC,BizCode);
GO

这样还是行[SQL]
CREATE CLUSTERED INDEX mt_index ON mlk.dbo.mtlevel1(days DESC,BizCode)
[Err] 42000 - [SQL Server]Cannot create nonunique clustered index on view 'mlk.dbo.mtlevel1' because only unique clustered indexes are allowed. Consider creating unique clustered index instead.

狗霸人间 | 园豆:4 (初学一级) | 2015-10-21 17:48

@狗霸人间: 我晕,居然只允许唯一聚集索引。。你试试days不用convert嘛,直接把Send_Time拿出来,用的时候再转嘛。

幻天芒 | 园豆:36522 (高人七级) | 2015-10-21 21:02

@幻天芒: 已经ok了 改了Sum(CASE WHEN dbo.TT_MT_LOG.MT_STATE=5 THEN 1 else 0 END) ,这样就没有为空的了,成功创建

狗霸人间 | 园豆:4 (初学一级) | 2015-10-22 09:13

@狗霸人间: ok,多根据sql执行器的提示来调整。

幻天芒 | 园豆:36522 (高人七级) | 2015-10-22 09:24
其他回答(2)
0

如果非要在计算列上建立索引的话,请考虑将它给持久化。

何德海 | 园豆:435 (菜鸟二级) | 2015-10-21 21:51
0

Sum(CASE WHEN dbo.TT_MT_LOG.MT_STATE=5 THEN 1 else 0 END)

狗霸人间 | 园豆:4 (初学一级) | 2015-10-22 09:14
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册