to hdhai9451(新新人类) 老大,您也太巧了吧?我是举个例子,types也许是其它形式,或者记录比较多,不是正好0+1=1的情况!!
select name,count(*) as RecordTotal,sum(types) as TypesTotal from table group by name
select name, count(*) as [总记录数], sum(case types when 1 then 1 else 0 end) as [types是1的记录数] from 表 group by name 或者投机一点:select name, count(*) as [总记录数], sum(types) as [types是1的记录数] from 表 group by name
那这么写Select name, Count(1) As N'总记录数', SUM(Case types When 1 Then 1 Else 0 End) As N'types是1的记录数' from TableName Group By name Order By name
这样types 为1,2都可以计算出来了。
--生成数据 create table #t(name varchar(10),types int) insert into #t select 'a',1 insert into #t select 'a',0 insert into #t select 'b',0 insert into #t select 'b',0 insert into #t select 'c',1 insert into #t select 'c',0 --执行查询 select name, count(*) as [总记录数], sum(case types when 1 then 1 else 0 end) as [types是1的记录数] from #t group by name order by name --输出结果 name 总记录数 types是1的记录数 ---- -------- ---------------- a 2 1 b 2 0 c 2 1
select a.name,a.总记录数,b.types是1的记录数 from (select name,count(*) as 总记录数 from 表 where types='1' group by name ) a,
(select name,count(*) as types是1的记录数 from 表 group by name ) b
where a.name=b.name
select [name],a.counts,count(a.types) as c1 from (select name,count(*) as counts,types from [temp] group by name,types) as a where a.types = 1 group by name,a.countsSQL查询分析器中调试通过
这种数据你也能同的过?好像不行吧!? name types a 1 a 0 > a 1 > a 2 b 0 b 0 c 1 c 0
老大,您也太巧了吧?我是举个例子,types也许是其它形式,或者记录比较多,不是正好0+1=1的情况!!
from table
group by name
name,
count(*) as [总记录数],
sum(case types when 1 then 1 else 0 end) as [types是1的记录数]
from
表
group by
name
或者投机一点:select
name,
count(*) as [总记录数],
sum(types) as [types是1的记录数]
from
表
group by
name
name,
Count(1) As N'总记录数',
SUM(Case types When 1 Then 1 Else 0 End) As N'types是1的记录数'
from TableName
Group By name
Order By name
create table #t(name varchar(10),types int)
insert into #t select 'a',1
insert into #t select 'a',0
insert into #t select 'b',0
insert into #t select 'b',0
insert into #t select 'c',1
insert into #t select 'c',0
--执行查询
select
name,
count(*) as [总记录数],
sum(case types when 1 then 1 else 0 end) as [types是1的记录数]
from
#t
group by
name
order by
name
--输出结果
name 总记录数 types是1的记录数
---- -------- ----------------
a 2 1
b 2 0
c 2 1
select a.name,a.总记录数,b.types是1的记录数 from
(select name,count(*) as 总记录数
from 表
where types='1'
group by name
) a,
(select name,count(*) as types是1的记录数
from 表
group by name
) b
where a.name=b.name
from
(select name,count(*) as counts,types from [temp]
group by name,types) as a where a.types = 1
group by name,a.countsSQL查询分析器中调试通过
name types
a 1
a 0
> a 1
> a 2
b 0
b 0
c 1
c 0