使用局部变量可以减少select语句，具体性能差异需要自己比较一下再决定采用哪种 -digitworm(digitworm) @卡尔加里华人论坛:卡尔加里枫下论坛 The Rolia Forum of Calgary

使用局部变量可以减少select语句，具体性能差异需要自己比较一下再决定采用哪种

digitworm(digitworm)

本文发表在 rolia.net 枫下论坛/*
method 2 - use case statement
optimized by using less select statements
*/

declare @m1 int
declare @m2 int
declare @m3 int
declare @m4 int
declare @m5 int
declare @m6 int
declare @m7 int
declare @m8 int
declare @m9 int
declare @m10 int
declare @m11 int
declare @m12 int

Select @m1=m1, @m2=m2, @m3=m3, @m4=m4, @m5=m5, @m6=m6,
@m7=m7, @m8=m8, @m9=m9, @m10=m10, @m11=m11, @m12=m12 From <your table>

Select @YTD = Case @m
When 1 Then @m1
When 2 Then @m1+@m2
When 3 Then @m1+@m2+@m3
When 4 Then @m1+@m2+@m3+@m4
When 5 Then @m1+@m2+@m3+@m4+@m5
When 6 Then @m1+@m2+@m3+@m4+@m5+@m6
When 7 Then @m1+@m2+@m3+@m4+@m5+@m6+@m7
When 8 Then @m1+@m2+@m3+@m4+@m5+@m6+@m7+@m8
When 9 Then @m1+@m2+@m3+@m4+@m5+@m6+@m7+@m8+@m9
When 10 Then @m1+@m2+@m3+@m4+@m5+@m6+@m7+@m8+@m9+@m10
When 11 Then @m1+@m2+@m3+@m4+@m5+@m6+@m7+@m8+@m9+@m10+@m11
When 12 Then @m1+@m2+@m3+@m4+@m5+@m6+@m7+@m8+@m9+@m10+@m11+@m12
End

Print 'YTD for month ' + cast (@m to varchar(20)) + ' = ' + cast (@YTD as varchar (20))更多精彩文章及讨论，请光临枫下论坛 rolia.net

