我的想法是先将几个字段合并起来,并过滤掉重复号码.然后在通过单列统计得出结果.可惜对SQL不大熟悉.
解决方案 »
- 如何根据内容找到字段
- 如何让mssql把.mdf(ldf)文件存在unc地址上?就是别的机器上的硬盘上
- 提示“键列信息不足或不正确,更新影响到多行” ?
- SQL语句加上WHERE和GROUP BY无法执行
- 请教各位高手,sql中行转列怎么写哈
- 请教一下,怎眼用sql语句把smalldatetime(日期)转换为bcd码啊?谢谢!!!
- ******存储过程调用返回相应表的记录条数错误,请达人指点******
- 两张表 插入不重复数据的问题
- 关于sql2000远程访问的问题!!愁死我了!
- 请教数据同步的问题!
- 新手求教:建立一条规则的语句
- 高分求救:如何把MS SQL2005中的存储过程迁移到MS SQL2000上?
insert into @t select 110,110,114,112
insert into @t select 114,112,111,112
select
a.val,
sum(case b.A when a.val then 1 end) A,
sum(case b.B when a.val then 1 end) B,
sum(case b.C when a.val then 1 end) C,
sum(case b.D when a.val then 1 end) D
from
(select A as val from @t union select B from @t union select C from @t union select D from @t) a,
@t b
group by
a.val
order by
a.val/*
110 1 1 NULL NULL
111 NULL NULL 1 NULL
112 NULL 1 NULL 2
114 1 NULL 1 NULL
*/
insert into @t1 select 110,110,114,112
insert into @t1 select 114,112,111,112declare @t2 table(A int,ftype char(1))insert into @t2
select a=A,type='a' from @t1
union all
select a=B,type='b' from @t1
union all
select a=C,type='C' from @t1
union all
select a=D,type='D' from @t1select a=count((case when ftype='a' then 1 else null end )) ,
b=count((case when ftype='b' then 1 else null end )),
c=count((case when ftype='c' then 1 else null end )),
d=count((case when ftype='d' then 1 else null end ))
from @t2 group by a
insert into @t1 select 110,110,114,112
insert into @t1 select 114,112,111,112declare @t2 table(A int,ftype char(1))insert into @t2
select a=A,type='a' from @t1
union all
select a=B,type='b' from @t1
union all
select a=C,type='C' from @t1
union all
select a=D,type='D' from @t1select a,a=count((case when ftype='a' then 1 else null end )) ,
b=count((case when ftype='b' then 1 else null end )),
c=count((case when ftype='c' then 1 else null end )),
d=count((case when ftype='d' then 1 else null end ))
from @t2 group by a