S
soul
Unregistered / Unconfirmed
GUEST, unregistred user!
改进一下
SELECT
UserFrom,
sum(case when d=1 then
s else
0 end) AS '1号',
sum(case when d=2 then
s else
0 end) AS '2号',
sum(case when d=3 then
s else
0 end) AS '3号',
sum(case when d=4 then
s else
0 end) AS '4号',
sum(case when d=5 then
s else
0 end) AS '5号',
sum(case when d=6 then
s else
0 end) AS '6号',
sum(case when d=7 then
s else
0 end) AS '7号',
sum(case when d=8 then
s else
0 end) AS '8号',
sum(case when d=9 then
s else
0 end) AS '9号',
sum(case when d=10 then
s else
0 end) AS '10号',
sum(case when d=11 then
s else
0 end) AS '11号',
sum(case when d=12 then
s else
0 end) AS '12号',
sum(case when d=13 then
s else
0 end) AS '13号',
sum(case when d=14 then
s else
0 end) AS '14号',
sum(case when d=15 then
s else
0 end) AS '15号',
sum(case when d=16 then
s else
0 end) AS '16号',
sum(case when d=17 then
s else
0 end) AS '17号',
sum(case when d=18 then
s else
0 end) AS '18号',
sum(case when d=19 then
s else
0 end) AS '19号',
sum(case when d=20 then
s else
0 end) AS '20号',
sum(case when d=21 then
s else
0 end) AS '21号',
sum(case when d=22 then
s else
0 end) AS '22号',
sum(case when d=23 then
s else
0 end) AS '23号',
sum(case when d=24 then
s else
0 end) AS '24号',
sum(case when d=25 then
s else
0 end) AS '25号',
sum(case when d=26 then
s else
0 end) AS '26号',
sum(case when d=27 then
s else
0 end) AS '27号',
sum(case when d=28 then
s else
0 end) AS '28号',
sum(case when d=29 then
s else
0 end) AS '29号',
sum(case when d=30 then
s else
0 end) AS '30号',
sum(case when d=31 then
s else
0 end) AS '31号'
FROM
(select day(datetime) as d, sum(Points) as s ,UserFrom
from letters
where parent=0
group by day(datetime),UserFrom
) t
group by UserFrom
这是按照大富翁写的,已经测试正确。
你的就是
SELECT
goods_name,
sum(case when d=1 then
s else
0 end) AS '1号',
sum(case when d=2 then
s else
0 end) AS '2号',
sum(case when d=3 then
s else
0 end) AS '3号',
sum(case when d=4 then
s else
0 end) AS '4号',
sum(case when d=5 then
s else
0 end) AS '5号',
sum(case when d=6 then
s else
0 end) AS '6号',
sum(case when d=7 then
s else
0 end) AS '7号',
sum(case when d=8 then
s else
0 end) AS '8号',
sum(case when d=9 then
s else
0 end) AS '9号',
sum(case when d=10 then
s else
0 end) AS '10号',
sum(case when d=11 then
s else
0 end) AS '11号',
sum(case when d=12 then
s else
0 end) AS '12号',
sum(case when d=13 then
s else
0 end) AS '13号',
sum(case when d=14 then
s else
0 end) AS '14号',
sum(case when d=15 then
s else
0 end) AS '15号',
sum(case when d=16 then
s else
0 end) AS '16号',
sum(case when d=17 then
s else
0 end) AS '17号',
sum(case when d=18 then
s else
0 end) AS '18号',
sum(case when d=19 then
s else
0 end) AS '19号',
sum(case when d=20 then
s else
0 end) AS '20号',
sum(case when d=21 then
s else
0 end) AS '21号',
sum(case when d=22 then
s else
0 end) AS '22号',
sum(case when d=23 then
s else
0 end) AS '23号',
sum(case when d=24 then
s else
0 end) AS '24号',
sum(case when d=25 then
s else
0 end) AS '25号',
sum(case when d=26 then
s else
0 end) AS '26号',
sum(case when d=27 then
s else
0 end) AS '27号',
sum(case when d=28 then
s else
0 end) AS '28号',
sum(case when d=29 then
s else
0 end) AS '29号',
sum(case when d=30 then
s else
0 end) AS '30号',
sum(case when d=31 then
s else
0 end) AS '31号'
FROM
(select day(bill_date) as d, sum(quantity) as s ,goods_name
from vdish
where (bill_date >= '2003-02-01') AND (bill_date <= '2003-02-03')
group by day(bill_date),goods_name
) t
group by goods_name
SELECT
UserFrom,
sum(case when d=1 then
s else
0 end) AS '1号',
sum(case when d=2 then
s else
0 end) AS '2号',
sum(case when d=3 then
s else
0 end) AS '3号',
sum(case when d=4 then
s else
0 end) AS '4号',
sum(case when d=5 then
s else
0 end) AS '5号',
sum(case when d=6 then
s else
0 end) AS '6号',
sum(case when d=7 then
s else
0 end) AS '7号',
sum(case when d=8 then
s else
0 end) AS '8号',
sum(case when d=9 then
s else
0 end) AS '9号',
sum(case when d=10 then
s else
0 end) AS '10号',
sum(case when d=11 then
s else
0 end) AS '11号',
sum(case when d=12 then
s else
0 end) AS '12号',
sum(case when d=13 then
s else
0 end) AS '13号',
sum(case when d=14 then
s else
0 end) AS '14号',
sum(case when d=15 then
s else
0 end) AS '15号',
sum(case when d=16 then
s else
0 end) AS '16号',
sum(case when d=17 then
s else
0 end) AS '17号',
sum(case when d=18 then
s else
0 end) AS '18号',
sum(case when d=19 then
s else
0 end) AS '19号',
sum(case when d=20 then
s else
0 end) AS '20号',
sum(case when d=21 then
s else
0 end) AS '21号',
sum(case when d=22 then
s else
0 end) AS '22号',
sum(case when d=23 then
s else
0 end) AS '23号',
sum(case when d=24 then
s else
0 end) AS '24号',
sum(case when d=25 then
s else
0 end) AS '25号',
sum(case when d=26 then
s else
0 end) AS '26号',
sum(case when d=27 then
s else
0 end) AS '27号',
sum(case when d=28 then
s else
0 end) AS '28号',
sum(case when d=29 then
s else
0 end) AS '29号',
sum(case when d=30 then
s else
0 end) AS '30号',
sum(case when d=31 then
s else
0 end) AS '31号'
FROM
(select day(datetime) as d, sum(Points) as s ,UserFrom
from letters
where parent=0
group by day(datetime),UserFrom
) t
group by UserFrom
这是按照大富翁写的,已经测试正确。
你的就是
SELECT
goods_name,
sum(case when d=1 then
s else
0 end) AS '1号',
sum(case when d=2 then
s else
0 end) AS '2号',
sum(case when d=3 then
s else
0 end) AS '3号',
sum(case when d=4 then
s else
0 end) AS '4号',
sum(case when d=5 then
s else
0 end) AS '5号',
sum(case when d=6 then
s else
0 end) AS '6号',
sum(case when d=7 then
s else
0 end) AS '7号',
sum(case when d=8 then
s else
0 end) AS '8号',
sum(case when d=9 then
s else
0 end) AS '9号',
sum(case when d=10 then
s else
0 end) AS '10号',
sum(case when d=11 then
s else
0 end) AS '11号',
sum(case when d=12 then
s else
0 end) AS '12号',
sum(case when d=13 then
s else
0 end) AS '13号',
sum(case when d=14 then
s else
0 end) AS '14号',
sum(case when d=15 then
s else
0 end) AS '15号',
sum(case when d=16 then
s else
0 end) AS '16号',
sum(case when d=17 then
s else
0 end) AS '17号',
sum(case when d=18 then
s else
0 end) AS '18号',
sum(case when d=19 then
s else
0 end) AS '19号',
sum(case when d=20 then
s else
0 end) AS '20号',
sum(case when d=21 then
s else
0 end) AS '21号',
sum(case when d=22 then
s else
0 end) AS '22号',
sum(case when d=23 then
s else
0 end) AS '23号',
sum(case when d=24 then
s else
0 end) AS '24号',
sum(case when d=25 then
s else
0 end) AS '25号',
sum(case when d=26 then
s else
0 end) AS '26号',
sum(case when d=27 then
s else
0 end) AS '27号',
sum(case when d=28 then
s else
0 end) AS '28号',
sum(case when d=29 then
s else
0 end) AS '29号',
sum(case when d=30 then
s else
0 end) AS '30号',
sum(case when d=31 then
s else
0 end) AS '31号'
FROM
(select day(bill_date) as d, sum(quantity) as s ,goods_name
from vdish
where (bill_date >= '2003-02-01') AND (bill_date <= '2003-02-03')
group by day(bill_date),goods_name
) t
group by goods_name