Mysql

從具有兩個以上特定值的表中選擇行

  • February 20, 2020

我有一張桌子,上面有這樣的細節

Userpin  product       Time1      contains
4556     23            7:30         A
4557     23            7:30         B
4558     23            7:30         C
4559     23            7:30         D
4560     23            7:30         E
4561     24            7:30         A
4562     24            7:30         B
4563     24            7:30         C
4564     24            7:30         D
4565     24            7:30         E
4566     25            7:30         A
4567     25            7:30         A
4568     23            7:30         F
4568     23            7:30         G

因此,對於上表,我需要所有包含 (A,B,C,D,E,F) 的產品。

對於給定的表,我期望結果為 23(雖然產品 23 也包含 G,但這並不困擾我。)。

我已經使用 Adventure Works 數據庫在 tempdb 中重新創建了您的表。此程式碼基本上選擇所有列包含(‘A’,‘B’,‘C’,‘D’,‘E’,‘F’)的記錄,然後對

$$ contains $$柱子。當結果為 6 時,記錄必須有一行(‘A’, ‘B’, ‘C’, ‘D’, ‘E’, ‘F’)。


Create Table #Table (Userpin int, product int, [time] time, [contains] varchar(1))
Insert into #Table (Userpin, product, [time], [contains])
Select 4556,     23,            '7:30',         'A' UNION ALL
Select 4557,     23,            '7:30',         'B' UNION ALL
Select 4558,     23,            '7:30',         'C' UNION ALL
Select 4559,     23,            '7:30',         'D' UNION ALL
Select 4560,     23,            '7:30',         'E' UNION ALL
Select 4561,     24,            '7:30',         'A' UNION ALL
Select 4562,     24,            '7:30',         'B' UNION ALL
Select 4563,     24,            '7:30',         'C' UNION ALL
Select 4564,     24,            '7:30',         'D' UNION ALL
Select 4565,     24,            '7:30',         'E' UNION ALL
Select 4566,     25,            '7:30',         'A' UNION ALL
Select 4567,     25,            '7:30',         'A' UNION ALL
Select 4568,     23,            '7:30',         'F' UNION ALL
Select 4568,     23,            '7:30',         'G' 

Select product, count(distinct [contains]) as count_distinct_contains
from #Table
where [contains] In ('A', 'B', 'C', 'D', 'E', 'F')
GROUP BY product 
HAVING count(distinct [contains]) = 6

這是 Microsoft SQL Server 語法。

引用自:https://dba.stackexchange.com/questions/260128