100分请教一个简单的统计问题.搞定就结.

[mastersky]

数据如下:
FuncID C IDStr
----------- --------- --------------------
101 1 1101000000
101 1 1100000000
101 1 1110000000
101 1 1100000000
104 0 0110000000
104 1 1110000000
104 0 0110000000
301 0 1101000000
301 0 0000100000

如何写一个统计的SQL语句,按照FuncID统计.
统计时:
如果C中有一个是1,则该值为1
IDStr如果该位有一个1则该位值为1
比如部分结果如下:
--------------------------------
101 1 1111000000
104 1 1110000000
.....
301 0 1101100000



C是bit型
IDStr是Varchar型

[mastersky]

gahade(我们了解历史时,我们已成为历史) 的方法不错,可是我的t2是动态查询出来的,不好写成固定的视图.还是什么好办法呢.

[saiwong]

我同意鱼的观点

[gahade]

select funcid,max(c) as c,dbo.f_t2(funcid) as idstr
from 子查询或者临时表
group by funcid

[wyfccc]

简单的问题还那上来问？自己搞定吧

[mastersky]

select funcid,max(c) as c,dbo.f_t2(funcid) as idstr
from 子查询或者临时表
group by funcid

函数里包含了表名,再想别的办法了.谢谢各位.解决了就揭帖.

[zheninchangjiang]

给function加个你那个动态查询过滤的条件参数,就能保持前后一致了啊

[mengmou]

--创建测试环境
create table t(FuncID int,C bit,IDStr varchar(20))

--插入测试数据
insert t(FuncID,C,IDStr)
select '101','1','1101000000' union all
select '101','1','1100000000' union all
select '101','1','1110000000' union all
select '101','1','1100000000' union all
select '104','0','0110000000' union all
select '104','1','1110000000' union all
select '104','0','0110000000' union all
select '301','0','1101000000' union all
select '301','0','0000100000'

--求解过程
select *,convert(varchar(20),'') as str into #t from t

