选择具有同一列中相同值但在另一列中具有不同值的行。

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

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关键字,但无法获得所需的输出。

  1. SELECT * FROM db_6sense_campaign_member_status
  2. WHERE mid IN (
  3. SELECT T1.mid
  4. FROM db_6sense_campaign_member_status T1 INNER JOIN
  5. db_6sense_campaign_member_status T2 ON T1.mid = T2.mid
  6. WHERE T1.status <> T2.status
  7. ) 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.

  1. SELECT * FROM db_6sense_campaign_member_status
  2. WHERE mid IN (
  3. SELECT T1.mid
  4. FROM db_6sense_campaign_member_status T1 INNER JOIN
  5. db_6sense_campaign_member_status T2 ON T1.mid = T2.mid
  6. WHERE T1.status &lt;&gt; T2.status
  7. ) and campaign_name = &#39;fy24_postal primary campaign_all segments&#39; order by mid

I would appreciate a help around this, thanks.

答案1

得分: 1

  1. SELECT mid
  2. FROM db_6sense_campaign_member_status
  3. WHERE campaign_name = 'fy24_postal primary campaign_all segments'
  4. GROUP BY 1
  5. HAVING COUNT(DISTINCT status) > 1
英文:
  1. SELECT mid
  2. FROM db_6sense_campaign_member_status
  3. WHERE campaign_name = &#39;fy24_postal primary campaign_all segments&#39;
  4. GROUP BY 1
  5. HAVING COUNT(DISTINCT status) &gt; 1
  6. </details>

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

发表评论

匿名网友

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

确定