[code=SQL]select [time]=isnull(a.[time],b.[time]), Aprice=sum(isnull(a.price,0)),Bprice=sum(isnull(b.price,0)) from 表A a full join 表B b on a.[time]=b.[time] group by isnull(a.[time],b.[time])[/code]
CREATE TABLE #tb1(id INT, price INT, [time] VARCHAR(10)) INSERT #tb1 SELECT 1, 23, '2011-08' UNION ALL SELECT 2, 11, '2011-09' UNION ALL SELECT 3, 2, '2011-09' GO CREATE TABLE #tb2(id INT, price INT, [time] VARCHAR(10)) INSERT #tb2 SELECT 1, 13, '2011-07' UNION ALL SELECT 2, 41, '2011-08' UNION ALL SELECT 3, 3, '2011-08' GO --SQL: SELECT a.time, b.*, c.* FROM (SELECT [time] FROM #tb1 UNION SELECT [time] FROM #tb2) a CROSS APPLY (SELECT [A.Price]=ISNULL(SUM(price),0) FROM #tb1 WHERE [time]=a.time) b OUTER APPLY (SELECT [B.Price]=ISNULL(SUM(price),0) FROM #tb2 WHERE [time]=a.time) c /* time A.Price B.Price 2011-07 0 13 2011-08 23 44 2011-09 13 0 */
晕,写完刚想贴代码,发现楼主是MYSQL,这个还是转发MYSQL版块吧。
with a as( select 1 id,23 price,'2011-08' time from dual union all select 2 , 11 , '2011-09' from dual union all select 3, 2 , '2011-09' from dual ),b as( select 1 id,13 price,'2011-07' time from dual union all select 2 , 41 , '2011-08' from dual union all select 3, 3 , '2011-08' from dual ) select COALESCE(t1.time, t2.time), nvl(t1.p, 0), nvl(t2.p, 0) from (select a.time, sum(a.price) p from a group by time) t1 full join (select b.time, sum(b.price) p from b group by time) t2 on t1.time = t2.time
能否给我个mysql的查询语句?
额 如果oracle的话 分别group 再连接查询。
select time,sum(price_a),sum(price_b) from ( select time,sum(price) as price_a,0 as price_b from a group by time union all select time,0 as price_a,sum(price) as price_b from b group by time ) group by time
Aprice=sum(isnull(a.price,0)),Bprice=sum(isnull(b.price,0))
from 表A a full join 表B b on a.[time]=b.[time]
group by isnull(a.[time],b.[time])[/code]
INSERT #tb1
SELECT 1, 23, '2011-08' UNION ALL
SELECT 2, 11, '2011-09' UNION ALL
SELECT 3, 2, '2011-09'
GO
CREATE TABLE #tb2(id INT, price INT, [time] VARCHAR(10))
INSERT #tb2
SELECT 1, 13, '2011-07' UNION ALL
SELECT 2, 41, '2011-08' UNION ALL
SELECT 3, 3, '2011-08'
GO
--SQL:
SELECT a.time, b.*, c.* FROM
(SELECT [time] FROM #tb1 UNION SELECT [time] FROM #tb2) a
CROSS APPLY
(SELECT [A.Price]=ISNULL(SUM(price),0) FROM #tb1 WHERE [time]=a.time) b
OUTER APPLY
(SELECT [B.Price]=ISNULL(SUM(price),0) FROM #tb2 WHERE [time]=a.time) c
/*
time A.Price B.Price
2011-07 0 13
2011-08 23 44
2011-09 13 0
*/
with a as(
select 1 id,23 price,'2011-08' time from dual
union all
select 2 , 11 , '2011-09' from dual
union all
select 3, 2 , '2011-09' from dual
),b as(
select 1 id,13 price,'2011-07' time from dual
union all
select 2 , 41 , '2011-08' from dual
union all
select 3, 3 , '2011-08' from dual
)
select COALESCE(t1.time, t2.time), nvl(t1.p, 0), nvl(t2.p, 0)
from (select a.time, sum(a.price) p from a group by time) t1
full join (select b.time, sum(b.price) p from b group by time) t2 on t1.time =
t2.time
from (
select time,sum(price) as price_a,0 as price_b
from a
group by time
union all
select time,0 as price_a,sum(price) as price_b
from b
group by time
)
group by time