如题 sql2000
select a.*,p.qjje,p.qjcs,p.qjgf,p.qjlf
from k_view a
left outer join (select khdm,sum(xse) as qjje,sum(gf) as qjgf,sum(lf) as qjlf,count(ro_no) as qjcs
from ro where wxjd='已结' and jd_date>='2007-07-07' AND jd_date<='2009-09-09' group by khdm) p on a.khdm=p.khdm
where a. jd_date>='2007-07-07' AND a.jd_date<='2009-09-09'
select a.*,p.qjje,p.qjcs,p.qjgf,p.qjlf
from k_view a
left outer join (select khdm,sum(xse) as qjje,sum(gf) as qjgf,sum(lf) as qjlf,count(ro_no) as qjcs
from ro where wxjd='已结' and jd_date>='2007-07-07' AND jd_date<='2009-09-09' group by khdm) p on a.khdm=p.khdm
where a. jd_date>='2007-07-07' AND a.jd_date<='2009-09-09'
解决方案 »
- 一个很看起来很简单的累加问题,搞死我了,救命啊~~~~
- 新手2表查询问题,谢谢
- 因前端实现太困难,所以不得已改变数据库表结构,再次请教如何便这样的存储过程!!
- primary key
- SET DATEFIRST 选项设置对 DATENAME(WEEKDAY, date) 结果有影响吗?
- 求教部署包含外部程序集的报表发布
- SQL难题求助----急!!!
- sql server2000发生19等级的严重错误怎么办
- 向明白人求教一个SQL查询语句
- 在sqlserver中如何通过系统表(数据字典或其他方法)来提取某个表的某个时间短的数据200分不少了
- 这个触发器错了呢??
- 表的竖向变成横向以生产部门为标题
介意你把a.* 换成 a表所有对应的列 一一列出来 这样效率要高很多
因为如果你用.* 那查询会去找a表的列
declare @begintime datetime ,@endtime datetime
set @begintime='2007-07-07'
set @endtime='2009-09-09'
select a.*,p.qjje,p.qjcs,p.qjgf,p.qjlf
from k_view a
left outer join (select khdm,sum(xse) as qjje,sum(gf) as qjgf,sum(lf) as qjlf,count(ro_no) as qjcs
from ro where wxjd='已结' and jd_date>=@begintime AND jd_date <=@endtime group by khdm) p on a.khdm=p.khdm
where a. jd_date>=@begintime= AND a.jd_date <=@endtime
from (select * from k_view where a. jd_date>='2007-07-07' AND a.jd_date <='2009-09-09' )a
left outer join (select khdm,sum(xse) as qjje,sum(gf) as qjgf,sum(lf) as qjlf,count(ro_no) as qjcs
from ro where wxjd='已结' and jd_date>='2007-07-07' AND jd_date <='2009-09-09' group by khdm) p on a.khdm=p.khdm 试试
当执行 CREATE VIEW 语句时,ANSI_NULLS 和 QUOTED_IDENTIFIER 选项必须设置为 ON。OBJECTPROPERTY 函数通过 ExecIsAnsiNullsOn 或 ExecIsQuotedIdentOn 属性为视图报告此信息。
为执行所有 CREATE TABLE 语句以创建视图引用的表,ANSI_NULLS 选项必须设置为 ON。
视图不能引用任何其它视图,只能引用基表。
视图引用的所有基表必须与视图位于同一个数据库中,并且所有者也与视图相同。
必须使用 SCHEMABINDING 选项创建视图。SCHEMABINDING 将视图绑定到基础基表的架构。
必须已使用 SCHEMABINDING 选项创建了视图中引用的用户定义的函数。
表和用户定义的函数必须由 2 部分的名称引用。不允许使用 1 部分、3 部分和 4 部分的名称。
视图中的表达式所引用的所有函数必须是确定性的。OBJECTPROPERTY 函数的 IsDeterministic 属性报告用户定义的函数是否是确定性的。有关更多信息,请参见确定性函数和非确定性函数。
视图中的 SELECT 语句不能包含下列 Transact-SQL 语法元素:
选择列表不能使用 * 或 table_name.* 语法指定列。必须显式给出列名。
不能在多个视图列中指定用作简单表达式的表的列名。如果对列的所有(或只有一个例外)引用是复杂表达式的一部分或是函数的一个参数,则可多次引用该列。例如,下列选择列表是非法的:
SELECT ColumnA, ColumnB, ColumnA
下列选择列表是合法的:
SELECT ColumnA, COUNT(ColumnA), ColumnA + Column B AS AddColAColB FROM T1
SELECT SUM(ColumnA), ColumnA % ColumnB AS ModuloColAColB, COUNT_BIG(*) FROM T1 GROUP BY ColumnA
派生表。
行集函数。
UNION 运算符。
子查询。
外联接或自联接。
TOP 子句。
ORDER BY 子句。
DISTINCT 关键字。
COUNT(*)(允许 COUNT_BIG(*)。)
AVG、MAX、MIN、STDEV、STDEVP、VAR 或 VARP 聚合函数。如果在引用索引视图的查询中指定 AVG、MAX、MIN、STDEV、STDEVP、VAR 或 VARP,如果视图选择列表包含以下替换函数,则优化器会经常计算需要的结果。
例如,索引视图选择列表不能包含表达式 AVG(SomeColumn)。如果视图选择列表包含表达式 SUM(SomeColumn) 和 COUNT_BIG(SomeColumn),则 SQL Server 可为引用视图并指定 AVG(SomeColumn) 的查询计算平均数。
引用可为空的表达式的 SUM 函数。
全文谓词 CONTAINS 或 FREETEXT。
COMPUTE 或 COMPUTE BY 子句。
如果没有指定 GROUP BY,则视图选择列表不能包含聚合表达式。
如果指定了 GROUP BY,则视图选择列表必须包含 COUNT_BIG(*) 表达式,并且,视图定义不能指定 HAVING、CUBE 或 ROLLUP。
通过一个既可以取值为 float 值也可以使用 float 表达式求值的表达式而生成的列不能作为索引视图或表的索引的键。
视图可以加索引 你仔细读读吧
from k_view a
left outer join (
select khdm,sum(xse) as qjje,sum(gf) as qjgf,sum(lf) as qjlf,count(ro_no) as qjcs
from ro
where wxjd='已结'
and jd_date>='2007-07-07'
AND jd_date <='2009-09-09'
group by khdm) as p
on a.khdm=p.khdm --加索引
and a. jd_date>='2007-07-07' --加索引
AND a.jd_date <='2009-09-09' --加索引
GO
SET ANSI_NULLS ON
GO CREATE view k_view
WITH SCHEMABINDING as
select m.khdm,m.khmc,m.embox,n.c_ser,n.vin_no,n.j_ser,n.pxxm,n.model,n.color,n.fy_date,r.address,r.phone,r.jd_date,r.lc,o.xse,z.rjlc,
DATEDIFF([Day],r.jd_date,getdate()) as ts,b.lc as llc,b.jd_date as lldate,b.cs,o.zcs,b.nNextlc,b.dtnextby,o.gsf,o.lf
from dbo.khxx m left outer join dbo.zc_wx n on m.khdm=n.khdm
left outer join (select khdm,address,phone,jd_date,lc from dbo.ro t where jd_date = (select max(jd_date) from dbo.ro where khdm = t.khdm)) r on m.khdm=r.khdm
left outer join (select khdm,sum(xse) as xse,sum(gf) as gsf,sum(lf) as lf,count(ro_no) as zcs from dbo.ro where wxjd='已结' group by khdm) o on m.khdm=o.khdm
left outer join (select v.khdm,v.lc,v.jd_date,o.cs,v.nNextlc,v.dtnextby from (select ro.khdm,ro.lc,ro.jd_date,ro.nNextlc,ro.dtnextby,ro_detail.wxxm,ro_detail.price from dbo.ro,dbo.ro_detail where ro.ro_no=ro_detail.ro_no) v
left outer join (select khdm,cs=count(*) from (select ro.khdm,ro.lc,ro.wxjd,ro.jd_date,ro.nNextlc,ro.dtnextby,ro_detail.wxxm,ro_detail.price from dbo.ro,dbo.ro_detail where ro.ro_no=ro_detail.ro_no) w where wxxm='例保'
and wxjd='已结' group by khdm) o on v.khdm=o.khdm where jd_date = (select max(jd_date) from (select ro.khdm,ro.lc,ro.wxjd,ro.jd_date,ro.nNextlc,ro.dtnextby,ro_detail.wxxm,ro_detail.price from dbo.ro,dbo.ro_detail where ro.ro_no=ro_detail.ro_no) w where khdm = v.khdm and wxxm='例保') and wxxm='例保') b
on m.khdm=b.khdm
left outer join (SELECT A.khdm,
CASE WHEN A.cnt = 1 THEN B.lc ELSE B.lc - C.lc END AS lcc,
CASE WHEN A.cnt = 1 THEN 0 ELSE DATEDIFF(day,C.jd_date,B.jd_date) END AS rqc,
CAST(CASE WHEN A.cnt = 1 THEN 0 ELSE (case when DATEDIFF(day,C.jd_date,B.jd_date)=0 then 0 else (B.lc - C.lc)*1./DATEDIFF(day,C.jd_date,B.jd_date) end) END AS NUMERIC(10,2)) AS rjlc
FROM (SELECT khdm,MIN(jd_date) AS 小日期,MAX(jd_date) AS 大日期,COUNT(*) AS cnt FROM (SELECT khdm,lc,jd_date FROM dbo.ro AS A WHERE jd_date IN (SELECT TOP 2 jd_date FROM dbo.ro WHERE khdm=A.khdm ORDER BY jd_date DESC)
) kk GROUP BY khdm) AS A
LEFT JOIN (SELECT khdm,lc,jd_date FROM dbo.ro AS A WHERE jd_date IN (SELECT TOP 2 jd_date FROM dbo.ro WHERE khdm=A.khdm ORDER BY jd_date DESC)
) AS B ON A.khdm=B.khdm AND A.大日期=B.jd_date
LEFT JOIN (SELECT khdm,lc,jd_date FROM dbo.ro AS A WHERE jd_date IN (SELECT TOP 2 jd_date FROM dbo.ro WHERE khdm=A.khdm ORDER BY jd_date DESC)
) AS C ON A.khdm=C.khdm AND A.小日期=C.jd_date ) z on m.khdm=z.khdm where o.zcs>=1 GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO命令完成SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GOCREATE UNIQUE CLUSTERED INDEX IV1 ON k_view(jd_date)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
服务器: 消息 1936,级别 16,状态 1,行 1
无法 index 视图 'naveco6i.dbo.k_view'。它包含一个或多个不允许使用的构造。
set statistics io on
set statistics time on
set statistics PROFILE on把执行计划贴出来...
你在存储过程里自己写,性能要求很高的话自己用存储过程写,把没必要的东西全部剔除掉,
最好用索引搜索,adoquery可以调用存储过程