问题描述:

I have a database with multiple items in it. Some sample entries from the database include:

Items

name price

Flashlight 15

Flashlight 20

Flashlight 21

Canoe 500

Canoe 500

Tent 60

Hotdog 5

Tent 45

I would like to retrieve only the items with multiple prices. So I want the table to be returned to look like:

Return_Table

name price

Flashlight 15

Flashlight 20

Flashlight 21

Tent 60

Tent 45

I don't want the items that just have one price, just the ones with multiple prices and all their different prices.

I know that I can do this with a script and several SQL queries, but I'm wondering if there is a way I could do this with just a SQL query and no scripting. I'm still learning SQL, so I don't know all the tricks yet. If it's not possible to do with just one SQL query please tell me so, I'm starting to think it's impossible.

网友答案:

You can use the following query:

SELECT name, price
FROM Items
WHERE name IN (
  SELECT name
  FROM Items
  GROUP BY name
  HAVING MIN(price) <> MAX(price))

Demo here

网友答案:

You could create a subquery containing the items with multiple prices, and filter the original list of items by inner-joining to the subquery, e.g.

SELECT
   Items.*
FROM Items
INNER JOIN
    (SELECT
      name
    FROM Items
    HAVING COUNT(DISTINCT price) > 1) multiPriceItems
ON Items.name = multiPriceItems.name
网友答案:
SELECT DISTINCT x.* 
           FROM items x
           JOIN items y 
             ON y.name = x.name 
            AND y.price <> x.price
相关阅读:
Top