MYSQL 连接具有不同行数的相关表 "Record"

Question

表 A 是一个订单表，每个订单 1 行。表 B 是一个 Items 表，每个项目 1 行。它们通过订单号链接。当我加入他们的 O/N 时，如果订单上有超过 1 件商品，我会收到多行订单。我按不同表中的字段进行分组，因此无法使用该方法。

连接它们的正确方法是什么，这样 1 个订单行和 3 个项目行不会变成 3 个订单和 3 个项目？

对这个基本问题表示歉意，我的大脑今天崩溃了。

我用下面的内容简化了我的要求。

CREATE TABLE Orders
    (`ORDURN` varchar(8), `ORDSOU` varchar(10), `ORDVAL` decimal(9,2))
;

INSERT INTO Orders
    (`ORDURN`, `ORDSOU`, `ORDVAL`)
VALUES
    ('12345112', 'WEB', '28.41'),
    ('22544548', 'ADVERT', '11.58'),
    ('44848643', 'TELEPHONE', '41.18')
;


CREATE TABLE Items
    (`ITMSTK` varchar(10), `ITMQTY` varchar (3), `ITMCOG` int(9), `ITMURN` varchar(8), `ITMSOU` varchar(10))
;

INSERT INTO Items
    (`ITMSTK`, `ITMQTY`, `ITMCOG`, `ITMURN`, `ITMSOU`)
VALUES
    ('WN778', '1', '2.00', '12345112', 'WEB'),
    ('WN776', '1', '1.45', '12345112', 'WEB'),
    ('WN771', '1', '1.86', '12345112', 'WEB'),
    ('WN845', '1', '1.45', '22544548', 'ADVERT'),
    ('WN846', '1', '1.38', '22544548', 'ADVERT'),
    ('WN845', '1', '20.16', '44848643', 'TELEPHONE')
;
CREATE TABLE Sources
    (`SOUCOD` varchar(10), `SOUDESC` varchar(45))
;
INSERT INTO Sources 
    (`SOUCOD`, `SOUDESC`)
VALUES
    ('WEB', 'Web Orders 2016'),
    ('ADVERT', 'Advert Junes 2016'),
    ('TELEPHONE', 'Telephone Orders 2016')
;

然后我在下面运行这个查询

    select
S.soucod as Sources,
s.soudesc as Description,
sum(i.itmcog) as COG,
count(DISTINCT o.ordurn) as Orders,
sum(o.ordval) as OrderValue
from sources s
join orders o on o.ordsou = s.soucod
join items i on i.itmsou = s.soucod
group by s.soucod

给我

Sources     Description           COG   Orders  OrderValue  
ADVERT      Advert Junes 2016       2        1       23.16
TELEPHONE   Telephone Orders 2016  20        1       41.18
WEB         Web Orders 2016         5        1       85.23

显然，由于连接计算了多行，因此订单值已被扭曲

这是期望的结果(商品成本是错误的，但我知道为什么会这样，就像我创建数据的方式一样，此列除了显示需要引入商品之外没有任何目的)表):

Sources     Description           COG   Orders  OrderValue  
ADVERT      Advert Junes 2016       2        1       11.58
TELEPHONE   Telephone Orders 2016  20        1       41.18
WEB         Web Orders 2016         5        1       28.41

我希望这能解释它。

Answer 1

需要顺序连接来计算中间结果。在连接的外部级别，对子查询的结果使用 max() 函数。

select Sources, Description,
       sum(i.itmcog) as COG,
       max(Orders) as Orders, max(OrderValue) as OrderValue
  from (
        select s.soucod as Sources, s.soudesc as Description, 
               count(o.ordurn) as Orders,
               sum(o.ordval) as OrderValue
          from sources s
          join orders o on o.ordsou = s.soucod
         group by s.soucod
       ) A
   join items i on i.itmsou = A.Sources
  group by A.Sources

如果只从依赖的表中选择一个结果，您可以在选择列表中使用子查询:

select s.SOUCOD as Sources, s.soudesc as Description, 
       (select sum(i.itmcog)
          from items i
         where i.itmsou=s.soucod
       ) as COG,
       count(o.ordurn) as Orders,
       sum(o.ordval) as OrderValue
  from sources s
  join orders o on o.ordsou = s.soucod
 group by s.soucod