mySQL - SUM、COUNT 和 JOIN 显示所有记录

Question

这个问题已经有答案了:

How can I do a FULL OUTER JOIN in MySQL? (15 个回答)

已关闭 4 年前。

这是此问题的后续问题 Join two tables with SUM and COUNT 。我尝试做的是将所有值显示为历史表中的某些值而不是轮换表中的值，反之亦然(999 和 777)

所以我的 table 是:

create table history (
    code int(10) primary key,
    PN varchar(10) not null,
    Qty int(10) not null,
    LOC_ID int(10));

insert into history values (1,  'T1', 1, 1);
insert into history values (2,  'A1', 2,2);
insert into history values (3,  'J1', 3,3);
insert into history values (4,  'A2', 1,4);
insert into history values (5,  'J2', 2,1);
insert into history values (6,  'A3', 3,2);
insert into history values (7,  'J3', 4,3);
insert into history values (8,  'T1', 5,4);
insert into history values (9,  'A1', 1,1);
insert into history values (10, '999', 3,2);
insert into history values (11, 'J2', 4,3);
insert into history values (12, 'A1', 3,4);
insert into history values (13, 'J2', 5,1);

create table rota (
        code int(10) primary key,
    PN varchar(10) not null,
    SN varchar(10) not null,
    LOC_ID int(10));

insert into rota values (1,     'T1',   't1a',1);
insert into rota values (2,     'A1',   'a1a',2);
insert into rota values (3,     'J1',   'j1a',3);
insert into rota values (4,     'A2',   'a2a',4);
insert into rota values (5,     'J2',   'j2a',1);
insert into rota values (6,     'A3',   'a3a',2);
insert into rota values (7,     'J3',   'j3a',3);
insert into rota values (8,     '777',   't1b',4);
insert into rota values (9,     'A1',   'a1b',1);
insert into rota values (10,    'J2',   'j2b',2);
insert into rota values (11,    'J2',   'j2c',3);
insert into rota values (12,    'A1',   'a1c',4);
insert into rota values (13,    'J2',   'j2d',1);
insert into rota values (14,    'J2',   'j2e',2);
insert into rota values (15,    'J2',   'j2f',3);

create table loca (
        code1 int(10) primary key,
    LOC varchar(10) not null);

insert into loca values (1,     'AAA');
insert into loca values (2,     'BBB');
insert into loca values (3,     'CCC');
insert into loca values (4,     'DDD');

我得到的代码是

select CASE WHEN a.pn IS NULL THEN b.pn ELSE a.pn  END AS PN
, a.q
, b.c 
, a.LOC_ID
, b.LOC_ID

from
(select 
h.pn
, sum(qty) q
, h.LOC_ID
from 
history h

group by h.pn, h.LOC_ID) a
RIGHT JOIN 
(select 
r.pn
, count(sn) c
, r.LOC_ID
from 
rota r
group by r.pn, r.LOC_ID) b
on a.pn = b.pn WHERE a.LOC_ID = b.LOC_ID
order by a.pn;

上面的代码适用于两个表中的所有 PN。问题在于特定于其中一个表的值。我可以从 JOIN 中删除 WHERE 子句，但它不正确。问题是 - 如何从历史记录和轮类表中获取所有 PN，其中一些 PN 只存在于一张表中。我在 RIGHT JOIN 方面运气不错，但这并没有涵盖其他表中的唯一值。有人以前遇到过解决方案吗？

结果应如下表所示

 PN      LOC_ID     Count   Qty
 T1        1           1    1
 A1        2           1    2
 J1        3           1    3
 A2        4           1    1
 J2        1           2    2
 A3        2           1    3
 J3        3           1    4
777        4           1    NULL
 A1        1           1    1
 J2        2           2    NULL
 J2        3           2    4
 A1        4           1    3
 J2        1           2    2
 J2        2           2    NULL
 J2        3           2    4
999        2           NULL 3

Answer 1

使用另一个连接，即left，并使它们union

select t.PN,t.q,t.c,t.LOC_ID,t.LOC_ID_b from 
(
  select CASE WHEN a.pn IS NULL THEN b.pn ELSE a.pn  END AS PN
, a.q
, b.c 
, a.LOC_ID
, b.LOC_ID as LOC_ID_b

from
(select 
h.pn
, sum(qty) q
, h.LOC_ID
from 
history h

group by h.pn, h.LOC_ID) a
RIGHT JOIN 
(select 
r.pn
, count(sn) c
, r.LOC_ID
from 
rota r
group by r.pn, r.LOC_ID) b
on a.pn = b.pn and a.LOC_ID = b.LOC_ID

) as t
union 
select t2.PN,t2.q,t2.c,t2.LOC_ID,t2.LOC_ID_b from
(
  select CASE WHEN a.pn IS NULL THEN b.pn ELSE a.pn  END AS PN
, a.q
, b.c 
, a.LOC_ID
, b.LOC_ID as LOC_ID_b

from
(select 
h.pn
, sum(qty) q
, h.LOC_ID
from 
history h

group by h.pn, h.LOC_ID) a
left JOIN 
(select 
r.pn
, count(sn) c
, r.LOC_ID
from 
rota r
group by r.pn, r.LOC_ID
) b
on a.pn = b.pn and a.LOC_ID = b.LOC_ID
) t2

http://sqlfiddle.com/#!9/c20c81/20