1
learningman 2021-01-23 13:42:53 +08:00
我的思路是读出来然后全部异或(
前提是 service_id 是个数字 |
2
ouyc OP service_id 可以是一个数字。是否还需要连表查询来,再异或。
|
3
7Qi7Qi 2021-01-23 15:27:57 +08:00
先查出来两条 service_id 相同的数据 service_id 集合,再直接判断 flag 值?
|
4
nuistzhou 2021-01-23 17:10:41 +08:00 via iPhone
group by service_id having ?
|
5
danielmiao 2021-01-23 17:30:44 +08:00
如果我没理解错的话:
1. 数据表中有 service_id 相同的数据,但有些 service_id 只有一条; 2. flag 只有 flag1 和 flag2,不存在空的情况; 我的思路是先吧 flag2 的数据查出来,然后去表里查 flag1 且在 flag2 里有的数据。 SELECT * FROM test3 WHERE flag = 'flag1' AND sid IN ( SELECT sid FROM test3 WHERE flag='flag2' ); |
6
zlowly 2021-01-23 19:13:11 +08:00
试试看 group by service_id having count(flag)=sum(strcmp(flag,'flag1'))
|
7
zlowly 2021-01-23 19:23:00 +08:00
搞错了,STRCMP(expr1,expr2)相等时才 0,既然只有 flag1,flag2,所以应该直接时 having sum(strcmp(flag,'flag1'))=0
|
8
liprais 2021-01-23 19:38:56 +08:00
select
* from ( select a, max(case when b = 2 then b end) as flag1, max(case when b = 3 then b end) as flag2 from flags group by a ) dt where flag1 is not null and flag2 is null 行转列过滤一下就行 |
9
iamxmz 2021-01-23 21:20:43 +08:00
select service_id, count(flag), sum(flag) from service_info group by service_id having count(flag) = 1 and sum(flag) = flag1
|
10
PopRain 2021-01-23 22:07:47 +08:00
select service_id, sum(case when flag='flag1' then 1 else 0 end) as f1, sum(case when flag='flag2' then 1 else 0 end) as f2
from service_info group by service_id having sum(case when flag='flag1' then 1 else 0 end)=1 and sum(case when flag='flag2' then 1 else 0 end) =0 |