解决方案 »
- 多行转成一行的问题
- SQL语句(backup)备份数据库问题
- 关于插入多个表的触发器问题
- 在数据库的存储过程中如何使用类似高级语言的全局常量?????在线等待。。。。。
- SQL触发器中可以定义全局变量(@@)吗
- 建了全文索引为何还是慢?
- 当 where ID = '"+myID+"' 没有匹配的记录时, myReader[TotalCarton]会输出什么值呢? 我的测试是什么都没有, 这样合理吗?
- 数据导入导出问题,急!!!
- 一个SQL查询,数据由列转行的问题
- 向大家请教:凡是有中文的字段,数据类型都采用nvarchar(n),这样的选择是否正确?谢谢!!!
- 求SQL语句
- SQL多层查询后再用窗口,求累计和,代码运行速度特别慢,求简化方案,谢谢了
shl.cgbyshlid
, shl.cgbyshl
, hsje.cgbyjeid
, hsje.cgbyje
from (
select
sum(f.id)
, sum(a.shl * f.rate)
from XXX
where f.type = 0
and ( datediff(day,f.start_date,a.rq) >= 0 and datediff(day,a.rq,f.end_date) >= 0 )
and (
(f.creditend > f.credit and a.shl > f.credit and a.shl <= f.creditend)
or (f.creditend = f.credit and f.credit <> 0 and a.shl > f.credit)
or (f.creditend = f.credit and f.credit = 0)
)
) shl
JOIN (
select
sum(f.id)
, sum(a.hsje * f.rate)
from XXX
where f.type = 1
and ( datediff(day,f.start_date,a.rq) >= 0 and datediff(day,a.rq,f.end_date) >= 0 )
and (
(f.creditend > f.credit and a.hsje > f.credit and a.hsje <= f.creditend)
or (f.creditend = f.credit and f.credit <> 0 and a.hsje > f.credit)
or (f.creditend = f.credit and f.credit = 0)
)
) hsje更进一步,这个日期范围的条件“datediff(day,f.start_date,a.rq) >= 0 and datediff(day,a.rq,f.end_date) >= 0”,含义是a.rq的日期部分应该介于f.start_date的日期部分和f.end_date的日期部分之间。我猜这么写是因为这三个字段的类型都是datetime,而程序只想比较日期的范围。那么,其实可以考虑写成“a.rq >= convert(date, f.start_date) and a.rq < dateadd(dd, 1, convert(date, f.end_date))”,因为将datetime字段转化为date,会自动将其时间部分清零,这样可以利用a.rq字段上的索引。并且,这个时间条件对于4个输出字段都是相同的,可以把它单独抽出来放到XXX的部分。
上述方法,把以前的一个查询拆成了两个子查询,但是避免了大量的条件判断。执行效率是否有提升,还是需要观察执行时间和开销的变化,以及分析执行计划。有时候,难看的语句执行效率反而比好看的语句要高~
shl.cgbyshlid
, shl.cgbyshl
, hsje.cgbyjeid
, hsje.cgbyje
from (
select
sum(f.id)
, sum(a.shl * f.rate)
from XXX
where f.type = 0
and ( datediff(day,f.start_date,a.rq) >= 0 and datediff(day,a.rq,f.end_date) >= 0 )
and (
(f.creditend > f.credit and a.shl > f.credit and a.shl <= f.creditend)
or (f.creditend = f.credit and f.credit <> 0 and a.shl > f.credit)
or (f.creditend = f.credit and f.credit = 0)
)
) shl
JOIN (
select
sum(f.id)
, sum(a.hsje * f.rate)
from XXX
where f.type = 1
and ( datediff(day,f.start_date,a.rq) >= 0 and datediff(day,a.rq,f.end_date) >= 0 )
and (
(f.creditend > f.credit and a.hsje > f.credit and a.hsje <= f.creditend)
or (f.creditend = f.credit and f.credit <> 0 and a.hsje > f.credit)
or (f.creditend = f.credit and f.credit = 0)
)
) hsje ON 1 = 1另外,如果XXX部分里包含where条件,就可以和外部的进行合并。