SQL 如何加入多个条件，返回两个条件的所有组合
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13131496/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to do join on multiple criteria, returning all combinations of both criteria
提问by tarheel
I am willing to bet that this is a really simple answer as I am a noob to SQL.
我敢打赌这是一个非常简单的答案，因为我是 SQL 的菜鸟。
table 1 has column 1 (criteria 1) column 2 (criteria 2) column 3 (metric 1)
表 1 有第 1 栏（标准 1） 第 2 栏（标准 2） 第 3 栏（指标 1）
table 2 has column 1 (criteria 1) column 2 (criteria 2) column 3 (metric 2 specific to table2.criteria2)
表 2 有第 1 列（标准 1）第 2 列（标准 2）第 3 列（特定于 table2.criteria2 的指标 2）
There can be anywhere from 1 - 5 values of criteria 2 for each criteria 1 on the table.
对于表中的每个条件 1，可以有 1 - 5 个条件 2 值。
when I use the join statement here (assuming I identify table 1 as One prior to this):
当我在这里使用 join 语句时（假设我在此之前将表 1 标识为 One）：
Select WeddingTable, TableSeat, TableSeatID, Name, Two.Meal
FROM table1 as One
inner join table2 as Two
on One.WeddingTable = Two.WeddingTable and One.TableSeat = Two.TableSeat
I only get one of the criteria 1/criteria 2 combinations even when I know for a fact that there are 3 or 4. How do I get all combinations?
即使我知道有 3 或 4 个组合，我也只得到标准 1/标准 2 组合之一。如何获得所有组合？
Take the situation where there is a wedding where table 1 is basically a seating chart, and table 2 is the meal option that each table/seat has chosen. Table 1 has the convenient TableSeatID, but Table 2 does not have a comparable ID.
以有婚礼的情况为例，表 1 基本上是座位表，表 2 是每个表/座位选择的用餐选项。表 1 具有方便的 TableSeatID，但表 2 没有可比较的 ID。
Sample Data:
样本数据：
The results needs to show all 4 lines, being all 3 seats at WeddingTable 001 and the one seat at WeddingTable 002.
结果需要显示所有 4 行，即婚礼桌 001 的所有 3 个座位和婚礼桌 002 的一个座位。
Desired Results:
预期结果：
回答by Anom
select one.*, two.meal
from table1 as one
left join table2 as two
on (one.weddingtable = two.weddingtable and one.tableseat = two.tableseat)
回答by John Woo
SELECT aa.*,
bb.meal
FROM table1 aa
INNER JOIN table2 bb
ON aa.tableseat = bb.tableseat AND
aa.weddingtable = bb.weddingtable
INNER JOIN
(
SELECT a.tableSeat
FROM table1 a
INNER JOIN table2 b
ON a.tableseat = b.tableseat AND
a.weddingtable = b.weddingtable
WHERE b.meal IN ('chicken', 'steak')
GROUP by a.tableSeat
HAVING COUNT(DISTINCT b.Meal) = 2
) c ON aa.tableseat = c.tableSeat
回答by user3499666
create table a1
(weddingTable INT(3),
tableSeat INT(3),
tableSeatID INT(6),
Name varchar(10));
insert into a1
(weddingTable, tableSeat, tableSeatID, Name)
values (001,001,001001,'Bob'),
(001,002,001002,'Joe'),
(001,003,001003,'Dan'),
(002,001,002001,'Mark');
create table a2
(weddingTable int(3),
tableSeat int(3),
Meal varchar(10));
insert into a2
(weddingTable, tableSeat, Meal)
values
(001,001,'Chicken'),
(001,002,'Steak'),
(001,003,'Salmon'),
(002,001,'Steak');
select x.*, y.Meal
from a1 as x
JOIN a2 as y ON (x.weddingTable = y.weddingTable) AND (x.tableSeat = y. tableSeat);
回答by Chris Latta
It sounds like you want to list all the metrics?
听起来您想列出所有指标？
SELECT Criteria1, Criteria2, Metric1 As Metric
FROM Table1
UNION ALL
SELECT Criteria1, Criteria2, Metric2 As Metric
FROM Table2
ORDER BY 1, 2
If you only want one Criteria1+Criteria2 combination, group them:
如果您只想要一个 Criteria1+Criteria2 组合，请将它们分组：
SELECT Criteria1, Criteia2, SUM(Metric) AS Metric
FROM (
SELECT Criteria1, Criteria2, Metric1 As Metric
FROM Table1
UNION ALL
SELECT Criteria1, Criteria2, Metric2 As Metric
FROM Table2
)
ORDER BY Criteria1, Criteria2
