table
fld1 fld2 fld3
a 10001 0
b 10002 0
a 10003 1
d 10004 1
b 10005 0
f 10006 0
d 10007 1
a 10008 1
效果:(fld3=1)
fld1 count
a 2
b 0
d 1
f 0
要求用 group by 和 count
fld1 fld2 fld3
a 10001 0
b 10002 0
a 10003 1
d 10004 1
b 10005 0
f 10006 0
d 10007 1
a 10008 1
效果:(fld3=1)
fld1 count
a 2
b 0
d 1
f 0
要求用 group by 和 count
解决方案 »
- 看不懂FULL-TEXT SEARCH的代码,求解释,注解。
- 菜鸟问题:怎么样查询一个表中字段的个数?
- 请问 left join 和 left outer join 的区别
- 菜鸟提问,高手请答:一个小查询
- 求一条查询语句,分组取最后3条,并编号
- 请教关于UNION和UNION ALL的使用与效率问题
- sql数据库里面删除数据库后如何恢复数据库的mdf文件
- 我用JAVA 连SQL 2000 总是报错误,麻烦大家帮忙看下,高手指点指点。
- SQL语句怎么变形呢???好好的SQL语句放到SQL查询分析器中进行语法检查后就变成了另外的样子??奇怪!?
- 一个sqlserver诡异问题求助
- 为什么我装的SQL2005无法还原以前的备份啊
- 十万火急!关于权限问题!
distinct(cyks) cyks,
(select count(cyks)
from
tab
where fldOther='aa' and cyks=a.cyks) N'count'
from tab a
order by cyks不用Group By
from tab group by cyks order by cyks你测试过没有?
insert @t select 'a', '10001', 0
insert @t select 'b', '10002', 0
insert @t select 'a', '10003', 1
insert @t select 'd', '10004', 1
insert @t select 'b', '10005', 0
insert @t select 'f', '10006', 0
insert @t select 'd', '10007', 1
insert @t select 'a', '10008', 1 select distinct fld1,[count]=(select count(1) from @t where fld1=a.fld1 and fld3=1) from @t a
(
fld1 varchar(10) , fld2 varchar(10) , fld3 varchar(10)
)
go
insert into #t
select 'a','10001' , '0'
union all
select 'b','10002' , '0'
union all
select 'a','10003' , '1'
union all
select 'd','10004' , '1'
union all
select 'b','10005' , '0'
union all
select 'f','10006' , '0'
union all
select 'd','10007' , '1'
union all
select 'a','10008' , '1'select fld1 , count(*) as count from #t group by fld1
(
fld1 varchar(10) , fld2 varchar(10) , fld3 varchar(10)
)
go
insert into #t
select 'a','10001' , '0'
union all
select 'b','10002' , '0'
union all
select 'a','10003' , '1'
union all
select 'd','10004' , '1'
union all
select 'b','10005' , '0'
union all
select 'f','10006' , '0'
union all
select 'd','10007' , '1'
union all
select 'a','10008' , '1'
select * from #t select B.fld1,isnull(A.num,0) as count
from
(select fld1,count(*) as num from #t where fld3='1' group by fld1) A right join
(select distinct fld1 from #t) B on A.fld1=B.fld1
insert @t select 'a', '10001', 0
insert @t select 'b', '10002', 0
insert @t select 'a', '10003', 1
insert @t select 'd', '10004', 1
insert @t select 'b', '10005', 0
insert @t select 'f', '10006', 0
insert @t select 'd', '10007', 1
insert @t select 'a', '10008', 1select A.fld1,isnull(B.count,0) as count from
(select distinct fld1 from @t) A left join
(select fld1,count=(select count(1) from @t where A.fld1=fld1)-1
from @t A
where fld3=1
group by fld1 ) B
on A.fld1=B.fld1
declare @t table(fld1 varchar(10),fld2 varchar(10),fld3 varchar(10))
insert @t select 'a', '10001', 0
insert @t select 'b', '10002', 0
insert @t select 'a', '10003', 1
insert @t select 'd', '10004', 1
insert @t select 'b', '10005', 0
insert @t select 'f', '10006', 0
insert @t select 'd', '10007', 1
insert @t select 'a', '10008', 1select fld1,sum(case when fld3=1 then 1 else 0 end) as [count]
from @t group by fld1 order by fld1--结果
fld1 count
---------- -----------
a 2
b 0
d 2
f 0(所影响的行数为 4 行)
declare @t table(fld1 varchar(10),fld2 varchar(10),fld3 varchar(10))
insert @t select 'a', '10001', 0
insert @t select 'b', '10002', 0
insert @t select 'a', '10003', 1
insert @t select 'd', '10004', 1
insert @t select 'b', '10005', 0
insert @t select 'f', '10006', 0
insert @t select 'd', '10007', 1
insert @t select 'a', '10008', 1select fld1,count(distinct case when fld3=1 then fld2 else null end)
as [count]
from @t group by fld1 order by fld1--结果
fld1 count
---------- -----------
a 2
b 0
d 2
f 0(所影响的行数为 4 行)
Haiwer(海阔天空) 都可以~ :)
(select fld1,fld3 from [table] where fld3=1)
group by fld1
union
select fld1,0 from [table] where fld1 not in
(select distinct fld1 from [table] where fld3 = 1)