select count(*) from users where st=1 and parentId='13825'
select count(*) from users where st=2 and parentId='13825'
select count(*) from users where st=3 and parentId='13825'这几个语句怎么写sql 查询 效率高?我初步想一个sql 都查询出来
这样分着查询 效率应该是最低的
parentId='13825' 有这么一个限定,要用临时表么?
select count(*) from users where st=2 and parentId='13825'
select count(*) from users where st=3 and parentId='13825'这几个语句怎么写sql 查询 效率高?我初步想一个sql 都查询出来
这样分着查询 效率应该是最低的
parentId='13825' 有这么一个限定,要用临时表么?
declare @stc1 int,@stc2 int,@stc3 intselect
@stc1=(sum(case when st when 1 then 1 else 0 end)),
@stc2=(sum(case when st when 2 then 1 else 0 end)),
@stc3=(sum(case when st when 3 then 1 else 0 end))
from users
where st in(1,2,3) and parentId='13825' group by stselect @stc1
union all
select @stc2
union all
select @stc3
sum(case when st when 1 then 1 else 0 end) as stc1,
sum(case when st when 2 then 1 else 0 end) as stc2,
sum(case when st when 3 then 1 else 0 end) as stc3
from
users
where
st in(1,2,3) and parentId='13825'
group by
st
select count(*) from users where st=2 and parentId='13825' and userid=1
select count(*) from users where st=3 and parentId='13825' and sid=15如果条件都是乱的呢? 都从一个表取 不同条件的行数
这个怎么写好呢?
--反正就放到case when后面,你条件变了改一下不就是了,如下:
select st,
sum(case when st=1 and parentId='13825' and pid=12 then 1 else 0 end) as stc1,
sum(case when st=2 and parentId='13825' and userid=1 then 1 else 0 end) as stc2,
sum(case when st=3 and parentId='13825' and sid=15 then 1 else 0 end) as stc3
from users
group by st
--改一下,都是sum不用group by
select
sum(case when st=1 and parentId='13825' and pid=12 then 1 else 0 end) as stc1,
sum(case when st=2 and parentId='13825' and userid=1 then 1 else 0 end) as stc2,
sum(case when st=3 and parentId='13825' and sid=15 then 1 else 0 end) as stc3
from users
(select count(*)c1,parentId from users where st=1 and parentId='13825' and pid=12)a
left joim
(select count(*)c2,parentId from users where st=2 and parentId='13825' and userid=1)b
on a.parentId=b.parentId left join
(select count(*)c3,parentId from users where st=3 and parentId='13825' and sid=15)c
on a.parentId=c.parentId )
表连接都查出来了