如何在MySQL中选择具有匹配字段的行？

Question

我有两张表，一张是价格优先级表，另一张是分钟数优先级表，每个用户和产品都有对应的记录。如何选择它们以便对于每个用户和产品都有一个记录呢？

Table 1: Override Prices
+--------+-----------+---------------+
| userID | productID | overridePrice |
+--------+-----------+---------------+
| 4      | 53        | 99.99         |
| 4      | 13        | 99.99         |
| 4      | 55        | 99.99         |
+--------+-----------+---------------+

Table 2: Override Minutes
+--------+-----------+---------------+
| userID | productID | overrideMin   |
+--------+-----------+---------------+
| 4      | 18        | 23            |
| 4      | 55        | 4             |
| 50     | 55        | 2             |
+--------+-----------+---------------+

我希望您能够生成我需要的表格。

Table 2: All overrides
+--------+-----------+-------------+---------------+
| userID | productID | overrideMin | overridePrice |
+--------+-----------+-------------+---------------+
| 4      | 13        | null        | 99.99         |
| 4      | 18        | 23          | null          |
| 4      | 53        | null        | 99.99         |
| 4      | 55        | 4           | 99.99         |
| 50     | 55        | 2           | null          |
+--------+-----------+-------------+---------------+

我尝试按userID和productID进行分组，但是由于表1中可能存在表2中不存在的产品ID，所以根据我分组的productID不同会得到不同的结果。

- bentedder

---

你尝试过使用INNER JOIN吗？ - Roy Bogado

Answer 1

使用UNION：

SELECT userID, productID, 
  MAX(overrideMin) AS overrideMin, 
  MAX(overridePrice) AS overridePrice
FROM
(
    SELECT userID, productID, null AS overrideMin, overridePrice
    FROM OverridePrices
    UNION
    SELECT userID, productID, overrideMin, null AS overridePrice
    FROM OverrideMinutes
) AS t
GROUP BY userID, productID;

这将为您提供您所需的确切结果：点击此处。

| userID | productID | overrideMin | overridePrice |
|--------|-----------|-------------|---------------|
|      4 |        13 |      (null) |         99.99 |
|      4 |        18 |          23 |        (null) |
|      4 |        53 |      (null) |         99.99 |
|      4 |        55 |           4 |         99.99 |
|     50 |        55 |           2 |        (null) |

- user9044110

---

使用嵌套选择语句与@vladimir-kovpak的答案中提到的IF语句相比，是否有优势？IF语句“看起来”更简洁。 - bentedder

@bentedder 另一个答案不会给你想要的输出。它只会给你第一个表中的行和ID。 - user9044110

@bentedder - 你可以在这里检查另一个答案的结果：http://sqlfiddle.com/#!9/906276/4 - user9044110

共识！ :) 是的，这个查询似乎给了我想要的结果。我爱互联网。谢谢。 - bentedder

Answer 2

你需要使用 IF 语句：

SELECT
  t1.userID,
  t1.productID,
  if (t2.overrideMin IS NULL, NULL, t2.overrideMin) AS overrideMin,
  if (t1.overridePrice IS NULL, NULL, t1.overridePrice) AS overridePrice
FROM OverridePrices AS t1
LEFT JOIN OverrideMinutes AS t2 ON t1.userID = t2.userID AND t1.productID = t2.productID;

但是，如果table1和table2中有不同的产品，您需要加入包含所有产品的表格，例如：

SELECT
  t1.userID,
  t1.productID,
  if (t2.overrideMin IS NULL, NULL, t2.overrideMin) AS overrideMin,
  if (t1.overridePrice IS NULL, NULL, t1.overridePrice) AS overridePrice
FROM (
  SELECT userID, productID FROM OverridePrices
  UNION
  SELECT userID, productID FROM OverrideMinutes
) AS t0
LEFT JOIN OverridePrices AS t1 ON t0.userID = t1.userID AND t0.productID = t1.productID
LEFT JOIN OverrideMinutes AS t2 ON t0.userID = t2.userID AND t0.productID = t2.productID;

此外，现在您可以使用GROUP，HAVING等。

- cn007b

Answer 3

SELECT table1.userID,table1.productID,table2.overrideMin,table1.overridePrice FROM table1 
LEFT JOIN table2 ON table1.userID=table2.userID AND table1.productID = table2.productID 
UNION SELECT table2.userID,table2.productID,table2.overrideMin,table1.overridePrice FROM table1 
RIGHT JOIN table2 ON table1.userID=table2.userID AND table1.productID = table2.productID 
ORDER BY userID, productID

这是输出

- Sumit