while @@rowcount > 0
update _t
set str = str
+ (select max(left(idstr,1)) from #t where funcid = _t.funcid)
,idstr = stuff(idstr,1,1,'')
from #t _t
where idstr <> ''

select funcid,max(case c when 1 then 1 else 0 end),str from #t
group by funcid,str
order by funcid

--删除测试环境
drop table t,#t

/*--测试结果
funcid str
----------- ----------- --------------------
101 1 1111000000
104 1 1110000000
301 0 1101100000

（所影响的行数为 3 行）
*/

[mengmou]

--优化一下，比楼上效率高一点。
--创建测试环境
create table t(FuncID int,C bit,IDStr varchar(20))

--插入测试数据
insert t(FuncID,C,IDStr)
select '101','1','1101000000' union all
select '101','1','1100000000' union all
select '101','1','1110000000' union all
select '101','1','1100000000' union all
select '104','0','0110000000' union all
select '104','1','1110000000' union all
select '104','0','0110000000' union all
select '301','0','1101000000' union all
select '301','0','0000100000'

--求解过程
select funcid,max(case c when 1 then 1 else 0 end) as c,convert(varchar(20),'') as str
into #t
from t
group by funcid

declare @i int,@len int select @i = 0,@len = max(len(idstr)) from t

while @@rowcount > 0
begin
set @i = @i + 1
update _t
set str = str
+(select max(substring(idstr,@i,1)) from t where funcid = _t.funcid)
from #t _t
where @i <= @len
end

select * from #t order by funcid

--删除测试环境
drop table t,#t

/*--测试结果
funcid str
----------- ----------- --------------------
101 1 1111000000
104 1 1110000000
301 0 1101100000

（所影响的行数为 3 行）
*/

[gahade]

create table t2(funcid int,c int,idstr bigint)
insert into t2
select 101,1,1101000000
union all select 101,1,1100000000
union all select 101,1,1110000000
union all select 101,1,1100000000
union all select 104,0,0110000000
union all select 104,1,1110000000
union all select 104,0,0110000000
union all select 301,0,1101000000
union all select 301,0,0000100000

create function f_t2(@idstr bigint)
returns bigint
as
begin
declare @s varchar(10)
select @s=rtrim(@idstr)
return case when substring(@s,1,1)>'0' then '1' else '0' end+
case when substring(@s,2,1)>'0' then '1' else '0' end+
case when substring(@s,3,1)>'0' then '1' else '0' end+
case when substring(@s,4,1)>'0' then '1' else '0' end+
case when substring(@s,5,1)>'0' then '1' else '0' end+
case when substring(@s,6,1)>'0' then '1' else '0' end+
case when substring(@s,7,1)>'0' then '1' else '0' end+
case when substring(@s,8,1)>'0' then '1' else '0' end+
case when substring(@s,9,1)>'0' then '1' else '0' end+
case when substring(@s,10,1)>'0' then '1' else '0' end
end

select funcid,max(c) as c,dbo.f_t2(sum(idstr)) as idstr
from t2
group by funcid

[mastersky]

如果IDStr有200个字符,那得写多长啊.我只截取了10个字符啊.后面还有190个字符没写出来.

[paoluo]

Create Table TEST
(FuncID Int,
C Bit,
IDStr Varchar(20))
Insert TEST Select 101, 1, '1101000000'
Union All Select 101, 1, '1100000000'
Union All Select 101, 1, '1110000000'
Union All Select 101, 1, '1100000000'
Union All Select 104, 0, '0110000000'
Union All Select 104, 1, '1110000000'
Union All Select 104, 0, '0110000000'
Union All Select 301, 0, '1101000000'
Union All Select 301, 0, '0000100000'
GO
Select
FuncID,
Max(Cast(C As Int)) As C,
Rtrim(Max(Left(IDStr, 1))) +
Rtrim(Max(Substring(IDStr, 2, 1))) +
Rtrim(Max(Substring(IDStr, 3, 1))) +
Rtrim(Max(Substring(IDStr, 4, 1))) +
Rtrim(Max(Substring(IDStr, 5, 1))) +
Rtrim(Max(Substring(IDStr, 6, 1))) +
Rtrim(Max(Substring(IDStr, 7, 1))) +
Rtrim(Max(Substring(IDStr, 8, 1))) +
Rtrim(Max(Substring(IDStr, 9, 1))) +
Rtrim(Max(Substring(IDStr, 10, 1))) As IDStr
From
TEST
Group By FuncID
GO
Drop Table TEST
--Result
/*
FuncID C IDStr
101 1 1111000000
104 1 1110000000
301 0 1101100000
*/

[jinanjiang]

up

[paoluo]

mastersky(浪) ( ) 信誉：100 Blog 2007-03-14 10:36:10 得分: 0


如果IDStr有200个字符,那得写多长啊.我只截取了10个字符啊.后面还有190个字符没写出来.


----------
哦，開始沒看到這個，我寫的那個也有問題了，不好意思。

[mastersky]

谢谢 gahade(我们了解历史时,我们已成为历史) 和 paoluo(一天到晚游泳的鱼)

你们已经帮了不少忙了.就是那个200个字符的怎么合并的问题,我一直没有想到好方法.

[no_mIss]

愣是没看明白什么意思?
先取C最大的,再取IDStr最大的?

[gahade]

改进一下,支持200个长度的.我那种按sum的取法是错的,应该是鱼老大的按max取才对!
drop table t2
create table t2(funcid int,c int,idstr varchar(200))
insert into t2
select 101,1,'1101000000101'
union all select 101,1,'1100000000010'
union all select 101,1,'1110000000000'
union all select 101,1,'1100000000000'
union all select 104,0,'011000000011'
union all select 104,1,'111000000000'
union all select 104,0,'011000000001'
union all select 301,0,'11010000000'
union all select 301,0,'00001000001'

alter function f_t2(@funcid int)
returns varchar(200)
as
begin
declare @s varchar(200)
set @s=''
declare @i int
set @i=1
declare @len int
select @len=len(max(idstr)) from t2 where funcid=@funcid
while @i<=@len
begin
select @s=@s+max(substring(idstr,@i,1))
from t2
where funcid=@funcid
set @i=@i+1
end
return @s
end

select funcid,max(c) as c,dbo.f_t2(funcid) as idstr
from t2
group by funcid

[prcgolf]

up

[巴拉莱卡]

怎么看起来有点像移位算法