MYSQL 连接每个“记录"具有不同行数的相关表 [英] MYSQL Joining Related Tables With Different Row Amounts Per "Record"
问题描述
表 A 是一个订单表，每个订单有 1 行.表 B 是一个项目表，每个项目有 1 行.它们通过订单号链接.当我在这个 O/N 上加入他们时，如果订单上有 1 个以上的项目，我会收到多行订单.我正在按不同表中的字段分组，因此无法使用该方法.
Table A is an Orders table with 1 row per order. Table B is an Items table with 1 row per item. They are linked by an order number. When I join them on this O/N, I get multiple lines of Orders if there was more than 1 item on the order. I am grouping by a field in a different table so I cannot use that method.
加入它们的正确方法是什么，以便 1 个订单行和 3 个项目行不会变成 3 个订单和 3 个项目?
What is the correct method of joining them so 1 Order row and 3 Item rows doesn't turn into 3 Order and 3 Item?
抱歉这个基本问题，今天我的大脑崩溃了.
Apologies for the basic question, my brain is on a meltdown today.
我用下面的内容简化了我的要求.
I have simplified what I am asking with the below.
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')
;
然后我在下面运行这个查询
And I am then running this query below
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
显然订单值已经被连接偏斜了，因为它已经计算了多行
Obviously the order values have been skewed by the join as it has counted multiple lines
这是想要的结果(Cost of goods 是错误的，但我知道为什么会这样，就像我创建数据的方式一样，此列不是为了显示需要引入 Items 之外的任何目的表):
This is the desired result (Cost of goods is wrong, but I know why that is, just the way I created the data, this column isn't here for any purpose other than to show the need to bring in the Items table):
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
我希望这能解释它.
推荐答案
需要顺序连接和计算中间结果.在连接的外层对子查询的结果使用 max() 函数.
Need sequential join with calculate intermediate result. On outer level of join use max() function on result of subquery.
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
如果只从依赖表中选择一个结果，您可以在选择列表中使用子查询:
If select only one result from depended table you can use subquery in select list:
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
这篇关于MYSQL 连接每个“记录"具有不同行数的相关表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
