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,0000100000create 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
endselect funcid,max(c) as c,dbo.f_t2(sum(idstr)) as idstr
from t2
group by funcid
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,0000100000create 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
endselect funcid,max(c) as c,dbo.f_t2(sum(idstr)) as idstr
from t2
group by funcid
解决方案 »
- 超简单,请问怎么在一个表中同时update更新两个数据!
- 再问个SQL的update 问题
- 求条简单的sql
- 求一SQL语句或存储过程。
- Access 2013尝试与服务器http://通信时客户端发生错误,如何解决?无法使用web模版
- 备份数据库,解决马上给分
- 各位我写个触发器,两个表的结构基本相同,表A :ID name SEX,表B :ID name sex ,要求对一个表进行了增删改后,另个表就能增删改,这个触
- 关于拉订阅问题,高手来
- 有sql语言写函数求面积(在线等)???????
- 急救:SQL Server2000 个人版安装时挂载问题
- ???VFP3.0中的数据类型说明,谁有???
- 自定义函数的参数默认值不能使用,大侠们帮忙看看啊!
(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
*/
如果IDStr有200个字符,那得写多长啊.我只截取了10个字符啊.后面还有190个字符没写出来.
----------
哦,開始沒看到這個,我寫的那個也有問題了,不好意思。
愣是没看明白什么意思?
先取C最大的,再取IDStr最大的?
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
endselect funcid,max(c) as c,dbo.f_t2(funcid) as idstr
from t2
group by funcid
from 子查询或者临时表
group by funcid
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 twhile @@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 funciddeclare @i int,@len int select @i = 0,@len = max(len(idstr)) from twhile @@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
endselect * from #t order by funcid--删除测试环境
drop table t,#t/*--测试结果
funcid str
----------- ----------- --------------------
101 1 1111000000
104 1 1110000000
301 0 1101100000(所影响的行数为 3 行)
*/