表:xiaoshou
字段:id ,username
数据:1 张三
2 李四
表:dingdan
字段:id ,ordertotal
1 100
1 50
sql:select username,sum(ordertotal) as ordertotal from xiaoshou left outer join dingdan on xiaoshou.id=dingdan.id where ordertotal>0 group by username
查询结果:username ordertotal
张三 150希望得到的结果:username ordertotal
张三 150
李四 0
即使“李四”没有数据也要显示李四的名字,且ordertotal为0。多谢!!
字段:id ,username
数据:1 张三
2 李四
表:dingdan
字段:id ,ordertotal
1 100
1 50
sql:select username,sum(ordertotal) as ordertotal from xiaoshou left outer join dingdan on xiaoshou.id=dingdan.id where ordertotal>0 group by username
查询结果:username ordertotal
张三 150希望得到的结果:username ordertotal
张三 150
李四 0
即使“李四”没有数据也要显示李四的名字,且ordertotal为0。多谢!!
你都加了where ordertotal>0这个限制条件了,当然只显示ordertotal大于0的
mysql> select * from xiaoshou;
+------+----------+
| id | username |
+------+----------+
| 1 | zhangsan |
| 2 | lisi |
+------+----------+
2 rows in set (0.00 sec)mysql> select * from dingdan;
+------+------------+
| id | ordertotal |
+------+------------+
| 1 | 100 |
| 1 | 50 |
+------+------------+
2 rows in set (0.00 sec)mysql> select t1.username, ifnull(sum(t2.ordertotal),0) as ordertotal from xiao
shou t1 left join dingdan t2 on t1.id = t2.id group by t1.username;
+----------+------------+
| username | ordertotal |
+----------+------------+
| lisi | 0 |
| zhangsan | 150 |
+----------+------------+
2 rows in set (0.00 sec)
估计要多家一个字段来区别了,我是想不到有什么方法了
from xiaoshu a left join
(select id, sum(ordertotal) as o_t from dingdan where ordertotal>0 group by id ) b
on a.id=b.id
select a.usernamer,isnull(b.o_t,0) as ordertotal
from xiaoshu a left join
(select id, sum(ordertotal) as o_t from dingdan where ordertotal>0 group by id ) b
on a.id=b.id
select a.usernamer,isnull(b.o_t,0) as ordertotal
from xiaoshu a left join
(select id, sum(ordertotal) as o_t from dingdan where ordertotal>0 group by id ) b
on a.id=b.id