sql 查询当月及全年每个用户累计销售量
答案:2 悬赏:40
解决时间 2021-01-16 11:37
- 提问者网友:临风不自傲
- 2021-01-15 11:24
sql 查询当月及全年每个用户累计销售量
最佳答案
- 二级知识专家网友:迷人又混蛋
- 2021-01-15 12:12
SELECt B.id, name, left(date,4) dt, sum(money) as 累计
FROM B LEFT JOIN A ON B.id=A.id
GROUP BY B.id, NAME, left(date,4)
UNIOn ALL
SELECt B.id, name, left(date,6) dt, sum(money) as 当月
FROM B LEFT JOIN A ON B.id=A.id
WHERe date BETEEN '204-1-1' AND '2014-1-31'
GROUP BY B.id, NAME, left(date,4)
ORDER BY B.id, NAME, left(date,4)追问我的意思是结果应该是四列,如下.用UNIOn查出来的是三列的
id, name, 累计, 当月追答SELECt ID, NAME, SUM(CASE WHEN DT='累计' THEN TOTAL ELSE 0 END) 累计
, SUM(CASE WHEN DT='当月' THEN TOTAL ELSE 0 END) 当月
FROM (
SELECt B.id, name, '累计' dt, sum(money) as total
FROM B LEFT JOIN A ON B.id=A.id
GROUP BY B.id, NAME
UNIOn ALL
SELECt B.id, name, '当月' dt, sum(money) as total
FROM B LEFT JOIN A ON B.id=A.idWHERe date BETEEN '2014-1-1' AND '2014-1-31'
GROUP BY B.id, NAME
) X
GROUP BY ID, NAME
FROM B LEFT JOIN A ON B.id=A.id
GROUP BY B.id, NAME, left(date,4)
UNIOn ALL
SELECt B.id, name, left(date,6) dt, sum(money) as 当月
FROM B LEFT JOIN A ON B.id=A.id
WHERe date BETEEN '204-1-1' AND '2014-1-31'
GROUP BY B.id, NAME, left(date,4)
ORDER BY B.id, NAME, left(date,4)追问我的意思是结果应该是四列,如下.用UNIOn查出来的是三列的
id, name, 累计, 当月追答SELECt ID, NAME, SUM(CASE WHEN DT='累计' THEN TOTAL ELSE 0 END) 累计
, SUM(CASE WHEN DT='当月' THEN TOTAL ELSE 0 END) 当月
FROM (
SELECt B.id, name, '累计' dt, sum(money) as total
FROM B LEFT JOIN A ON B.id=A.id
GROUP BY B.id, NAME
UNIOn ALL
SELECt B.id, name, '当月' dt, sum(money) as total
FROM B LEFT JOIN A ON B.id=A.idWHERe date BETEEN '2014-1-1' AND '2014-1-31'
GROUP BY B.id, NAME
) X
GROUP BY ID, NAME
全部回答
- 1楼网友:时间的尘埃
- 2021-01-15 12:17
SELECt B.id, name, sum(money) as total, '累计' as sort
FROM B LEFT JOIN A ON B.id=A.id
union all
SELECt B.id, name, sum(money) as total, '当月' as sort
FROM B LEFT JOIN A ON B.id=A.id
WHERe date BETEEN '204-1-1' AND '2014-1-31'
ORDER BY B.id追问我的意思是结果应该是四列,如下.用UNIOn查出来的是三列的
id, name, 累计, 当月
FROM B LEFT JOIN A ON B.id=A.id
union all
SELECt B.id, name, sum(money) as total, '当月' as sort
FROM B LEFT JOIN A ON B.id=A.id
WHERe date BETEEN '204-1-1' AND '2014-1-31'
ORDER BY B.id追问我的意思是结果应该是四列,如下.用UNIOn查出来的是三列的
id, name, 累计, 当月
我要举报
如以上问答内容为低俗、色情、不良、暴力、侵权、涉及违法等信息,可以点下面链接进行举报!
大家都在看
推荐资讯