猎豹清扫大师下载安装:一个SQL小程序
来源:百度文库 编辑:高校问答 时间:2024/05/05 12:17:44
一个表orders,有orderno(订单号),orderdate(订单时间),costperorder(每笔订单的价值).
编写一个程序显示每天的定单状态。如果当天的定单值总合大于170,则显示“High sales”,否则显示”Low sales”.报告中要求列出日期、定单状态和定单总价值。
显示日期和订单总价值可以用select orderdate, sum(costperorder) from orders Group By orderdate.
但是如何把每一天的订单状态和日期,总价值一起显示呢?
编写一个程序显示每天的定单状态。如果当天的定单值总合大于170,则显示“High sales”,否则显示”Low sales”.报告中要求列出日期、定单状态和定单总价值。
显示日期和订单总价值可以用select orderdate, sum(costperorder) from orders Group By orderdate.
但是如何把每一天的订单状态和日期,总价值一起显示呢?
用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;
你两条语句都试试 !
select orderdate,orderdate, sum(costperorder) as total from orders Group By orderdate
rs("total")就是总价值
select orderdate, 订单状态 = case when sum(costperorder)>170 then "High sales" else "Low sales" end,sum(costperorder) from orders Group By orderdate;