有2个表
a(evid)
b(evid,status)现在sql是 select a.evid ,count(*) from a ,b where a.evid=b.evid and b.status=1 group by a.evid
(这个sql有问题 , 问题如下)
现在发现要的是 按照a.evid 的各个分组下 只能要全部b.status=1的分组请问这个sql如何改写?
a(evid)
b(evid,status)现在sql是 select a.evid ,count(*) from a ,b where a.evid=b.evid and b.status=1 group by a.evid
(这个sql有问题 , 问题如下)
现在发现要的是 按照a.evid 的各个分组下 只能要全部b.status=1的分组请问这个sql如何改写?
a.evid b.status
1 0
1 1
2 1
2 1
如果按照上面的sql a.evid=1会取出来
但是现在的要求是不要a.evid的分组 (确实业务是这样要求的)
不知道哦这个sql如何改写
你的sql 实际上跟我的是一样的哦请看清楚我的问题比如
a.evid b.status
1 0
1 1
2 1
2 1
如果按照上面的sql a.evid=1会取出来
但是现在的要求是不要a.evid的分组 (确实业务是这样要求的)
不知道哦这个sql如何改写
a.evid ,
count(*)
from a ,b
where a.evid=b.evid
and not exists(select 1 from b t where t.evid=b.evid and t.evid=0)
group by a.evid
b表数据
+--------+------+
| status | evid |
+--------+------+
| 1 | 1 |
| 1 | 2 |
| 0 | 3 |
| 0 | 1 |
+--------+------+
4 rows in setselect
a.evid ,
count(*)
from a ,b
where a.evid=b.evid
and not exists(select 1 from b t where t.evid=b.evid and t.evid=0)
group by a.evid结果mysql>
select
a.evid ,
count(*)
from bb a ,bc b
where a.evid=b.evid
and not exists(select 1 from bc t where t.evid=b.evid and t.evid=0)
group by a.evid;
+------+----------+
| evid | count(*) |
+------+----------+
| 1 | 2 |
| 2 | 1 |
| 3 | 1 |
+------+----------+
3 rows in set结果是错误的 啦
我的解决方法是3 层嵌套 ,但是可读性实在太差
要知道 这个a b 表都是大几百万的大表
select * from (select * from
(select a.* ,b.status from bb a, bc b where a.evid=b.evid group by a.evid,b.status) a
group by a.evid having count(*)=1) b
where b.status=1
a.evid ,
count(*)
from a ,b
where a.evid=b.evid
and not exists(select 1 from b t where t.evid=b.evid and t.status=0)
group by a.evid
不好意思,写错了一个字段,再试试
+------+
| evid |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)mysql> select * from b;
+--------+------+
| status | evid |
+--------+------+
| 1 | 1 |
| 1 | 2 |
| 0 | 3 |
| 0 | 1 |
+--------+------+
4 rows in set (0.00 sec)mysql> select a.evid,count(*) from a left join b on a.evid=b.evid where b.status=1 group by a
+------+----------+
| evid | count(*) |
+------+----------+
| 1 | 1 |
| 2 | 1 |
+------+----------+
2 rows in set (0.00 sec)
from (select a.evid evid,sum(b.status) s,count(*) c,b.status st
from a,b
where a.evid=b.evid
group by a.evid) t
where t.s=t.c
and t.st=1;