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

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

ORDERS表

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

CUSTOMER表

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

最佳答案

您可以将包含特定CUSTOMER订单的ORDERS和<chemno>表与仅购买产品的储钱罐子查询结合在一起：

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