我下面的这组数据,是我根据这段sql查询出来的
select b.superId,b.createTime,b.totalMoney from tb_months a ,(
select sum(o.total_money) as totalMoney,month(o.create_time) as createTime,a.superid as superId ,a.address as address from
tb_order o,tb_address a where o.address_id=a.address_id and year(create_time)=YEAR(NOW())
group by month(create_time),superid
)b where a.monthes=b.createTime
结果:
superId createTime totalMoney
ad006 1 200
ad006 2 80
ad006 3 30
ad006 4 70
ad006 5 300
ad008 6 200
ad008 7 70
ad006 10 50
ad008 10 345
ad006 11 20
ad013 11 100
ad013 12 100createTime 为月份,然后我现在需要的是,把id分组列出1-12月份的信息,没有的用0补足。要显示的结果如下:
id months totalMoney
ad006 1 200
ad006 2 80
ad006 3 30
ad006 4 70
ad006 5 300
ad006 6 0
ad006 7 0
ad006 8 0
ad006 9 0
ad006 10 50
ad006 11 20
ad006 12 0
ad008 1 0
ad008 2 0
ad008 3 0
ad008 4 0
ad008 5 0
ad008 6 200
ad008 7 70
ad008 8 0
ad008 8 0
ad008 10 345
ad008 11 0
ad008 12 0
ad013 1 0
ad013 2 0
ad013 3 0
ad013 4 0
ad013 5 0
ad013 6 0
ad013 7 0
ad013 8 0
ad013 9 0
ad013 10 0
ad013 11 100
ad013 12 100
select b.superId,b.createTime,b.totalMoney from tb_months a ,(
select sum(o.total_money) as totalMoney,month(o.create_time) as createTime,a.superid as superId ,a.address as address from
tb_order o,tb_address a where o.address_id=a.address_id and year(create_time)=YEAR(NOW())
group by month(create_time),superid
)b where a.monthes=b.createTime
结果:
superId createTime totalMoney
ad006 1 200
ad006 2 80
ad006 3 30
ad006 4 70
ad006 5 300
ad008 6 200
ad008 7 70
ad006 10 50
ad008 10 345
ad006 11 20
ad013 11 100
ad013 12 100createTime 为月份,然后我现在需要的是,把id分组列出1-12月份的信息,没有的用0补足。要显示的结果如下:
id months totalMoney
ad006 1 200
ad006 2 80
ad006 3 30
ad006 4 70
ad006 5 300
ad006 6 0
ad006 7 0
ad006 8 0
ad006 9 0
ad006 10 50
ad006 11 20
ad006 12 0
ad008 1 0
ad008 2 0
ad008 3 0
ad008 4 0
ad008 5 0
ad008 6 200
ad008 7 70
ad008 8 0
ad008 8 0
ad008 10 345
ad008 11 0
ad008 12 0
ad013 1 0
ad013 2 0
ad013 3 0
ad013 4 0
ad013 5 0
ad013 6 0
ad013 7 0
ad013 8 0
ad013 9 0
ad013 10 0
ad013 11 100
ad013 12 100
解决方案 »
- 不知道mysql密码
- 单表goods,260w数据,key_buffer_size目前为8mb,感觉好像少了
- 请问这个sql语句错在哪里(关于mysql not in的用法)
- 一个复杂的sql语句,在数据多的时候可能会卡住吗?
- select * from table where a = 1 and b = 2,这样的如何做索引比较合适呢
- mysql问题!昨天有人责怪我对高手大呼小叫的!今天就低调一点!
- mysql5 sql优化问题,大家献计献策!
- 庞大的数据智能导入问题,如何实现,谢谢
- 100分请教:奇怪的MySQL日志
- 消息 102,级别 15,状态 1,第 1 行 '-' 附近有语法错误。
- pgsql可以进行事务的嵌套吗
- 问mysql同步在项目中能实现数据备份吗,会不会有问题?
你的查询:存为VIEW1
SELECT id,months,SUM(totalMoney) FROM (
SELECT * FROM VIEW1
UNION ALL
SELECT A.id,B.ID,0 FROM LSB B,(select DISTINCT id FROM VIEW1) A) C
GROUP BY id,months
(select dictinct superId from atb_address )a
union all
select * from (
select sum(o.total_money) as totalMoney,month(o.create_time) as createTime,a.superid as superId ,a.address as address from
tb_order o,tb_address a where o.address_id=a.address_id and year(create_time)=YEAR(NOW())
group by month(create_time),superid
)b1对UNION的结果再求和,自行修改2个SQL语句返回的字段