MySql查询代码从单个ID中获取唯一数据

Question

如何列出仅订购化学品 [NUMBER] 的任何客户的 CUSTNUM 和名称。

订单表

+---------+--------+------------+------+
| CUSTNUM | CHEMNO | DATE       | QTY  |
+---------+--------+------------+------+
|  123456 |   1234 | 2000-00-00 |   35 |
+---------+--------+------------+------+

客户表

+---------+-----------+-----------+
| CUSTNUM | NAME      | LOCATION  |
+---------+-----------+-----------+
|  123456 | AmChem    | New York  |
+---------+-----------+-----------+

Answer 1

您可以加入 CUSTOMER和 ORDERS包含特定 <chemno> 订单的表格使用仅购买产品的 custnum 的子查询:

SELECT
    CUSTNUM, NAME 
FROM
    CUSTOMER c
INNER JOIN
    ORDERS o ON o.CUSTNUM = c.CUSTNUM and o.CHEMNO = <chemno>
INNER JOIN
    ( SELECT
          CUSTNUM 
      FROM
          ORDERS
      GROUP BY
          CUSTNUM 
      HAVING
          COUNT(DISTINCT CHEMNO) = 1 ) t ON t.CUSTNUM = o.CUSTNUM