mysql - 带联合的 SQL 查询

Question

我有以下查询。我做了 union 以获得所需的输出，但我没有得到它。

create table foo (dateFact date, id int);
create table bar (dateFact date, id int);

insert into foo(dateFact, id) values('2013-03-01', 88), ('2013-03-02', 89),('2013-03-03', 90);
insert into bar(dateFact, id) values('2013-03-01', 88), ('2013-03-02', 89),('2013-03-03', 90);

select * from foo;
select * from bar;

(select dateFact, id as idA, 0 from foo order by dateFact) union (select dateFact, 0, id as idB from bar order by dateFact);

上面的查询给了我下面的结果:

dateFact               idA                  0
2013-03-01             88                   0
2013-03-02             89                   0
2013-03-03             90                   0
2013-03-01             0                    88
2013-03-02             0                    89
2013-03-03             0                    90

但是我想要:

dateFact               idA                 idB
2013-03-01             88                   88
2013-03-02             89                   89
2013-03-03             90                   90

谢谢你的帮助!

Answer 1

包装联合查询并使用 MAX() 对外部查询执行聚合，并按 dateFact 对它们进行分组。

SELECT dateFact, MAX(idA) idA, MAX(idB) idB
FROM
        (
            SELECT dateFact, id AS idA, 0 AS idB FROM foo
            UNION
            SELECT dateFact, 0 AS idA, id AS idB FROM bar 
        ) s
GROUP   BY dateFact
ORDER   BY dateFact