求一个sql---在线等马上给分!!!
表: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。
多谢！！
[解决办法]
select username,isnull(sum(ordertotal),0) as ordertotal from xiaoshou left outer join dingdan on xiaoshou.id=dingdan.id group by username
[解决办法]
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)
select username,sum(ordertotal) as ordertotal from xiaoshou left join dingdan t2 on t1.id = t2.id where ordertotal>0 or username='李四' group by username
select a.id,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
