SQL SELECT 产品可在多个国家/地区使用

Question

我有一张带有市场和价格的水果表(表格实际上只是一个摘录(

Product Market  Price
Apple   UK  4
Apple   DE  5
Apple   US  4
Apple   IT  3
Banana  US  2
Orange  UK  1
Kiwi    ES  3
Kiwi    DE  10
Kiwi    US  12
Kiwi    UK  11
Cucumb  IT  5
Cucumb  DE  4
Cucumb  UK  3
Peach   IT  12
Peach   DE  10
Peach   UK  10
Peach   US  11

现在，我只想选择(或分组(在德、英国、意大利和美国这四个市场提供的产品。这应该导致下表：

Product Market  Price
Apple   UK  4
Apple   DE  5
Apple   US  4
Apple   IT  3
Peach   IT  12
Peach   DE  10
Peach   UK  10
Peach   US  11

我已经尝试过分组依据+拥有+计数不同，但它不起作用。见下文

SELECT
market, product, AVG(price) as pr
FROM
fruits
WHERE       
market IN (DE, IT, UK, US)
GROUP BY
market, product
HAVING Count (DISTINCT market=4)

我想我以错误的方式使用计数不同。 请帮忙。谢谢！

Answer 1

具有 - 条件Count (DISTINCT market=4)应该是：

SELECT
market, product, AVG(price) as pr
FROM
fruits
WHERE       
market IN (DE, IT, UK, US)
GROUP BY
market, product
HAVING Count (DISTINCT market) = 4

Answer 2

您的产品没有重复的国家/地区。 我会推荐简单的窗口功能：

select f.*
from (select f.*, count(*) over (partition by product) as cnt
from fruits f
where market in ('DE', 'IT', 'UK', 'US')
) f
where cnt = 4;