Question

一个SQL小程序

一个表orders,有orderno(订单号),orderdate(订单时间),costperorder(每笔订单的价值).

编写一个程序显示每天的定单状态。如果当天的定单值总合大于170，则显示“High sales”,否则显示”Low sales”.报告中要求列出日期、定单状态和定单总价值。

显示日期和订单总价值可以用select orderdate, sum(costperorder) from orders Group By orderdate.
但是如何把每一天的订单状态和日期,总价值一起显示呢?

Answer 1

用case语句！！

mysql语句：

select orderdate, case when sum(costperorder)>170 then "High sales" else "Low sales" end as 订单状态,sum(costperorder) from orders Group By orderdate;

sqlserver语句略有不同：

select orderdate, 订单状态 = case when sum(costperorder)>170 then "High sales" else "Low sales" end,sum(costperorder) from orders Group By orderdate;

你两条语句都试试 ！

Answer 2

select orderdate,orderdate, sum(costperorder) as total from orders Group By orderdate

rs("total")就是总价值

Answer 3

select orderdate, 订单状态 = case when sum(costperorder)>170 then "High sales" else "Low sales" end,sum(costperorder) from orders Group By orderdate;