SQL获取字段由几个数组成

[kkkkshy]

现在有个int类型的字段 len 是int类型

      len
      4
      5
      12
      146
      574
      1980
      2413
      3342
      49824

现在需要获取int字段是由几个数字组成的。
需返回
4 返回 1
1980 返回 4

求解

[闹铃]

select len(len)

[百年树人]

select [len],len([len]) from tb

[快溜]

select len([len]) from tb

[liang145]

create table #tb([len] int)
insert #tb
select 4 union all
select 5 union all
select 12 union all
select 146 union all
select 574 union all
select 1980 union all
select 2413 union all
select 3342 union all
select 49824

select [len],len(ltrim([len])) as strLen from #tb

[百年树人]

返回不重复的数字个数

if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([len] int)
insert [tb]
select 4 union all
select 5 union all
select 12 union all
select 146 union all
select 574 union all
select 1980 union all
select 2413 union all
select 3342 union all
select 49824

select [len],count(distinct num) as cnt
from
(
 select [len],substring(ltrim([len]),b.number,1) as num
 from [tb] a 
 join master..spt_values b on b.type='P' 
 where b.number between 1 and len([len])
) t
group by [len]


--测试结果:
/*
len         cnt         
----------- ----------- 
4           1
5           1
12          2
146         3
574         3
1980        4
2413        4
3342        3
49824       4

（所影响的行数为 9 行）

*/

[cd731107]

select [len],len(ltrim([len])) from tb

[liang145]

哦~~~！！
学到东西了。。

[oO寒枫Oo]

楼主记得结贴

[attababy]

你需要的是交叉表查询

应该是：

SELECT
CASE len
WHEN 4 THEN 1
WHEN 1980 THEN 4
ELSE 0
END AS LEN
FROM ......