求数量的问题

helinhai 2010-10-20 01:41:15

dianhao A B C D E F
10211034 NULL NULL NULL NULL 3;5;6;7 NULL
10211529 2;10 10 2;10 2;10 NULL NULL
10211536 5;6;8 NULL 5;6;8 6;9;10 NULL NULL



需要的数据
dianhao A B C D E F
10211034 0 0 0 0 4 0
10211529 2 1 2 2 0 0
10211536 3 0 3 3 0 0


也就是根据";"号来区分有多少个



















...全文
96 点赞 收藏 9
写回复
9 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
chenxippt 2010-10-20

select isnull((len('2;3;4')-len(replace('2;3;4',';','')))+1,0)
回复
hello926 2010-10-20
Create table dot(
dianhao varchar(10),
A VARCHAR(10),
B VARCHAR(10),
C VARCHAR(10),
D VARCHAR(10),
E VARCHAR(10),
F VARCHAR(10)
)
go
insert into dot
select '10211034' , NULL , NULL , NULL ,NULL , '3;5;6;7' , NULL union all
select '10211529' , '2;10 ' , '10', '2;10', '2;10 ', NULL , NULL union all
select '10211536' , '5;6;8', NULL , '5;6;8' , '6;9;10' , NULL , NULL

查询语句

select dianhao,
CASE WHEN A IS NULL THEN 0 ELSE ISNULL(len(A)-LEN(replace(A,';','')),0)+1 END AS 'A',
CASE WHEN B IS NULL THEN 0 ELSE ISNULL(len(B)-LEN(replace(B,';','')),0)+1 END AS 'B',
CASE WHEN C IS NULL THEN 0 ELSE ISNULL( len(C)-LEN(replace(C,';','')),0)+1 END AS 'C',
CASE WHEN D IS NULL THEN 0 ELSE ISNULL( len(D)-LEN(replace(D,';','')),0)+1 END AS 'D',
CASE WHEN E IS NULL THEN 0 ELSE ISNULL( len(E)-LEN(replace(E,';','')),0)+1 END AS 'E',
CASE WHEN F IS NULL THEN 0 ELSE ISNULL( len(F)-LEN(replace(F,';','')),0)+1 END AS 'F'
FROM dot
回复
dawugui 2010-10-20
create table tb([dianhao] int,[A] varchar(5),[B] int,[C] varchar(5),[D] varchar(6),[E] varchar(7),[F] varchar(7))
insert tb
select 10211034,null,null,null,null,'3;5;6;7',null union all
select 10211529,'2;10',10,'2;10','2;10',null,null union all
select 10211536,'5;6;8',null,'5;6;8','6;9;10',null,null

select dianhao,
case when len(a) - len(replace(a,';','')) is null then 0 else len(a) - len(replace(a,';','')) + 1 end A,
case when len(b) - len(replace(b,';','')) is null then 0 else len(b) - len(replace(b,';','')) + 1 end B,
case when len(c) - len(replace(c,';','')) is null then 0 else len(c) - len(replace(c,';','')) + 1 end C,
case when len(d) - len(replace(d,';','')) is null then 0 else len(d) - len(replace(d,';','')) + 1 end D,
case when len(e) - len(replace(e,';','')) is null then 0 else len(e) - len(replace(e,';','')) + 1 end E,
case when len(f) - len(replace(f,';','')) is null then 0 else len(f) - len(replace(f,';','')) + 1 end F
from tb

drop table tb

/*
dianhao A B C D E F
----------- ----------- ----------- ----------- ----------- ----------- -----------
10211034 0 0 0 0 4 0
10211529 2 1 2 2 0 0
10211536 3 0 3 3 0 0

(所影响的行数为 3 行)

*/
回复
fpzgm 2010-10-20

--> 测试数据:#tb
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go
create table #tb([dianhao] int,[A] varchar(5),[B] int,[C] varchar(5),[D] varchar(6),[E] varchar(7),[F] varchar(7))
insert #tb
select 10211034,null,null,null,null,'3;5;6;7',null union all
select 10211529,'2;10',10,'2;10','2;10',null,null union all
select 10211536,'5;6;8',null,'5;6;8','6;9;10',null,null
go

create function dbo.str(@str varchar(10))
returns int
as
begin
declare @result int
select @result=case when @str is null then 0
when @str is not null and charindex(';',@str)=0 then 1
when charindex(';',@str)>1 then len(replace(@str,';',';;'))-len(@str)+1
end
return @result
end



select dianhao,dbo.str(A) A,dbo.str(B) B,dbo.str(C) C
,dbo.str(D) D,dbo.str(E) E,dbo.str(F) F
from #tb

/*

dianhao A B C D E F
10211034 0 0 0 0 4 0
10211529 2 1 2 2 0 0
10211536 3 0 3 3 0 0
*/
回复
fpzgm 2010-10-20

--更正
--要分情况,可以在原来上面加case when,有的要加1
--或者建个函数
create function dbo.str(@str varchar)
return int
as
begin
declare @result int
select @result=case when @str is null then 0
when @str is not null and charindex(';',@str)=0 then 1
else len(replace(@str,';',';;'))-len(@str)+1
end
return @result
end

--调用函数
select dianhao,dbo.str(A),dbo.str(B),dbo.str(C),dbo.str(D),dbo.str(E),dbo.str(F)
from [table]


回复
闹铃 2010-10-20


select dianhao,
len(A)-len(replace(A,';','')),
len(B)-len(replace(B,';','')),
len(C)-len(replace(C,';','')),
len(D)-len(replace(D,';','')),
len(E)-len(replace(E,';','')),
len(F)-len(replace(F,';',''))
from [table]

回复
小_爱 2010-10-20
[Quote=引用 2 楼 beirut 的回复:]
SQL code
--> 测试数据:#tb
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go
create table #tb([dianhao] int,[A] varchar(5),[B] int,[C] varchar(5),[D] varchar(6),[E] varchar(7),[F] varchar(7)……
[/Quote]
都少了一个,哈哈 就这样了 自己改改吧
回复
小_爱 2010-10-20
--> 测试数据:#tb
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go
create table #tb([dianhao] int,[A] varchar(5),[B] int,[C] varchar(5),[D] varchar(6),[E] varchar(7),[F] varchar(7))
insert #tb
select 10211034,null,null,null,null,'3;5;6;7',null union all
select 10211529,'2;10',10,'2;10','2;10',null,null union all
select 10211536,'5;6;8',null,'5;6;8','6;9;10',null,null
go

-->测试开始
select [dianhao],len([A])-len(replace([A],';','')) as [A],
len([B])-len(replace([B],';','')) as [B],
len([C])-len(replace([C],';','')) as [C],
len([D])-len(replace([D],';','')) as [D],
len([E])-len(replace([E],';','')) as [E],
len([F])-len(replace([F],';','')) as [F]
from #tb
/*
dianhao A B C D E F
----------- ----------- ----------- ----------- ----------- ----------- -----------
10211034 NULL NULL NULL NULL 3 NULL
10211529 1 0 1 1 NULL NULL
10211536 2 NULL 2 2 NULL NULL

(3 行受影响)


*/
回复
fpzgm 2010-10-20


select dianhao,
len(replace(A,';',';;'))-len(A),
len(replace(B,';',';;'))-len(B),
len(replace(C,';',';;'))-len(C),
len(replace(D,';',';;'))-len(D),
len(replace(E,';',';;'))-len(E),
len(replace(F,';',';;'))-len(F)
from [table]
回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2010-10-20 01:41
社区公告
暂无公告