Select state,Count(*) as ICount From tablename where state=0 or state=1 Group By state或者,变成一条记录: SQL SERVER: Select SUM(CASE WHEN STATE=0 THEN 1 ELSE 0 END) AS COUNT0, SUM(CASE WHEN STATE=1 THEN 1 ELSE 0 END) AS COUNT1 From tablename where state=0 or state=1ACCESS: Select SUM(IIF(STATE=0,1,0)) AS COUNT0, SUM(IIF(STATE=1,1,0)) AS COUNT1 From tablename where state=0 or state=1
你可以先用sql语句把多个表连接生成一张临时表。然后对一张表操作,就简单多了。
select itype, (select count(id) from tblueybook where itype=10) as no1, (select count(id) from tblueybook where itype=20) as no2 from tblueybook group by itype
同意8992026(8992026)的: Select state,Count(*) From tablename where state=0 or state=1 Group By state 就可以了!
使用分组统计即可 select XXX,count(XXX) from YYYY group by XXX, 这里XXX,YYYY均为代指
select state,count(state) from yourtable where state in(0,1) group by state;
是不是需要这样:Select state,Count(*) as ICount From ( select * from tablename1 union all select * from tablename2 ) where ... Group By state
select 0 as state, count(*) as cnt from table1 where state =0 union select 1 as state, count(*) as cnt from table1 where state =1
Select state,Count(*) as ICount From ( select * from tablename1 union all select * from tablename2 ) where ... Group By state这样如果两个表中有完全相同的记录则只计数一个。而我想让两个表中的数目相加。
Select Count(*) From TableName where state=0 Group By stateSelect Count(*) From TableName where state=1 Group By state
参考上面的帖子我写出了这样的语句:select sum(a),state from( select count(id) a, state from table1 where state in(0,1) group by state union all select count(id) a, state from table2 where olcomstate in(0,1) group by state ) group by state order by state查询出来的结果对于两个表中state相同的count(id) a却不相加,而是分成了两个记录。若把table2换成了table1,则结果正确。我猜想,虽然被union all了,但是记录仍然有区别的。请各位指点。
Select Count(*) , state From tablename Group By state HAVING state in ('0','1')
有点不懂,union all应该不会合并记录,用8992026的方法应该可以的,为什么会不行,你测试过吗?(可能和数据库有关,我用SQL SERVER ,)
有点不懂,union all应该不会合并记录,用8992026的方法应该可以的,为什么会不行,你测试过吗?(可能和数据库有关,我用SQL SERVER ,但你没说你用什么) state的大小不一样的解决方法是转到同样大小的类型。select sum(a),state from( select count(id) a,convert(numeric(18,2), state) as state from table1 where state in(0,1) group by convert(numeric(18,2), state) union all select count(id) a, convert(numeric(18,2), state) as state from table2 where olcomstate in(0,1) group by convert(numeric(18,2), state) ) group by state order by state
SQL SERVER:
Select SUM(CASE WHEN STATE=0 THEN 1 ELSE 0 END) AS COUNT0,
SUM(CASE WHEN STATE=1 THEN 1 ELSE 0 END) AS COUNT1
From tablename where state=0 or state=1ACCESS:
Select SUM(IIF(STATE=0,1,0)) AS COUNT0,
SUM(IIF(STATE=1,1,0)) AS COUNT1
From tablename where state=0 or state=1
(select count(id) from tblueybook where itype=10) as no1,
(select count(id) from tblueybook where itype=20) as no2
from tblueybook
group by itype
Select state,Count(*) From tablename where state=0 or state=1 Group By state
就可以了!
select XXX,count(XXX) from YYYY group by XXX,
这里XXX,YYYY均为代指
(
select * from tablename1
union all
select * from tablename2
)
where ...
Group By state
where state =0
union
select 1 as state, count(*) as cnt from table1
where state =1
(
select * from tablename1
union all
select * from tablename2
)
where ...
Group By state这样如果两个表中有完全相同的记录则只计数一个。而我想让两个表中的数目相加。
from(
select count(id) a, state
from table1
where state in(0,1)
group by state
union all
select count(id) a, state
from table2
where olcomstate in(0,1)
group by state
)
group by state
order by state查询出来的结果对于两个表中state相同的count(id) a却不相加,而是分成了两个记录。若把table2换成了table1,则结果正确。我猜想,虽然被union all了,但是记录仍然有区别的。请各位指点。
state的大小不一样的解决方法是转到同样大小的类型。select sum(a),state
from(
select count(id) a,convert(numeric(18,2), state) as state
from table1
where state in(0,1)
group by convert(numeric(18,2), state)
union all
select count(id) a, convert(numeric(18,2), state) as state
from table2
where olcomstate in(0,1)
group by convert(numeric(18,2), state)
)
group by state
order by state