×

Loading...
Ad by
  • 最优利率和cashback可以申请特批,好信用好收入offer更好。请点链接扫码加微信咨询,Scotiabank -- Nick Zhang 6478812600。
Ad by
  • 最优利率和cashback可以申请特批,好信用好收入offer更好。请点链接扫码加微信咨询,Scotiabank -- Nick Zhang 6478812600。

使用case

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
Report

Replies, comments and Discussions:

  • 工作学习 / 专业技术讨论 / 我也有个SQL难题, 请教高手:
    Table结构是: id, m1, m2, m3, ..., m12

    我需要根据现在的月份(这个我知道怎么用SQL得到, 姑且记做@m吧), 算出m1 + m2 + ... + m(@m), 也就是business中常用的year-to-date.

    我以前是在程序里面算的, 现在想改成直接用SQL算. 不知道能不能做到?

    多谢!
    • 可以动态生成SQL,用 Exec执行。不过你的数据表由问题。怎莫这样设计?
      • 表不是我设计的. 我只能拿来用. 我现在想用静态的SQL实现, 也就是没有程序帮忙.
    • 这个很难嘛? 如果你的mi1..m12都是number的话,select(m1+m2+m3...+m12) as YTD from table where...不就完了嘛?
      • 那就是year-end, 不是 year-to-date了
        • 哦,又没注意看题...那就写个stored procedure吧,把月份作为参数传进去,动态build select语句.把你的m1 到 m12在sp里面做出来.
          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方便得多.
          • 多谢! 明天试试去!
    • 我现在想到的是一种笨办法: 把m1 ... m12选在12个参数里面, 然后用12个if ... 应该可行, 是不是? 不过显得太苯了... 大家能不能想到更好的办法?
    • try this..
      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
      • 多谢! 比我的设想(12个IF)好看多了, 明天试试.
    • 如果为了美观的话,try this
      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.
      • 确实美观多了. 明天试试. 多谢!
      • 这种方法可能会扫描表多次,降低效率。我建议采用静态SQL,这里已经有许多朋友给出了很好的方案。12种CASE一点也不算多。动态SQL难维护,有安全隐患,即使有BUG在编译期间也不知道,所以应该作为最后选择。
    • 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).
      • 谢谢, logic都是business定的, 这些我自己都做好了, 其实原表没有id. 而是什么公司, 客户, 年份之类的. 我没有详细写在贴子里, 是想突出我自己觉得不好解决的SQL部分.
    • 多谢楼上朋友们, 坛子里真是SQL高手众多, 我自己深感惭愧 -- TSQL, PL/SQL我也写了不少了, 跟高手一比还是差远了. 以后会多来请教.
    • 两种方法供参考:1 使用临时表 2 使用case语句. 建议用标量函数(scaler function)封装
      本文发表在 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行不行, 先试试再说了.
        • MS SQL 2000/2005 支持case
          • 再谢, 那就应该一点问题都没有了 -- 有的话就是我个人的脑筋了 ^_^
            • 使用局部变量可以减少select语句,具体性能差异需要自己比较一下再决定采用哪种
              本文发表在 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!!
            • 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.
    • 使用case
      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!