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 語法。