如何选择具有一个列中相同值但另一个列中不同值的行?

huangapple go评论72阅读模式
英文:

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,并且对于同一个mid具有不同的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 &lt;&gt; T2.status
   )  and campaign_name = &#39;fy24_postal primary campaign_all segments&#39;  order by mid

I would appreciate a help around this, thanks.

答案1

得分: 1

从 db_6sense_campaign_member_status 表中选择 mid
WHERE campaign_name = 'fy24_postal primary campaign_all segments'
按第一列进行分组
HAVING COUNT(DISTINCT status) > 1
英文:
SELECT mid
FROM db_6sense_campaign_member_status
WHERE campaign_name = &#39;fy24_postal primary campaign_all segments&#39;
GROUP BY 1
HAVING COUNT(DISTINCT status) &gt; 1

</details>



huangapple
  • 本文由 发表于 2023年8月9日 13:30:32
  • 转载请务必保留本文链接:https://go.coder-hub.com/76864838.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定