对比表1名称 数据
1 ADXDXDXAZADXDXDXAZ
2 CCCCCCXAZADXDXDXAZ
3 ADXDAAAAAZAAXDXDXAZ
................用1条数据对比表1的数据,相同的记录为“对”,不同的记录为“错”比如用CCCCCSSAASSASASDXDXDXAZADXDXDXAZ这个条数据对比CCCCCSSAASSASASDXDXDXAZADXDXDXAZ
ADXDXDXAZADXDXDXAZ
对比结果
错错错错错错对错错CCCCCSSAASSASASDXDXDXAZADXDXDXAZ
CCCCCCXAZADXDXDXAZ
对比结果
对对对对对错错对得结果
表2名称 数据
1 错错错错错错对错错....
2 对对对对对错错对........
3 ...................
1 ADXDXDXAZADXDXDXAZ
2 CCCCCCXAZADXDXDXAZ
3 ADXDAAAAAZAAXDXDXAZ
................用1条数据对比表1的数据,相同的记录为“对”,不同的记录为“错”比如用CCCCCSSAASSASASDXDXDXAZADXDXDXAZ这个条数据对比CCCCCSSAASSASASDXDXDXAZADXDXDXAZ
ADXDXDXAZADXDXDXAZ
对比结果
错错错错错错对错错CCCCCSSAASSASASDXDXDXAZADXDXDXAZ
CCCCCCXAZADXDXDXAZ
对比结果
对对对对对错错对得结果
表2名称 数据
1 错错错错错错对错错....
2 对对对对对错错对........
3 ...................
insert into @table
select 1,'ADXDXDXAZADXDXDXAZ' union all
select 2,'CCCCCCXAZADXDXDXAZ' union all
select 3,'ADXDAAAAAZAAXDXDXAZ'
declare @m int,@x int,@y varchar(50),@w int,@z varchar(50),@zz varchar(50),@r varchar(100)
set @x=1
set @zz='CCCCCSSAASSASASDXDXDXAZADXDXDXAZ'
set @r=''
declare cur cursor for select a,b from @table
open cur
fetch next from cur into @m,@y
while @@fetch_status=0
begin
if(len(@zz)<len(@y))
set @w=len(@y)
else
set @w=len(@zz) while @x<@w
begin
if(substring(@zz,@x,1)=substring(@y,@x,1))
set @r=@r+'对'
else
set @r=@r+'错'
set @x=@x+1
end
print cast(@m as varchar)+','+ @r
set @x=1
set @r=''
fetch next from cur into @m,@y
end
close cur
deallocate cur
--result:
/*
1,错错错错错错错对错错错错错错错错错错错错错错错错错错错错错错错
2,对对对对对错错对错错错错错错错错错错错错错错错错错错错错错错错
3,错错错错错错错对对错错对错错错对对错错错错错错错错错错错错错错
*/
/*
1,错错错错错错错对错错错错错错错错错错错错错错错错错错错错错错错
2,对对对对对错错对错错错错错错错错错错错错错错错错错错错错错错错
3,错错错错错错错对对错错对错错错对对错错错错错错错错错错错错错错
*/是这样的,可是后面的都是错啊,表1里数据没了就停止了啊,进行下1条啊
应该得这个的结果啊1,错错错错错错错对错错错错错错错错错错错
2,对对对对对错错对错错错错错错错错错错错
3,错错错错错错错对对错错对错错错对对错错
declare @table table(a int,b varchar(50))
insert into @table
select 1,'ADXDXDXAZADXDXDXAZ' union all
select 2,'CCCCCCXAZADXDXDXAZ' union all
select 3,'ADXDAAAAAZAAXDXDXAZ'
declare @m int,@x int,@y varchar(50),@w int,@z varchar(50),@zz varchar(50),@r varchar(100)
set @x=1
set @zz='CCCCCSSAASSASASDXDXDXAZADXDXDXAZ'
set @r=''
declare cur cursor for select a,b from @table
open cur
fetch next from cur into @m,@y
while @@fetch_status=0
begin
set @w=len(@y) while @x<@w
begin
if(substring(@zz,@x,1)=substring(@y,@x,1))
set @r=@r+'对'
else
set @r=@r+'错'
set @x=@x+1
end
print cast(@m as varchar)+','+ @r
set @x=1
set @r=''
fetch next from cur into @m,@y
end
close cur
deallocate cur
/*
1,错错错错错错错对错错错错错错错错错
2,对对对对对错错对错错错错错错错错错
3,错错错错错错错对对错错对错错错对对错*/
insert into @table
select 1,'ADXDXDXAZADXDXDXAZ' union all
select 2,'CCCCCCXAZADXDXDXAZ' union all
select 3,'ADXDAAAAAZAAXDXDXAZ'
declare @m int,@x int,@y varchar(50),@w int,@z varchar(50),@zz varchar(50),@r varchar(100)
set @x=1
set @zz='CCCCCSSAASSASASDXDXDXAZADXDXDXAZ'
set @r=''
declare cur cursor for select a,b from @table
open cur
fetch next from cur into @m,@y
while @@fetch_status=0
begin
while @x<len(@y)
begin
if substring(@zz,@x,1)=substring(@y,@x,1)
set @r=@r+'对'
else
set @r=@r+'错'
set @x=@x+1
end
print cast(@m as varchar)+','+@r
set @x=1
set @r=''
fetch next from cur into @m,@y
end
close cur
deallocate cur
/*
1,错错错错错错错对错错错错错错错错错
2,对对对对对错错对错错错错错错错错错
3,错错错错错错错对对错错对错错错对对错
*/
-- Author: liangCK 小梁
-- Date : 2008-11-29 18:42:26
---------------------------------
--> 生成测试数据: @T
DECLARE @T TABLE (名称 INT,数据 VARCHAR(19))
INSERT INTO @T
SELECT 1,'ADXDXDXAZADXDXDXAZ' UNION ALL
SELECT 2,'CCCCCCXAZADXDXDXAZ' UNION ALL
SELECT 3,'ADXDAAAAAZAAXDXDXAZ'--SQL查询如下:DECLARE @data AS VARCHAR(50);
SET @data='CCCCCSSAASSASASDXDXDXAZADXDXDXAZ';WITH Liang AS
(
SELECT TOP(50)
ROW_NUMBER() OVER(ORDER BY o.object_id) AS ID
FROM sys.objects AS o
CROSS JOIN sys.columns AS c
),
Liang2 AS
(
SELECT
ID,
SUBSTRING(@data,ID,1) AS data
FROM Liang
WHERE SUBSTRING(@data,ID,1)<>''
)
SELECT
名称,
数据,
REPLACE(
(
SELECT
CASE WHEN B.ID IS NULL AND B.data IS NULL THEN '错' ELSE '对' END AS [data()]
FROM (
SELECT
ID,
SUBSTRING(T.数据,ID,1) AS data
FROM Liang
WHERE SUBSTRING(T.数据,ID,1)<>''
) AS A
LEFT JOIN Liang2 AS B
ON A.ID=B.ID
AND A.data=B.data
FOR XML PATH('')
),' ','') AS 对错
FROM @T AS T/*
名称 数据 对错
----------- ------------------- -----------------------------------
1 ADXDXDXAZADXDXDXAZ 错错错错错错错对错错错错错错错错错错
2 CCCCCCXAZADXDXDXAZ 对对对对对错错对错错错错错错错错错错
3 ADXDAAAAAZAAXDXDXAZ 错错错错错错错对对错错对错错错对对错错(3 行受影响)*/
-- Author: liangCK 小梁
-- Date : 2008-11-29 18:42:26
--------------------------------- --> 生成测试数据: @T
DECLARE @T TABLE (名称 INT,数据 VARCHAR(19))
INSERT INTO @T
SELECT 1,'ADXDXDXAZADXDXDXAZ' UNION ALL
SELECT 2,'CCCCCCXAZADXDXDXAZ' UNION ALL
SELECT 3,'ADXDAAAAAZAAXDXDXAZ' --SQL查询如下: DECLARE @data AS VARCHAR(50);
SET @data='CCCCCSSAASSASASDXDXDXAZADXDXDXAZ' ;WITH Liang AS
(
SELECT TOP(50)
ROW_NUMBER() OVER(ORDER BY o.object_id) AS ID
FROM sys.objects AS o
CROSS JOIN sys.columns AS c
),
Liang2 AS
(
SELECT
ID,
SUBSTRING(@data,ID,1) AS data
FROM Liang
WHERE SUBSTRING(@data,ID,1) <>''
)
SELECT
名称,
数据,
REPLACE(
(
SELECT
CASE WHEN B.ID IS NULL AND B.data IS NULL THEN '错' ELSE '对' END AS [data()]
FROM (
SELECT
ID,
SUBSTRING(T.数据,ID,1) AS data
FROM Liang
WHERE SUBSTRING(T.数据,ID,1) <>''
) AS A
LEFT JOIN Liang2 AS B
ON A.ID=B.ID
AND A.data=B.data
FOR XML PATH('')
),' ','') AS 对错
FROM @T AS T /*
名称 数据 对错
----------- ------------------- -----------------------------------
1 ADXDXDXAZADXDXDXAZ 错错错错错错错对错错错错错错错错错错
2 CCCCCCXAZADXDXDXAZ 对对对对对错错对错错错错错错错错错错
3 ADXDAAAAAZAAXDXDXAZ 错错错错错错错对对错错对错错错对对错错 (3 行受影响) */
-- Author: liangCK 小梁
-- Date : 2008-11-29 18:42:26
---------------------------------
--> 生成测试数据: @T
DECLARE @T TABLE (名称 INT,数据 VARCHAR(19))
INSERT INTO @T
SELECT 1,'ADXDXDXAZADXDXDXAZ' UNION ALL
SELECT 2,'CCCCCCXAZADXDXDXAZ' UNION ALL
SELECT 3,'ADXDAAAAAZAAXDXDXAZ'--SQL查询如下:DECLARE @data AS VARCHAR(50);
SET @data='CCCCCSSAASSASASDXDXDXAZADXDXDXAZ';WITH Liang AS
(
SELECT TOP(50)
ROW_NUMBER() OVER(ORDER BY o.object_id) AS ID
FROM sys.objects AS o
CROSS JOIN sys.columns AS c
),
Liang2 AS
(
SELECT
ID,
SUBSTRING(@data,ID,1) AS data
FROM Liang
WHERE SUBSTRING(@data,ID,1)<>''
)
SELECT
名称,
数据,
REPLACE(
(
SELECT
CASE WHEN B.data IS NULL THEN '错' ELSE '对' END AS [data()]
FROM (
SELECT
ID,
SUBSTRING(T.数据,ID,1) AS data
FROM Liang
WHERE SUBSTRING(T.数据,ID,1)<>''
) AS A
LEFT JOIN Liang2 AS B
ON A.ID=B.ID
AND A.data=B.data
FOR XML PATH('')
),' ','') AS 对错
FROM @T AS T/*
名称 数据 对错
----------- ------------------- ----------------------------------
1 ADXDXDXAZADXDXDXAZ 错错错错错错错对错错错错错错错错错错
2 CCCCCCXAZADXDXDXAZ 对对对对对错错对错错错错错错错错错错
3 ADXDAAAAAZAAXDXDXAZ 错错错错错错错对对错错对错错错对对错错(3 行受影响)*/
---------------------------------
-- Author: liangCK 小梁
-- Date : 2008-11-29 18:42:26
---------------------------------GO
--2000写个函数去处理CREATE FUNCTION dbo.f_RgihtWrong(@data VARCHAR(50),@S_Data VARCHAR(50))
RETURNS VARCHAR(50)
AS
BEGIN
DECLARE @Seq TABLE(ID INT IDENTITY,flag BIT)
INSERT @Seq(flag)
SELECT TOP 50 1
FROM master..sysobjects
CROSS JOIN master..syscolumns
DECLARE @result VARCHAR(50)
SET @result=''
SELECT @result=@result+
CASE WHEN B.data IS NULL THEN '错' ELSE '对' END
FROM (
SELECT
ID,
SUBSTRING(@data,ID,1) AS data
FROM @Seq AS T
WHERE SUBSTRING(@data,ID,1)<>''
) AS A
LEFT JOIN (
SELECT
ID,
SUBSTRING(@S_Data,ID,1) AS data
FROM @Seq AS T
WHERE SUBSTRING(@S_Data,ID,1)<>''
) AS B
ON A.ID=B.ID
AND A.data=B.data
RETURN(@result)
END
GO--> 生成测试数据: @T
DECLARE @T TABLE (名称 INT,数据 VARCHAR(19))
INSERT INTO @T
SELECT 1,'ADXDXDXAZADXDXDXAZ' UNION ALL
SELECT 2,'CCCCCCXAZADXDXDXAZ' UNION ALL
SELECT 3,'ADXDAAAAAZAAXDXDXAZ'--SQL查询如下:DECLARE @data AS VARCHAR(50);
SET @data='CCCCCSSAASSASASDXDXDXAZADXDXDXAZ'SELECT
名称,
数据,
dbo.f_RgihtWrong(数据,@data) AS 对错
FROM @TGO
DROP FUNCTION dbo.f_RgihtWrong/*
名称 数据 对错
----------- ------------------- --------------------------------------------------
1 ADXDXDXAZADXDXDXAZ 错错错错错错错对错错错错错错错错错错
2 CCCCCCXAZADXDXDXAZ 对对对对对错错对错错错错错错错错错错
3 ADXDAAAAAZAAXDXDXAZ 错错错错错错错对对错错对错错错对对错错(3 行受影响)*/