mysql如何在 select 语句中添加新列

发布时间: 2017/1/1 3:24:04
注意事项: 本文中文内容可能为机器翻译,如要查看英文原文请点击上面连接.

我想在我的 select 语句中添加新列

SELECT name,line,style,operation,
7to8am,8to9am,9to10am,10to11am,11to12am,
1to2pm,2to3pm,3to4pm,4to5pm,5to6pm,6to7pm,7to8pm,8to9pm,9to10pm,10to11pm,11to12pm,
sum(7to8am+8to9am+9to10am+10to11am+11to12am+1to2pm
+2to3pm+3to4pm+4to5pm+5to6pm+6to7pm+7to8pm+
8to9pm+9to10pm+10to11pm+11to12pm) as DailyTotal,id from new_hourly GROUP By line

我想要添加新列将显示的日期为今天的 DailyTotal 总和

这是我的 sql 备份文件http://www.uploadmb.com/dw.php?id=1446536983

请帮助我 !非常感谢!

解决方法 1:

所以你想要一个额外添加的列只线在哪里 datee 等于当前日期吗?

在这种情况下,我愿意为案件时表达去︰

coalesce(
      sum(
        case 
          when datee = CURDATE() 
             then 7to8am+8to9am+9to10am+10to11am+11to12am+1to2pm+2to3pm+3to4pm+4to5pm+5to6pm+6to7pm+7to8pm+8to9pm+9to10pm+10to11pm+11to12pm 
             else null 
          end
         )
    ,0) as TodaysTotal

这总结只有这些行在哪里列 dateecurdate() 和返回 0,如果根本没有行目前为今天。

完整的 SQL:

SELECT name,line,style,operation,
7to8am,8to9am,9to10am,10to11am,11to12am,
1to2pm,2to3pm,3to4pm,4to5pm,5to6pm,6to7pm,7to8pm,8to9pm,9to10pm,10to11pm,11to12pm,
sum(7to8am+8to9am+9to10am+10to11am+11to12am+1to2pm
+2to3pm+3to4pm+4to5pm+5to6pm+6to7pm+7to8pm+
8to9pm+9to10pm+10to11pm+11to12pm) as DailyTotal,

coalesce(
  sum(
    case 
      when datee = CURDATE() 
         then 7to8am+8to9am+9to10am+10to11am+11to12am+1to2pm+2to3pm+3to4pm+4to5pm+5to6pm+6to7pm+7to8pm+8to9pm+9to10pm+10to11pm+11to12pm 
         else null 
      end
     )
,0) as TodaysTotal

,id from new_hourly GROUP By line
赞助商