有下面的数据,要求统计成为下列格式 table1
日期 点数 用户数 张数
2007-09-24 100 1 1
2007-09-24 200 1 1
2007-09-24 200 1 1
2007-09-24 300 1 1
2007-09-24 300 1 1
2007-09-24 300 1 1
2007-09-24 300 1 1
2007-09-24 640 1 1
2007-09-24 640 1 3
2007-09-24 640 1 1
2007-09-24 640 2 2
2007-09-24 640 1 4
2007-09-24 640 1 1
2007-09-24 640 1 1
2007-09-24 640 1 1
2007-09-24 640 1 1
2007-09-24 640 1 1
2007-09-24 640 1 1
2007-09-24 640 2 2
2007-09-24 640 1 1
2007-09-24 640 1 1
2007-09-24 640 1 2
2007-09-24 640 1 1
2007-09-24 640 1 1
2007-09-24 640 1 1
2007-09-24 640 1 2
2007-09-24 640 1 2
2007-09-24 640 1 2
2007-09-24 640 1 1
2007-09-24 640 1 1
2007-09-24 640 1 2
2007-09-24 640 1 1
2007-09-24 640 1 2
2007-09-24 640 1 1
2007-09-24 640 1 1
2007-09-24 640 1 1
2007-09-24 640 1 1
2007-09-24 640 1 1
2007-09-24 640 1 1
2007-09-24 640 1 1
2007-09-24 640 1 1
2007-09-24 640 1 1
2007-09-24 640 1 1
2007-09-24 640 1 1
2007-09-24 640 1 1
2007-09-24 640 1 1
2007-09-24 640 1 1
2007-09-24 640 1 3
2007-09-24 640 1 1
2007-09-24 640 1 1
2007-09-24 640 1 1
2007-09-24 640 1 1
2007-09-24 640 1 1
2007-09-24 640 2 2
2007-09-24 640 1 1
2007-09-24 640 1 1
2007-09-24 1080 1 1
2007-09-24 1080 1 1
2007-09-24 1080 1 1
2007-09-24 1080 1 1
Date ¥5(100point) ¥10(200point) ¥15(300point) ¥30(640point) ¥50(1080point)
2007-09-24 1 2 4 49 4
日期 点数 用户数 张数
2007-09-24 100 1 1
2007-09-24 200 1 1
2007-09-24 200 1 1
2007-09-24 300 1 1
2007-09-24 300 1 1
2007-09-24 300 1 1
2007-09-24 300 1 1
2007-09-24 640 1 1
2007-09-24 640 1 3
2007-09-24 640 1 1
2007-09-24 640 2 2
2007-09-24 640 1 4
2007-09-24 640 1 1
2007-09-24 640 1 1
2007-09-24 640 1 1
2007-09-24 640 1 1
2007-09-24 640 1 1
2007-09-24 640 1 1
2007-09-24 640 2 2
2007-09-24 640 1 1
2007-09-24 640 1 1
2007-09-24 640 1 2
2007-09-24 640 1 1
2007-09-24 640 1 1
2007-09-24 640 1 1
2007-09-24 640 1 2
2007-09-24 640 1 2
2007-09-24 640 1 2
2007-09-24 640 1 1
2007-09-24 640 1 1
2007-09-24 640 1 2
2007-09-24 640 1 1
2007-09-24 640 1 2
2007-09-24 640 1 1
2007-09-24 640 1 1
2007-09-24 640 1 1
2007-09-24 640 1 1
2007-09-24 640 1 1
2007-09-24 640 1 1
2007-09-24 640 1 1
2007-09-24 640 1 1
2007-09-24 640 1 1
2007-09-24 640 1 1
2007-09-24 640 1 1
2007-09-24 640 1 1
2007-09-24 640 1 1
2007-09-24 640 1 1
2007-09-24 640 1 3
2007-09-24 640 1 1
2007-09-24 640 1 1
2007-09-24 640 1 1
2007-09-24 640 1 1
2007-09-24 640 1 1
2007-09-24 640 2 2
2007-09-24 640 1 1
2007-09-24 640 1 1
2007-09-24 1080 1 1
2007-09-24 1080 1 1
2007-09-24 1080 1 1
2007-09-24 1080 1 1
Date ¥5(100point) ¥10(200point) ¥15(300point) ¥30(640point) ¥50(1080point)
2007-09-24 1 2 4 49 4
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 日期
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)
顶
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)
既然楼上一经说了,那我就免了/