select * from(
select *,
(select count(1) from b表 where type=1)paused_count,
(select count(1) from b表 where type=2)running_count,
(select count(1) from b表 where type=3)waiting_count,
from 表A)
where 1=1 and paused_count=0 and running_count=0 and waiting_count=0;这是我根据方法改的.大概能表达我的意思.
1.首先如果只执行内部的select 数据是可以出来的.我把内部select 当成临时数据 查询里面然后增加条件筛选但是问题是如果用paused_count=0或running_count=0或waiting_count=0其中任意一个作为外部条件是可以出来数据的但.只要两两组合在一起数据就会出不来.不报错只是一直在跑.假设这样数据是可以出来的 也就是paused_count几个字段如果是查询出来的就有问题.
select * from(
select *,
1 paused_count,
0 running_count,
0 waiting_count,
from 表A)
where 1=1 and paused_count=0 and running_count=0 and waiting_count=0;
select count(1) into running_count from b表 where type=2;
select count(1) into waiting_count from b表 where type=3;
if paused_count = 0 and running_count = 0 and waiting_count = 0 then
select *
into xxxx
from 表A;
select *,
(select count(1) from b表 where type=1)paused_count,
(select count(1) from b表 where type=2)running_count,
(select count(1) from b表 where type=3)waiting_count,
from 表A)
where 1=1 and paused_count=0 and running_count=0 and waiting_count=0;
这样写是有问题的,当b表数据少的时候还好,但b数据多的时候,必然一直在跑,因为只要a表有一条数据b的所有type=1,2,3的数据,都要被count一次,必然非常慢。可以用2楼的方法如果不想用if
那么就
select count(1) into paused_count from b表 where type=1;
select count(1) into running_count from b表 where type=2;
select count(1) into waiting_count from b表 where type=3;select *,paused_count,running_count ,waiting_count
from 表A
where paused_count=0 and running_count=0 and waiting_count=0;这样应该能满足你的要求了
with t1 as (select *, (select count(1) from b表 where type=1) paused_count,
(select count(1) from b表 where type=2) running_count,
(select count(1) from b表 where type=3) waiting_count
from 表A)
select * From t1
where 1 = 1
and t1.paused_count = 0
and t1.running_count = 0
and t1.waiting_count = 0;