暂时写了这么一个函数!
其它还没想到更好的方法啊!
create table A
(
T_name varchar(20)
)insert A select 'assdfsa'
insert A select 'wesda'
insert A select 'werirs'create Function T_fun(@T varchar(100),@Ti varchar(1))
returns int
as
begin
declare @i int
declare @L int
set @L=0
set @i=1
while @i<=len(@T)
begin
if charindex(@Ti,SUBSTRING(@T,@i,1))>0
set @L=@L+1
set @i=@i+1
end
return @L
end
select dbo.T_fun(T_name,'a') from A
其它还没想到更好的方法啊!
create table A
(
T_name varchar(20)
)insert A select 'assdfsa'
insert A select 'wesda'
insert A select 'werirs'create Function T_fun(@T varchar(100),@Ti varchar(1))
returns int
as
begin
declare @i int
declare @L int
set @L=0
set @i=1
while @i<=len(@T)
begin
if charindex(@Ti,SUBSTRING(@T,@i,1))>0
set @L=@L+1
set @i=@i+1
end
return @L
end
select dbo.T_fun(T_name,'a') from A
create table A
(
T_name varchar(20)
)insert A select 'abced'
insert A select 'ilieb'
insert A select 'lkjiioe'
insert A select 'ia0lka'drop Function T_fun
create Function T_fun(@T varchar(100),@Ti varchar(1))
returns int
as
begin
declare @i int
declare @L int
set @L=0
set @i=1
while @i<=len(@T)
begin
if charindex(@Ti,SUBSTRING(@T,@i,1))>0
set @L=@L+1
set @i=@i+1
end
return @L
end
create proc T_pro
as
declare @T_SQL as varchar(8000)
declare @i int
declare @T table(ID int,T varchar(1))
set @T_SQL=''
set @i=65
while @i<=122
begin
insert @T select @i,CHAR(@i)
if @i=90
set @i=@i+7
else
set @i=@i+1
end
select @T_SQL=@T_SQL + 'select ''' + T + ''' as id1,sum(dbo.T_fun(T_name, ''' + T + ''' )) as id2 from A Union ' from @T
set @T_SQL=left(@T_SQL,len(@T_SQL)-len('Union '))
print @T_SQL
exec (@T_SQL)
exec T_pro
GO-- 测试数据
CREATE TABLE tb(col varchar(100))
INSERT tb SELECT 'abced'
UNION ALL SELECT 'ilieb'
UNION ALL SELECT 'lkjiioe'
UNION ALL SELECT 'ia0lka'
GO-- 统计: 字段信息中有 a的 数据是多少,有 b的数据是多少,有e的数据是多少 依次类推....
SELECT TOP 8000
id = identity(int,1,1)
INTO # FROM syscolumns a, syscolumns b
SELECT id1 = SUBSTRING(a.col, b.id, 1), id2 = COUNT(*)
FROM tb a, # b
WHERE LEN(a.col) >= b.id
GROUP BY SUBSTRING(a.col, b.id, 1)
ORDER BY SUBSTRING(a.col, b.id, 1)
DROP TABLE #
GO
/*--结果
id1 id2
---- -----------
0 1
a 3
b 2
c 1
d 1
e 3
i 5
j 1
k 2
l 3
o 1(所影响的行数为 11 行)
--*/
-- 如何取到字段中任何一个字符都不相同的数据
SELECT TOP 8000
id = identity(int,1,1)
INTO # FROM syscolumns a, syscolumns bSELECT * FROM tb
WHERE col NOT IN(
SELECT col
FROM(
SELECT a.col, id1 = SUBSTRING(a.col, b.id, 1)
FROM tb a, # b
WHERE LEN(a.col) >= b.id
)A GROUP BY col, id1
HAVING COUNT(*) > 1)
DROP TABLE #
GO
/*--结果
col
--------------------
abced(所影响的行数为 1 行)
--*/
-- 删除测试
DROP TABLE tb