MySQL查询选择具有相同值的所有字段

问题描述：

每行至少有两行具有相同的conversation_id。我想选择具有相同conversation_id的所有行中的recipient_state字段中具有“删除”的那些字段。

SELECT conversation_id 
FROM xf_conversation_recipient 
GROUP BY recipient_state HAVING (delete in all fields)

以下conversation_id将选择

conversation_id recipient_state 
1    delete 
1    delete

以下conversation_id不会选择

conversation_id recipient_state 
1    delete 
1    active

'每行至少有两行'你需要纠正这个 – Ejaz

我瘦你的问题可能会更清晰，如果你提供一些样本数据 – Randy

我加tw o例子。谢谢。 –

答

SELECT conversation_id, COUNT(DISTINCT recipient_state) AS nb, recipient_state 
FROM xf_conversation_recipient 
GROUP BY conversation_id 
HAVING nb=1 AND recipient_state='delete'

由conversation_id组成的查询组只保留具有1个不同的recipient_state的记录，而recipient_state等于'delete'。

这是完美的。谢谢Jocelyn。我非常感谢你分享你的知识。 –

答

最好我能想出是自左联接：

SELECT DISTINCT r1.conversation_id 
FROM xf_conversation_recipient AS r1 
LEFT JOIN xf_conversation_recipient AS r2 ON r2.conversation_id = r1.conversation_id AND r2.recipient_state != 'delete' 
WHERE r1.recipient_state = 'delete' AND r2.conversation_id IS NULL

基本上，抓住没有没有“删除”作为状态的匹配行的所有行。

非常接近我的想法。但是，我会执行DISTINCT对话ID，并且预先确定您希望至少具有1个“删除”条目的对话（通过WHERE r1.recipient_state ='delete'）条款。为什么要查询10k条目，如果只有200条条目具有“删除”状态。否则，我只会用计数（*）与计数（'删除'状态）条目查询整个文件，并且只有在计数相同时才保留。 – DRapp

谢谢你，阿德里安。我试过你的查询但没有结果。我不明白你添加的r1和r2值。通常，当使用连接时，我会在该字段之前添加表名。 –

你好，Drapp。你会如此善良以提供一个例子。谢谢。 –

答

给定一个表xf_conversation_recipient，看起来像这样：

+----------------+----------------+ 
|conversation_id |recipient_state | 
+----------------+----------------+ 
|1    |delete   | 
|1    |delete   | 
|2    |active   | 
|2    |delete   | 
|3    |delete   | 
|3    |delete   | 
|4    |active   | 
|4    |delete   | 
|5    |active   | 
|5    |active   | 
|6    |delete   | 
|6    |delete   | 
+----------------+----------------+

下面的查询返回的所有会话的ID符合您的条件

SELECT 
    conversation_id AS selectedId, 
    count(*) AS count  
FROM xf_conversation_recipient 
WHERE recipient_state = "delete" 
GROUP BY conversation_id 
HAVING count>1

+-----------+------+ 
|selectedId |count | 
+-----------+------+ 
|1   |2  | 
|3   |2  | 
|6   |2  | 
+-----------+------+

注，取决于您的应用程序，您可能想要停在这里。

筑巢这个查询，我们可以只提取selectedId列，并再次筑巢它，我们可以使用它作为一个查询的IN条件，就像这样：

SELECT * FROM xf_conversation_recipient 
WHERE xf_conversation_recipient.conversation_id IN (
    SELECT t1.selectedId FROM (
     SELECT 
      conversation_id AS selectedId, 
      count(*) AS count 
     FROM xf_conversation_recipient 
     WHERE recipient_state = "delete" 
     GROUP BY conversation_id 
     HAVING count>1 
    ) t1 
)

将返回：

+----------------+----------------+ 
|conversation_id |recipient_state | 
+----------------+----------------+ 
|1    |delete   | 
|1    |delete   | 
|3    |delete   | 
|3    |delete   | 
|6    |delete   | 
|6    |delete   | 
+----------------+----------------+

MySQL查询选择具有相同值的所有字段

相关推荐