求一句SQL语句
有下面的数据，要求统计成为下列格式 table1
日期 点数 用户数 张数
2007-09-2410011
2007-09-2420011
2007-09-2420011
2007-09-2430011
2007-09-2430011
2007-09-2430011
2007-09-2430011
2007-09-2464011
2007-09-2464013
2007-09-2464011
2007-09-2464022
2007-09-2464014
2007-09-2464011
2007-09-2464011
2007-09-2464011
2007-09-2464011
2007-09-2464011
2007-09-2464011
2007-09-2464022
2007-09-2464011
2007-09-2464011
2007-09-2464012
2007-09-2464011
2007-09-2464011
2007-09-2464011
2007-09-2464012
2007-09-2464012
2007-09-2464012
2007-09-2464011
2007-09-2464011
2007-09-2464012
2007-09-2464011
2007-09-2464012
2007-09-2464011
2007-09-2464011
2007-09-2464011
2007-09-2464011
2007-09-2464011
2007-09-2464011
2007-09-2464011
2007-09-2464011
2007-09-2464011
2007-09-2464011
2007-09-2464011
2007-09-2464011
2007-09-2464011
2007-09-2464011
2007-09-2464013
2007-09-2464011
2007-09-2464011
2007-09-2464011
2007-09-2464011
2007-09-2464011
2007-09-2464022
2007-09-2464011
2007-09-2464011
2007-09-24108011
2007-09-24108011
2007-09-24108011
2007-09-24108011
Date ￥5(100point) ￥10(200point) ￥15(300point) ￥30(640point) ￥50(1080point)
2007-09-24 1 2 4 49 4
[解决办法]
--如果日期是datatime类型
select convert(varchar(10),日期,120) 日期,
sum(case 点数 when 100 then 用户数*张数 else 0 end) '￥5(100point) ',
sum(case 点数 when 200 then 用户数*张数 else 0 end) '￥10(200point) ',
sum(case 点数 when 300 then 用户数*张数 else 0 end) '￥15(300point) ',
sum(case 点数 when 640 then 用户数*张数 else 0 end) '￥30(640point) ',
sum(case 点数 when 1080 then 用户数*张数 else 0 end) '￥50(1080point) '
from tb
group by convert(varchar(10),日期,120)
