--说明 member是会员表,cardconsume是消费表,这两个表中由字段m_cardno关联。
--建一个会员消费合计的视图
--目前会员表数据大概有5万,消费表数据大概有15万
--如果没有索引查询的时候相当慢
SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL,ARITHABORT,QUOTED_IDENTIFIER,ANSI_NULLS ON
GO
CREATE VIEW V1
WITH SCHEMABINDING
AS
select
a.m_cardno,a.m_name,max(b.consumdate) as maxconsumdate,
a.issuedate,a.pointtotal,sum(b.cardpay) as sumcardpay,sum(b.cashpay) as sumcashpay,
sum(b.tradepoint) as sumtradepoint,a.nowbalance,max(b.tradetimes) as sumtradetimes,
sum(b.savemoney) as sumsavemoney,
sum(b.thisconsume) as sumconsume,sum(b.returnmoney) as sumreturnmoney,
sum(b.returnpoint) as sumreturnpoint,a.m_sex,a.m_age
from cardconsume b ,member a
where b.m_cardno=a.m_cardno
group by a.m_cardno,a.m_name,a.issuedate,a.pointtotal,a.nowbalance,a.m_sex,a.m_age
go--以上视图create成功,但以下的索引视图create失败.
--建立视图索引
CREATE UNIQUE CLUSTERED INDEX IV1 ON V1 (m_cardno)
GO
--错误提示如下:
--无法 index 视图 'rkcxcrm_mix.dbo.V1'。它包含一个或多个不允许使用的构造。--请大家帮助。
--建一个会员消费合计的视图
--目前会员表数据大概有5万,消费表数据大概有15万
--如果没有索引查询的时候相当慢
SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL,ARITHABORT,QUOTED_IDENTIFIER,ANSI_NULLS ON
GO
CREATE VIEW V1
WITH SCHEMABINDING
AS
select
a.m_cardno,a.m_name,max(b.consumdate) as maxconsumdate,
a.issuedate,a.pointtotal,sum(b.cardpay) as sumcardpay,sum(b.cashpay) as sumcashpay,
sum(b.tradepoint) as sumtradepoint,a.nowbalance,max(b.tradetimes) as sumtradetimes,
sum(b.savemoney) as sumsavemoney,
sum(b.thisconsume) as sumconsume,sum(b.returnmoney) as sumreturnmoney,
sum(b.returnpoint) as sumreturnpoint,a.m_sex,a.m_age
from cardconsume b ,member a
where b.m_cardno=a.m_cardno
group by a.m_cardno,a.m_name,a.issuedate,a.pointtotal,a.nowbalance,a.m_sex,a.m_age
go--以上视图create成功,但以下的索引视图create失败.
--建立视图索引
CREATE UNIQUE CLUSTERED INDEX IV1 ON V1 (m_cardno)
GO
--错误提示如下:
--无法 index 视图 'rkcxcrm_mix.dbo.V1'。它包含一个或多个不允许使用的构造。--请大家帮助。
其中member表的主键为m_cardno,
consume表的主键为ID.
http://topic.csdn.net/t/20031123/20/2486622.html
--try:
CREATE VIEW V1
WITH SCHEMABINDING
AS
select
a.m_cardno,a.m_name,max(b.consumdate) as maxconsumdate,
a.issuedate,a.pointtotal,sum(b.cardpay) as sumcardpay,sum(b.cashpay) as sumcashpay,
sum(b.tradepoint) as sumtradepoint,a.nowbalance,max(b.tradetimes) as sumtradetimes,
sum(b.savemoney) as sumsavemoney,
sum(b.thisconsume) as sumconsume,sum(b.returnmoney) as sumreturnmoney,
sum(b.returnpoint) as sumreturnpoint,a.m_sex,a.m_age, COUNT_BIG(*) AS cBig
from cardconsume b ,member a
where b.m_cardno=a.m_cardno
group by a.m_cardno,a.m_name,a.issuedate,a.pointtotal,a.nowbalance,a.m_sex,a.m_age
go
无法将 视图 'V1' 绑定到架构,因为名称 'cardconsume' 对于架构绑定无效。名称必须由两部分构成,并且对象不能引用自身。
谢谢楼上各位。结账了。