100分请教一个简单的统计问题.搞定就结.
数据如下:
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型
[解决办法]
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
[解决办法]
Create Table TEST
(FuncIDInt,
CBit,
IDStrVarchar(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
/*
FuncIDCIDStr
10111111000000
10411110000000
30101101100000
*/
[解决办法]
up
[解决办法]
mastersky(浪) ( ) 信誉：100 Blog 2007-03-14 10:36:10 得分: 0
如果IDStr有200个字符,那得写多长啊.我只截取了10个字符啊.后面还有190个字符没写出来.
----------
哦，開始沒看到這個，我寫的那個也有問題了，不好意思。
[解决办法]
愣是没看明白什么意思?
先取C最大的,再取IDStr最大的?
[解决办法]
改进一下,支持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
[解决办法]
select funcid,max(c) as c,dbo.f_t2(funcid) as idstr
from 子查询或者临时表
group by funcid
------解决方案--------------------
--创建测试环境
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 行）
*/
[解决办法]
--优化一下，比楼上效率高一点。
--创建测试环境
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 行）
*/
