select a.id , IFNULL(SAG,0) from A a INNER join (SELECT ID,SUM(AGE) AS SAG FROM B WHERE reporttime ='2011-08-08' GROUP BY ID) b on a.id = b.id where a.id IN(1,2) ;
INNER join 还是outer join? 我也觉得必须要用子查询。
不是, 先要计算 (SELECT ID,SUM(AGE) AS SAG FROM B WHERE reporttime ='2011-08-08' GROUP BY ID) 再与A表连接,INNER JOIN、LEFT JOIN均可
(SELECT ID,SUM(AGE) AS SAG FROM B WHERE reporttime ='2011-08-08' GROUP BY ID) 查出的是空, inner join 出来也是空的啊。我把 inner 换成 full outer join 好像可以解决问题yeah!
SELECT id,SUM(sag) FROM ( SELECT ID,SUM(AGE) AS SAG FROM B WHERE reporttime ='2011-08-10' GROUP BY ID UNION ALL SELECT id,0 FROM a) a1 GROUP BY id) b1 ON a.id=b1.id WHERE a.id IN(1,2)这样就OK了
case when sum(age) is null then 0 else sum(age)
SELECT id,SUM(sag) FROM ( SELECT ID,SUM(AGE) AS SAG FROM B WHERE reporttime ='2011-08-10' UNION ALL SELECT id,0 AS SAG FROM A ) WHERE id IN(1,2) Group BY ID 不知道是不是这样才对,回家测试下。
SELECT id,SUM(sag) FROM ( SELECT id,SUM(AGE) AS SAG FROM B WHERE reporttime =120 Group BY ID UNION ALL SELECT id,0 AS SAG FROM A WHERE id IN(1,2) ) Group BY ID
(SELECT ID,SUM(AGE) AS SAG FROM B WHERE reporttime ='2011-08-08' GROUP BY ID) b
on a.id = b.id
where a.id IN(1,2) ;
先要计算
(SELECT ID,SUM(AGE) AS SAG FROM B WHERE reporttime ='2011-08-08' GROUP BY ID)
再与A表连接,INNER JOIN、LEFT JOIN均可
查出的是空, inner join 出来也是空的啊。我把 inner 换成 full outer join 好像可以解决问题yeah!
不知道你能不能理解我呢,
就是我查B表的时候,需要传入id(3,5),那么我就在B表内生成数据(3,0,null),(5,0,null)
因为age字段是0,所以sum()的时候没有影响,我只要在where 语句中 把 (or reporttime is null)的数据
全部取出来,好像也是可以的。但是好像sql不好写。
SELECT ID,SUM(AGE) AS SAG FROM B WHERE reporttime ='2011-08-10' GROUP BY ID
UNION ALL
SELECT id,0 FROM a) a1 GROUP BY id) b1 ON a.id=b1.id WHERE a.id IN(1,2)这样就OK了
SELECT id,SUM(sag) FROM
(
SELECT ID,SUM(AGE) AS SAG FROM B WHERE reporttime ='2011-08-10'
UNION ALL
SELECT id,0 AS SAG FROM A
)
WHERE id IN(1,2) Group BY ID
不知道是不是这样才对,回家测试下。
(
SELECT id,SUM(AGE) AS SAG FROM B WHERE reporttime =120 Group BY ID
UNION ALL
SELECT id,0 AS SAG FROM A WHERE id IN(1,2)
)
Group BY ID