sqloracle-databasegroup-by

An SQL problem, there are multiple products in an order

An SQL problem, there are multiple products in an order. You want to query all order information including product a, product B, and product C. Thank you very much for your help database:oracle Order Details

orderNo  goods 
1001     A
1001    B
1001     C
1001     D
1002    A
1003     A1
1003    B1
1003     C
1003     D
1004    A

………… How can I find 1001?

Thank you very much for your help!

Solution

  • You need GROUP BY with HAVING as follows:

    SELECT ORDERNO
FROM YOUR_TABLE
WHERE
    GOODS IN ( 'A', 'B', 'C')
GROUP BY ORDERNO
HAVING COUNT(DISTINCT GOODS) = 3