表A有字段B,B的数据全部为数字和句号分隔例:
1.22.5.53.42.1.2.15.
3.6.7.5.21.15.33.4.
5.6.9.32.45.63.
....
....
....
上面是数据列名为1-99的数字,下面是每个数字总共出现过几次
1 2 3 4 5 6 7 8 9 10 11 ........
50 20 6 8 0 10 13 23 12 16 23 ........
请问有什么办法?
1.22.5.53.42.1.2.15.
3.6.7.5.21.15.33.4.
5.6.9.32.45.63.
....
....
....
上面是数据列名为1-99的数字,下面是每个数字总共出现过几次
1 2 3 4 5 6 7 8 9 10 11 ........
50 20 6 8 0 10 13 23 12 16 23 ........
请问有什么办法?
create table A(B VARCHAR(1000))
insert A values('1.22.5.53.42.1.2.15. ')
insert A values('3.6.7.5.21.15.33.4. ')
insert A values('5.6.9.32.45.63.')SELECT TOP 99 IDENTITY(INT,1,1) ID INTO #T FROM SYSCOLUMNSSELECT ID,SUM(CASE WHEN A.B IS NULL THEN 0 ELSE 1 END) Cou
FROM #T T LEFT JOIN A
ON CHARINDEX('.'+CAST(T.ID AS VARCHAR(3))+'.','.'+A.B+'.')>0
GROUP BY ID
create table tableA(b varchar(1000))
go
insert into tableA values ('1.22.5.53.42.1.2.15.')
insert into tableA values ('3.6.7.5.21.15.33.4.')
insert into tableA values ('5.6.9.32.45.63.')
go
declare @sql varchar(8000)
declare @str varchar(1000)
set @str = ''
declare @i int
set @i = 1
while @i <= 100
begin
set @str = @str + '[' + cast(@i as varchar(3)) + '],'
set @i = @i + 1
endset @str = left(@str,len(@str)-1)set @sql = '
select * from
(
SELECT B.value
FROM(
SELECT [value] = CONVERT(xml,'' <root> <v>'' + REPLACE(left(b,LEN(b)-1), ''.'', '' </v> <v>'') + '' </v> </root>'') FROM tableA
)A
OUTER APPLY(
SELECT value = N.v.value(''.'', ''int'') FROM A.[value].nodes(''/root/v'') N(v)
)B
)x
PIVOT
(
COUNT([value])
FOR [value] IN (' + @str + ')
)AS pvt'exec (@sql)go