MySQL 从同一列中选择多个值的 SQL 查询

SQL query to select from same column but multiple values

提问by Rupak Banerjee

This is my table.

这是我的桌子。

id  customer        product 
1   Tizag           Pen 
4   Gerald Garner   19" LCD Screen  
5   Tizag           19" LCD Screen  

I want to select the customer who has both "Pen" and '19" LCD Screen'. So, the result with be customer 'Tizag'.

我想选择同时拥有“笔”和“19”液晶屏的客户。所以，结果是客户“Tizag”。

How can I do this.

我怎样才能做到这一点。

Thanks And Regards, Rupak Banerjee.

感谢和问候， Rupak Banerjee。

回答by Ryan P

Try this:

尝试这个：

SELECT customer FROM table t1
JOIN table t2 USING( customer )
WHERE t1.product = 'Pen'
AND t2.product = '19" LCD Screen'

However, the query needs to change if you change the number of products. There might be a better way to do this, but I think this will work.

但是，如果更改产品数量，则需要更改查询。可能有更好的方法来做到这一点，但我认为这会奏效。

回答by Cade Roux

Naively:

天真：

SELECT DISTINCT customer FROM tblname t1
WHERE EXISTS (SELECT * FROM tblname WHERE product = 'Pen' AND customer = t1.customer)
    AND EXISTS (SELECT * FROM tblname WHERE product = '19" LCD Screen' AND customer = t1.customer)

But I think in general, there's more to this kind of question and you need to give more details as to the environment and the range of parameters.

但我认为总的来说，这类问题还有更多，您需要提供有关环境和参数范围的更多详细信息。

Please note, that I am not necessarily advocating this approach but I give it (and denote it as naive, meaning that it is very simple and takes very little into account in terms of table indexing) because:

请注意，我不一定提倡这种方法，但我给出了它（并将其表示为幼稚，这意味着它非常简单并且很少考虑表索引），因为：

1. It's very portably ANSI - nothing special going on
2. It is very clear (without joins) for a SQL beginner to parse and get understanding in a set-based manner
3. It is obviously extensible by parameters (perhaps for dynamic SQL)

1. 这是非常便携的 ANSI - 没什么特别的
2. SQL初学者以基于集合的方式解析和理解是非常清楚的（没有连接）
3. 显然是可以通过参数扩展的（也许对于动态SQL）

The drawbacks are that:

缺点是：

1. Table scans, who knows how bad this execution plan is going to be
2. Correlated subqueries, relating to problems with #1
3. Verbosity, repetition
4. Any SELECT * (even inside an EXISTS) is likely to raise an eyebrow

1. 表扫描，谁知道这个执行计划会有多糟糕
2. 相关子查询，与#1 的问题有关
3. 冗长、重复
4. 任何 SELECT * （甚至在 EXISTS 中）都可能引起注意

回答by Teja

SELECT CUSTOMER
FROM YOURTABLE yt1,YOURTABLE yt2
WHERE yt1.customer=yt2.customer
  AND yt1.product='Pen'
  AND yt2.product=''19" LCD Screen';