求集合中最大公共元素的SQL语句

Question

我有一个类似

id contact value
1  A       2
2  A       3
3  B       2
4  B       3
5  B       4
6  C       2

现在我想获取给定联系人组的共同最大值的表格。 例如： 如果我的联系人集是 {A,B} 它将返回 3； 对于集合 {A,C} 它将返回 2 对于集合 {B} 它将返回 4

哪些 SQL 语句可以做到这一点？

Answer 1

试试这个：

  SELECT value, count(distinct contact) as cnt
    FROM my_table
   WHERE contact IN ('A', 'C')
   GROUP BY value
   HAVING cnt = 2
   ORDER BY value DESC
   LIMIT 1

这是 MySQL 语法，可能因您的数据库而异。 HAVING 子句中的数字 (2) 是集合中元素的数量。

Answer 2

SELECT max(value) FROM table WHERE contact IN ('A', 'C')

编辑：最大共同点

declare @contacts table ( contact nchar(10) )


insert into @contacts values ('a')
insert into @contacts values ('b')


select MAX(value)
from MyTable
where (select COUNT(*) from @contacts) = 
      (select COUNT(*) 
       from MyTable t
       join @contacts c on c.contact = t.contact
       where t.value = MyTable.value)

Answer 3

大多数人会告诉您使用：

  SELECT MAX(t.value)
    FROM TABLE t
   WHERE t.contact IN ('A', 'C')
GROUP BY t.value
  HAVING COUNT(DISTINCT t.*) = 2

几个注意事项：

• DISTINCT 是关键，否则您可能会有两行 t.contact = 'A'。
• COUNT(DISTINCT t.*) 的数量必须等于 IN 子句中指定的值的数量

我的首选是使用 JOIN：

  SELECT MAX(t.value)
    FROM TABLE t
    JOIN TABLE t2 ON t2.value = t.value AND t2.contact = 'C'
   WHERE t.contact = 'A'

这样做的缺点是您必须对每个条件（在本例中为联系人值）进行自联接（联接到同一个表）。