如何为以下表格编写SQL查询？

Question

我的表格定义如下：

名称是一个字符串，也是属性。

ID | Name | Property

这个表格中的一个数据示例如下：

ID | Name | Property
1    Peter  Newsletter
2    Paul   Register
3    Peter  Register
4    Shaun  Newsletter
5    Steve  Register

现在我想查询所有拥有newsletter和register属性的人。结果应该返回Peter，因为他两个属性都有。

所以结果表格应该如下：

ID | Name | Property
1    Peter  Newsletter
3    Peter  Register

我试图查询哪个人即订阅了物业通讯又注册了账号。

- PreDer

Answer 1

以下是一种方法：

select t.*
from table t
where exists (select 1
              from table t2
              where t2.name = t.name and t2.property = 'NewsLetter'
             ) and
      exists (select 1
              from table t2
              where t2.name = t.name and t2.property = 'Register'
             );

如果您只需获取名称列表，可能带有id，我会这样做：

代码如下：

select t.name
from table t
where t2.property in ('NewsLetter', 'Register')
group by t.name
having count(distinct property) = 2;

获取id列表的方式取决于您的数据库，类似于listagg()、group_concat()或string_agg()。

- Gordon Linoff

Answer 2

一种替代方案，基本上与Gordon的解决方案相同，但不使用EXISTS：

select * from tablename
where name in (select name from tablename where property = 'Newsletter')
and name in (select name from tablename where property = 'Register')

- shree.pat18

---

我会尝试你们两种方法，现在我遇到了另一个问题。请给我一些时间，我会把最有效的方法作为解决方案 =) 到目前为止非常感谢你们。 - PreDer

Answer 3

如果没有更多数据了解，很难确定。根据您提供给我们的确切要求，这将产生您展示的结果：

WITH multprop (multName) AS (
   SELECT NAME FROM myTable
       WHERE Property IN('Newsletter','Register')
       GROUP BY NAME
       HAVING count(*)>1 )
select id, Name, Property
 from multprop inner join myTable
      on multName = Name

但是，你的需求稍有不同就会造成混乱。例如，除了你列出的两个属性值之外，是否会出现其他属性值？或者一个名称是否可以多次显示相同的属性值？

编辑：添加的WHERE子句将CTE中的行限制为请求的特定属性值集。这是来自评论中更详细的要求。

- user2338816

---

有更多的属性超出我列出的两个，但这两个是我感兴趣的。一个名称不能以相同的属性多次显示。 - PreDer

您的评论缩小了可能性，因此WHERE子句现在限制了CTE（公共表达式，即WITH子句）中的行数。使用CTE是一种值得学习的替代语法。IN()谓词可以是子查询而不是显式列表，以便所需的Property列表可以来自临时表。 - user2338816

Answer 4

还有一种方法：

SELECT * FROM T as T1
WHERE Property IN ('Newsletter','Register')
      AND EXISTS (SELECT * FROM T 
                      WHERE Name=T1.Name 
                            and Property IN ('Newsletter','Register')
                            and Property <> T1.Property
                 )

SQLFiddle演示

- valex

Answer 5

另一个，作为记录。

WITH cteHasBoth
 as (select Name
       from MyTable
       where Property in ('Newsletter', 'Register')
       group by Name
       having count(*) = 2)
 select ID, Name
  from MyTable
  where name in (select Name from cteHasBoth)

这只需要通过表格进行两次扫描。

- Philip Kelley