Mysql
從具有兩個以上特定值的表中選擇行
我有一張桌子，上面有這樣的細節
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 語法。