英文:
how to select rows that has same value in one column but different value in another column
问题
我的表格有3列，分别是mid、campaign name和status。我想选择一个mid，该mid应该具有指定的campaign name，并且具有不同的status。
例如，输出应该如下所示 -
| mid | campaign_name | status |
|---|---|---|
| 50f07d3be5e911b | fy24_postal primary campaign_all segments | delivered |
| 50f07d3be5e911b | fy24_postal primary campaign_all segments | delivered |
| 50f07d3be5e911b | fy24_postal primary campaign_all segments | shipped |
我尝试了连接和distinct关键字，但无法获得所需的输出。
SELECT * FROM db_6sense_campaign_member_status
WHERE mid IN (
SELECT T1.mid
FROM db_6sense_campaign_member_status T1 INNER JOIN
db_6sense_campaign_member_status T2 ON T1.mid = T2.mid
WHERE T1.status <> T2.status
) and campaign_name = 'fy24_postal primary campaign_all segments' order by mid
我会感激有关这个问题的帮助，谢谢。
英文:
My table has 3 columns as mid, campaign name and status. I want to select a mid that should have specified campaign name and has different status for that same mid.
For eg output should be like below -
| mid | campaign_name | status |
|---|---|---|
| 50f07d3be5e911b | fy24_postal primary campaign_all segments | delivered |
| 50f07d3be5e911b | fy24_postal primary campaign_all segments | delivered |
| 50f07d3be5e911b | fy24_postal primary campaign_all segments | shipped |
I tried joins, distinct keyword but unable to get desired output.
SELECT * FROM db_6sense_campaign_member_status
WHERE mid IN (
SELECT T1.mid
FROM db_6sense_campaign_member_status T1 INNER JOIN
db_6sense_campaign_member_status T2 ON T1.mid = T2.mid
WHERE T1.status <> T2.status
) and campaign_name = 'fy24_postal primary campaign_all segments' order by mid
I would appreciate a help around this, thanks.
答案1
得分: 1
SELECT mid
FROM db_6sense_campaign_member_status
WHERE campaign_name = 'fy24_postal primary campaign_all segments'
GROUP BY 1
HAVING COUNT(DISTINCT status) > 1
英文:
SELECT mid
FROM db_6sense_campaign_member_status
WHERE campaign_name = 'fy24_postal primary campaign_all segments'
GROUP BY 1
HAVING COUNT(DISTINCT status) > 1
</details>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论