gpt4 book ai didi

php - Mysql查询两列不重复

转载 作者:行者123 更新时间:2023-11-28 23:26:16 29 4
gpt4 key购买 nike

我有两个名称为 user1id 和 user2id 的列。我想计算 user1id 和 user2id 中存在的 id,而不重复 userId,例如,如果我的用户 id 是 1: enter image description here

最佳答案

你可以试试这个:

SELECT CASE WHEN t.user_id1 > t.userid2 THEN t.user_id1 ELSE t.user_id2 end as user_1,
CASE WHEN t.user_id1 > t.userid2 THEN t.user_id2 ELSE t.user_id1 end as user_2,
COUNT(*) as cnt
FROM YourTable t
GROUP BY CASE WHEN t.user_id1 > t.userid2 THEN t.user_id1 ELSE t.user_id2 end ,
CASE WHEN t.user_id1 > t.userid2 THEN t.user_id2 ELSE t.user_id1 end

结果看起来像这样:

user_id1 , user_id2 , count
17 1 1
18 1 3
19 1 1
20 1 3

您现在要做的就是从中选择您想要的内容,在本例中为 user_id1,从 ( .. ) 开始计数,其中 user_id2 = 1

编辑:对于 id 为 2 的用户,使用:

SELECT CASE WHEN t.user1id > t.user2id THEN t.user1id ELSE t.user2id end as user_1,
CASE WHEN t.user1id > t.user2id THEN t.user2id ELSE t.user1id end as user_2,
COUNT(*) as cnt
FROM YourTable t
WHERE 2 IN(t.user1id,t.user2id)
GROUP BY CASE WHEN t.user1id > t.user2id THEN t.user1id ELSE t.user2id end ,
CASE WHEN t.user1id > t.user2id THEN t.user2id ELSE t.user1id end

关于php - Mysql查询两列不重复,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/39220495/

29 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com