(#3264351@0)
Last Updated: 2006-10-15
This post has been archived. It cannot be replied.

可以动态生成SQL，用 Exec执行。不过你的数据表由问题。怎莫这样设计？ -nealzhao(neal); 2006-10-14 (#3261513@0)

表不是我设计的. 我只能拿来用. 我现在想用静态的SQL实现, 也就是没有程序帮忙. -poohbear(毛毛熊); 2006-10-14 (#3261574@0)

这个很难嘛? 如果你的mi1..m12都是number的话,select(m1+m2+m3...+m12) as YTD from table where...不就完了嘛? -hard(hard); 2006-10-14 (#3261556@0)

那就是year-end, 不是 year-to-date了 -poohbear(毛毛熊); 2006-10-14 (#3261580@0)

哦,又没注意看题...那就写个stored procedure吧,把月份作为参数传进去,动态build select语句.把你的m1 到 m12在sp里面做出来. -hard(hard); 2006-10-14 {598} (#3261611@0)
ceate sp..
(@month int)
as
begin
declare @m1 varchar(10)
declare @mn varchar(10)
declare @sql varchar(500)
declare @i int
set @m1=' isnull(m1,0)'
set @mn =' '
set @i=2
while @i<=@month
begin
--build columns
Set @mn =@mn + ' + isnull(m' + convert(@i,varchar(2)) + ',0) ,'
end
Set @sql = ' select sum ( ' + @m1+ sunstring (@mn , len(@mn)-1) + ' ) from .......where ....'
---------------------------------------------------remove the latest ','
exec @sql'
end

没装sql,可能有不少错误,不过大概就这个意思吧.用sp做,比建query方便得多.

多谢! 明天试试去! -poohbear(毛毛熊); 2006-10-15 (#3264232@0)

我现在想到的是一种笨办法: 把m1 ... m12选在12个参数里面, 然后用12个if ... 应该可行, 是不是? 不过显得太苯了... 大家能不能想到更好的办法? -poohbear(毛毛熊); 2006-10-14 (#3261579@0)

try this.. -bluebluesky(bluebluesky); 2006-10-14 {198} (#3261607@0)

select decode(
to_char(sysdate,'mm'),
'01', m1,
'02', m1+m2,
'03', m1+m2+m3,
........
'12',m1+m2+m3+.......+m12)
from xxxx
where xxxxxxxx

这个思路还不错，不过总感觉应该可以更好。不过我没想到。。8-p -xfile(xfile); 2006-10-14 (#3261868@0)
多谢! 比我的设想(12个IF)好看多了, 明天试试. -poohbear(毛毛熊); 2006-10-15 (#3264221@0)

如果为了美观的话，try this -886xyz(cqcq); 2006-10-14 {607} (#3262150@0)

select id, sum(rev) from
(
select id, m1, 1 from t
union all
select id, m2, 2 from t
union all
select id, m3, 3 from t
union all
select id, m4, 4 from t
union all
select id, m5, 5 from t
union all
select id, m6, 6 from t
union all
select id, m7, 7 from t
union all
select id, m8, 8 from t
union all
select id, m9, 9 from t
union all
select id, m10, 10 from t
union all
select id, m11, 11 from t
union all
select id, m12, 12 from t
) v where month <= @m group by id;

The table is not normalized, so the SQL needs some kind of hard-coding, either use union all or case expression.

确实美观多了. 明天试试. 多谢! -poohbear(毛毛熊); 2006-10-15 (#3264227@0)
这种方法可能会扫描表多次，降低效率。我建议采用静态SQL,这里已经有许多朋友给出了很好的方案。12种CASE一点也不算多。动态SQL难维护，有安全隐患，即使有BUG在编译期间也不知道，所以应该作为最后选择。 -newkid(newkid); 2006-10-16 (#3265085@0)

You need to build query conditions based on current date. For YTD, your date range shall be 1/1/2006 and current month -1 (last month end). -23456789(大白呼); 2006-10-15 (#3263986@0)

谢谢, logic都是business定的, 这些我自己都做好了, 其实原表没有id. 而是什么公司, 客户, 年份之类的. 我没有详细写在贴子里, 是想突出我自己觉得不好解决的SQL部分. -poohbear(毛毛熊); 2006-10-15 (#3264245@0)

多谢楼上朋友们, 坛子里真是SQL高手众多, 我自己深感惭愧 -- TSQL, PL/SQL我也写了不少了, 跟高手一比还是差远了. 以后会多来请教. -poohbear(毛毛熊); 2006-10-15 (#3264252@0)

两种方法供参考：1 使用临时表 2 使用case语句. 建议用标量函数（scaler function）封装 -digitworm(digitworm); 2006-10-15 {2342} (#3264266@0)

本文发表在 rolia.net 枫下论坛/* method 1 : use temporary table*/

declare @YTD int
declare @m int /* shoud be a parameter */

/* set parameter value for test */
set @m=3

/* Create a temorary table */
create #monthdays_tbl (
mon int,
days int
)

/* get month days from your table and inserted into the temporary table */
select 1 as mon, m1 as days insert #monthdays_tbl from <your table>
select 2 as mon, m2 as days insert #monthdays_tbl from <your table>
select 3 as mon, m3 as days insert #monthdays_tbl from <your table>
select 4 as mon, m4 as days insert #monthdays_tbl from <your table>
select 5 as mon, m5 as days insert #monthdays_tbl from <your table>
select 6 as mon, m6 as days insert #monthdays_tbl from <your table>
select 7 as mon, m7 as days insert #monthdays_tbl from <your table>
select 8 as mon, m8 as days insert #monthdays_tbl from <your table>
select 9 as mon, m9 as days insert #monthdays_tbl from <your table>
select 10 as mon, m10 as days insert #monthdays_tbl from <your table>
select 11 as mon, m11 as days insert #monthdays_tbl from <your table>
select 12 as mon, m12 as days insert #monthdays_tbl from <your table>

select @YTD = select sum (days) from #monthdays_tbl where mon<=@m

Print 'YTD for month ' + cast (@m to varchar(20)) + ' = ' + cast (@YTD as varchar (20))

/* method 2 - use case statement */

Select @YTD = Case @m
When 1 Then Select m1 From <your table>
When 2 Then Select m1 + m2 From <your table>
When 3 Then Select m1 + m2 + m3 From <your table>
When 4 Then Select m1 + m2 + m3 + m4 From <your table>
When 5 Then Select m1 + m2 + m3 + m4 + m5 From <your table>
When 6 Then Select m1 + m2 + m3 + m4 + m5 + m6 From <your table>
When 7 Then Select m1 + m2 + m3 + m4 + m5 + m6 + m7 From <your table>
When 8 Then Select m1 + m2 + m3 + m4 + m5 + m6 + m7 + m8 From <your table>
When 9 Then Select m1 + m2 + m3 + m4 + m5 + m6 + m7 + m8 + m9 From <your table>
When 10 Then Select m1 + m2 + m3 + m4 + m5 + m6 + m7 + m8 + m9 + m10 From <your table>
When 11 Then Select m1 + m2 + m3 + m4 + m5 + m6 + m7 + m8 + m9 + m10 + m11 From <your table>
When 12 Then Select m1 + m2 + m3 + m4 + m5 + m6 + m7 + m8 + m9 + m10 + m11 + m12 From <your table>
End

Print 'YTD for month ' + cast (@m to varchar(20)) + ' = ' + cast (@YTD as varchar (20))更多精彩文章及讨论，请光临枫下论坛 rolia.net

谢谢. 记下了, 明天两种方法都试一试. 我个人觉得临时表没有省下太多code, 似是大材小用了. CASE好象不是SQL定义的, 不知道我们SQL SERVER行不行, 先试试再说了. -poohbear(毛毛熊); 2006-10-15 (#3264293@0)

MS SQL 2000/2005 支持case -digitworm(digitworm); 2006-10-15 (#3264311@0)

再谢, 那就应该一点问题都没有了 -- 有的话就是我个人的脑筋了 ^_^ -poohbear(毛毛熊); 2006-10-15 (#3264320@0)

使用局部变量可以减少select语句，具体性能差异需要自己比较一下再决定采用哪种 -digitworm(digitworm); 2006-10-15 {1070} (#3264351@0)
本文发表在 rolia.net 枫下论坛/*
method 2 - use case statement
optimized by using less select statements
*/

declare @m1 int
declare @m2 int
declare @m3 int
declare @m4 int
declare @m5 int
declare @m6 int
declare @m7 int
declare @m8 int
declare @m9 int
declare @m10 int
declare @m11 int
declare @m12 int

Select @m1=m1, @m2=m2, @m3=m3, @m4=m4, @m5=m5, @m6=m6,
@m7=m7, @m8=m8, @m9=m9, @m10=m10, @m11=m11, @m12=m12 From <your table>

Select @YTD = Case @m
When 1 Then @m1
When 2 Then @m1+@m2
When 3 Then @m1+@m2+@m3
When 4 Then @m1+@m2+@m3+@m4
When 5 Then @m1+@m2+@m3+@m4+@m5
When 6 Then @m1+@m2+@m3+@m4+@m5+@m6
When 7 Then @m1+@m2+@m3+@m4+@m5+@m6+@m7
When 8 Then @m1+@m2+@m3+@m4+@m5+@m6+@m7+@m8
When 9 Then @m1+@m2+@m3+@m4+@m5+@m6+@m7+@m8+@m9
When 10 Then @m1+@m2+@m3+@m4+@m5+@m6+@m7+@m8+@m9+@m10
When 11 Then @m1+@m2+@m3+@m4+@m5+@m6+@m7+@m8+@m9+@m10+@m11
When 12 Then @m1+@m2+@m3+@m4+@m5+@m6+@m7+@m8+@m9+@m10+@m11+@m12
End

Print 'YTD for month ' + cast (@m to varchar(20)) + ' = ' + cast (@YTD as varchar (20))更多精彩文章及讨论，请光临枫下论坛 rolia.net

Thank you!! -poohbear(毛毛熊); 2006-10-15 (#3264484@0)

downstair "looi500" gave a good idea for just using one select statement without using local variables, see #3264399. for your app just using "@m" replaces "MONTH(CURRENT_TIMESTAMP)" in the sql. -digitworm(digitworm); 2006-10-16 (#3265196@0)

使用case -looi500(looi); 2006-10-15 {965} (#3264399@0)

Tested in MySQL:
select
case
when MONTH(CURRENT_TIMESTAMP) = 1 then m1
when MONTH(CURRENT_TIMESTAMP) = 2 then m1 + m2
when MONTH(CURRENT_TIMESTAMP) = 3 then m1 + m2 + m3
when MONTH(CURRENT_TIMESTAMP) = 4 then m1 + m2 + m3 + m4
when MONTH(CURRENT_TIMESTAMP) = 5 then m1 + m2 + m3 + m4 + m5
when MONTH(CURRENT_TIMESTAMP) = 6 then m1 + m2 + m3 + m4 + m5 + m6
when MONTH(CURRENT_TIMESTAMP) = 7 then m1 + m2 + m3 + m4 + m5 + m6 + m7
when MONTH(CURRENT_TIMESTAMP) = 8 then m1 + m2 + m3 + m4 + m5 + m6 + m7 + m8
when MONTH(CURRENT_TIMESTAMP) = 9 then m1 + m2 + m3 + m4 + m5 + m6 + m7 + m8 + m9
when MONTH(CURRENT_TIMESTAMP) = 10 then m1 + m2 + m3 + m4 + m5 + m6 + m7 + m8 + m9 + m10
when MONTH(CURRENT_TIMESTAMP) = 11 then m1 + m2 + m3 + m4 + m5 + m6 + m7 + m8 + m9 + m10 + m11
else m1 + m2+ m3 + m4 + m5+ m6 + m7 + m8 + m9 + m10 + m11 + m12
end as YTD
from yourTable

Thank you! -poohbear(毛毛熊); 2006-10-15 (#3264476@0)

@Calgary

使用局部变量可以减少select语句，具体性能差异需要自己比较一下再决定采用哪种

Replies, comments and Discussions:

More Topics