问题是执行了以下语句后,明明有02 03 04三个相同数,再把这个记录写到字段R3 里,可运行结果却记录为R0=11(一个也不同为11),
运行的结果应该是R3=11(有三个相同数的记录为11)请问如何修改?(说明:TB1和TB2比较,0个数相同记为R0,1个数相同记为R1,2个数相同记为R2,3个数相同记为R3......6个数相同记为R6)SELECT * FROM TB1IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[TB1]') AND type in (N'U'))
DROP TABLE [dbo].[TB1]
GO
CREATE TABLE TB1 (ID INT IDENTITY (1,1),NO1 VARCHAR(5), NO2 VARCHAR(5), NO3 VARCHAR(5), NO4 VARCHAR(5), NO5 VARCHAR(5), NO6 VARCHAR(5))
GO
INSERT INTO TB1
SELECT '02','03','04','22','23','31'
GO
BEGIN TRANSACTION
GO
ALTER TABLE dbo.TB1 ADD
R0 INT DEFAULT 0 NOT NULL,
R1 INT DEFAULT 0 NOT NULL,
R2 INT DEFAULT 0 NOT NULL,
R3 INT DEFAULT 0 NOT NULL,
R4 INT DEFAULT 0 NOT NULL,
R5 INT DEFAULT 0 NOT NULL,
R6 INT DEFAULT 0 NOT NULL
GO
COMMIT
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[TB2]') AND type in (N'U'))
DROP TABLE [dbo].[TB2]
GO
CREATE TABLE TB2 (ID INT IDENTITY (1,1),NO1 VARCHAR(5), NO2 VARCHAR(5), NO3 VARCHAR(5), NO4 VARCHAR(5), NO5 VARCHAR(5), NO6 VARCHAR(5))
GO
INSERT INTO TB2
SELECT '01','02','03','04','05','06'
UNION ALL SELECT '01','02','03','04','05','07'
UNION ALL SELECT '01','02','03','04','05','08'
UNION ALL SELECT '01','02','03','04','05','09'
UNION ALL SELECT '01','02','03','04','05','10'
UNION ALL SELECT '01','02','03','04','05','11'
UNION ALL SELECT '01','02','03','04','05','12'
UNION ALL SELECT '01','02','03','04','05','13'
UNION ALL SELECT '01','02','03','04','05','14'
UNION ALL SELECT '01','02','03','04','05','15'
UNION ALL SELECT '01','02','03','04','05','16'--SELECT * FROM TB1
--SELECT * FROM TB2DECLARE @COUNT INT;DECLARE @TB1_ID INT;
DECLARE @TB2_ID INT;
DECLARE @TB1_NO1 VARCHAR(5);
DECLARE @TB1_NO2 VARCHAR(5);
DECLARE @TB1_NO3 VARCHAR(5);
DECLARE @TB1_NO4 VARCHAR(5);
DECLARE @TB1_NO5 VARCHAR(5);
DECLARE @TB1_NO6 VARCHAR(5);DECLARE @TB2_NO1 VARCHAR(5);
DECLARE @TB2_NO2 VARCHAR(5);
DECLARE @TB2_NO3 VARCHAR(5);
DECLARE @TB2_NO4 VARCHAR(5);
DECLARE @TB2_NO5 VARCHAR(5);
DECLARE @TB2_NO6 VARCHAR(5);SET @TB1_ID = 1
WHILE(SELECT COUNT(1) FROM TB1 WHERE ID = @TB1_ID ) = 1
BEGIN
PRINT '@TB1_ID: '+CONVERT(VARCHAR,@TB1_ID)
SET @TB2_ID = 1
SELECT @TB1_NO1 = NO1,
@TB1_NO2 = NO2,
@TB1_NO3 = NO3,
@TB1_NO4 = NO4,
@TB1_NO5 = NO5,
@TB1_NO6 = NO6
FROM TB1 WHERE ID = @TB1_ID
WHILE(SELECT COUNT(1) FROM TB2 WHERE ID = @TB2_ID ) = 1
BEGIN
PRINT ' @TB2_ID: '+CONVERT(VARCHAR,@TB2_ID)
SET @COUNT = 0;
SELECT @TB2_NO1 = NO1,
@TB2_NO2 = NO2,
@TB2_NO3 = NO3,
@TB2_NO4 = NO4,
@TB2_NO5 = NO5,
@TB2_NO6 = NO6
FROM TB2 WHERE ID = @TB2_ID
SET @COUNT = 0
IF ( @TB1_NO1 = @TB2_NO1 ) SET @COUNT = @COUNT + 1;PRINT @COUNT
IF ( @TB1_NO2 = @TB2_NO2 ) SET @COUNT = @COUNT + 1;PRINT @COUNT
IF ( @TB1_NO3 = @TB2_NO3 ) SET @COUNT = @COUNT + 1;PRINT @COUNT
IF ( @TB1_NO4 = @TB2_NO4 ) SET @COUNT = @COUNT + 1;PRINT @COUNT
IF ( @TB1_NO5 = @TB2_NO5 ) SET @COUNT = @COUNT + 1;PRINT @COUNT
IF ( @TB1_NO6 = @TB2_NO6 ) SET @COUNT = @COUNT + 1;PRINT @COUNT
IF @COUNT = 0
UPDATE TB1 SET R0 = R0+1 WHERE ID = @TB1_ID
IF @COUNT = 1
UPDATE TB1 SET R1 = R1+1 WHERE ID = @TB1_ID
IF @COUNT = 2
UPDATE TB1 SET R2 = R2+1 WHERE ID = @TB1_ID
IF @COUNT = 3
UPDATE TB1 SET R3 = R3+1 WHERE ID = @TB1_ID
IF @COUNT = 4
UPDATE TB1 SET R4 = R4+1 WHERE ID = @TB1_ID
IF @COUNT = 5
UPDATE TB1 SET R5 = R5+1 WHERE ID = @TB1_ID
IF @COUNT = 6
UPDATE TB1 SET R6 = R6+1 WHERE ID = @TB1_ID SET @TB2_ID = @TB2_ID + 1
END
SET @TB1_ID = @TB1_ID + 1
END
--SELECT * FROM TB1
运行的结果应该是R3=11(有三个相同数的记录为11)请问如何修改?(说明:TB1和TB2比较,0个数相同记为R0,1个数相同记为R1,2个数相同记为R2,3个数相同记为R3......6个数相同记为R6)SELECT * FROM TB1IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[TB1]') AND type in (N'U'))
DROP TABLE [dbo].[TB1]
GO
CREATE TABLE TB1 (ID INT IDENTITY (1,1),NO1 VARCHAR(5), NO2 VARCHAR(5), NO3 VARCHAR(5), NO4 VARCHAR(5), NO5 VARCHAR(5), NO6 VARCHAR(5))
GO
INSERT INTO TB1
SELECT '02','03','04','22','23','31'
GO
BEGIN TRANSACTION
GO
ALTER TABLE dbo.TB1 ADD
R0 INT DEFAULT 0 NOT NULL,
R1 INT DEFAULT 0 NOT NULL,
R2 INT DEFAULT 0 NOT NULL,
R3 INT DEFAULT 0 NOT NULL,
R4 INT DEFAULT 0 NOT NULL,
R5 INT DEFAULT 0 NOT NULL,
R6 INT DEFAULT 0 NOT NULL
GO
COMMIT
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[TB2]') AND type in (N'U'))
DROP TABLE [dbo].[TB2]
GO
CREATE TABLE TB2 (ID INT IDENTITY (1,1),NO1 VARCHAR(5), NO2 VARCHAR(5), NO3 VARCHAR(5), NO4 VARCHAR(5), NO5 VARCHAR(5), NO6 VARCHAR(5))
GO
INSERT INTO TB2
SELECT '01','02','03','04','05','06'
UNION ALL SELECT '01','02','03','04','05','07'
UNION ALL SELECT '01','02','03','04','05','08'
UNION ALL SELECT '01','02','03','04','05','09'
UNION ALL SELECT '01','02','03','04','05','10'
UNION ALL SELECT '01','02','03','04','05','11'
UNION ALL SELECT '01','02','03','04','05','12'
UNION ALL SELECT '01','02','03','04','05','13'
UNION ALL SELECT '01','02','03','04','05','14'
UNION ALL SELECT '01','02','03','04','05','15'
UNION ALL SELECT '01','02','03','04','05','16'--SELECT * FROM TB1
--SELECT * FROM TB2DECLARE @COUNT INT;DECLARE @TB1_ID INT;
DECLARE @TB2_ID INT;
DECLARE @TB1_NO1 VARCHAR(5);
DECLARE @TB1_NO2 VARCHAR(5);
DECLARE @TB1_NO3 VARCHAR(5);
DECLARE @TB1_NO4 VARCHAR(5);
DECLARE @TB1_NO5 VARCHAR(5);
DECLARE @TB1_NO6 VARCHAR(5);DECLARE @TB2_NO1 VARCHAR(5);
DECLARE @TB2_NO2 VARCHAR(5);
DECLARE @TB2_NO3 VARCHAR(5);
DECLARE @TB2_NO4 VARCHAR(5);
DECLARE @TB2_NO5 VARCHAR(5);
DECLARE @TB2_NO6 VARCHAR(5);SET @TB1_ID = 1
WHILE(SELECT COUNT(1) FROM TB1 WHERE ID = @TB1_ID ) = 1
BEGIN
PRINT '@TB1_ID: '+CONVERT(VARCHAR,@TB1_ID)
SET @TB2_ID = 1
SELECT @TB1_NO1 = NO1,
@TB1_NO2 = NO2,
@TB1_NO3 = NO3,
@TB1_NO4 = NO4,
@TB1_NO5 = NO5,
@TB1_NO6 = NO6
FROM TB1 WHERE ID = @TB1_ID
WHILE(SELECT COUNT(1) FROM TB2 WHERE ID = @TB2_ID ) = 1
BEGIN
PRINT ' @TB2_ID: '+CONVERT(VARCHAR,@TB2_ID)
SET @COUNT = 0;
SELECT @TB2_NO1 = NO1,
@TB2_NO2 = NO2,
@TB2_NO3 = NO3,
@TB2_NO4 = NO4,
@TB2_NO5 = NO5,
@TB2_NO6 = NO6
FROM TB2 WHERE ID = @TB2_ID
SET @COUNT = 0
IF ( @TB1_NO1 = @TB2_NO1 ) SET @COUNT = @COUNT + 1;PRINT @COUNT
IF ( @TB1_NO2 = @TB2_NO2 ) SET @COUNT = @COUNT + 1;PRINT @COUNT
IF ( @TB1_NO3 = @TB2_NO3 ) SET @COUNT = @COUNT + 1;PRINT @COUNT
IF ( @TB1_NO4 = @TB2_NO4 ) SET @COUNT = @COUNT + 1;PRINT @COUNT
IF ( @TB1_NO5 = @TB2_NO5 ) SET @COUNT = @COUNT + 1;PRINT @COUNT
IF ( @TB1_NO6 = @TB2_NO6 ) SET @COUNT = @COUNT + 1;PRINT @COUNT
IF @COUNT = 0
UPDATE TB1 SET R0 = R0+1 WHERE ID = @TB1_ID
IF @COUNT = 1
UPDATE TB1 SET R1 = R1+1 WHERE ID = @TB1_ID
IF @COUNT = 2
UPDATE TB1 SET R2 = R2+1 WHERE ID = @TB1_ID
IF @COUNT = 3
UPDATE TB1 SET R3 = R3+1 WHERE ID = @TB1_ID
IF @COUNT = 4
UPDATE TB1 SET R4 = R4+1 WHERE ID = @TB1_ID
IF @COUNT = 5
UPDATE TB1 SET R5 = R5+1 WHERE ID = @TB1_ID
IF @COUNT = 6
UPDATE TB1 SET R6 = R6+1 WHERE ID = @TB1_ID SET @TB2_ID = @TB2_ID + 1
END
SET @TB1_ID = @TB1_ID + 1
END
--SELECT * FROM TB1
tb2 no1=01 no2=02 no3=03 no4=04所以SET @COUNT = 0
IF ( @TB1_NO1 = @TB2_NO1 ) SET @COUNT = @COUNT + 1;PRINT @COUNT
IF ( @TB1_NO2 = @TB2_NO2 ) SET @COUNT = @COUNT + 1;PRINT @COUNT
IF ( @TB1_NO3 = @TB2_NO3 ) SET @COUNT = @COUNT + 1;PRINT @COUNT
IF ( @TB1_NO4 = @TB2_NO4 ) SET @COUNT = @COUNT + 1;PRINT @COUNT
IF ( @TB1_NO5 = @TB2_NO5 ) SET @COUNT = @COUNT + 1;PRINT @COUNT
IF ( @TB1_NO6 = @TB2_NO6 ) SET @COUNT = @COUNT + 1;PRINT @COUNT 没有相同的
@COUNT = 0
INSERT INTO TB1
SELECT '02','03','04','22','23','31'
...
...
INSERT INTO TB2
SELECT '01','02','03','04','05','06'
UNION ALL SELECT '01','02','03','04','05','07'
UNION ALL SELECT '01','02','03','04','05','08'
UNION ALL SELECT '01','02','03','04','05','09'
UNION ALL SELECT '01','02','03','04','05','10'
UNION ALL SELECT '01','02','03','04','05','11'
UNION ALL SELECT '01','02','03','04','05','12'
UNION ALL SELECT '01','02','03','04','05','13'
UNION ALL SELECT '01','02','03','04','05','14'
UNION ALL SELECT '01','02','03','04','05','15'
UNION ALL SELECT '01','02','03','04','05','16'明明有3个数数相同的,如何用SQL语句完成正确的查询?
DECLARE @COUNT INT;DECLARE @TB1_ID INT;
DECLARE @TB2_ID INT;
DECLARE @TB1_NO1 VARCHAR(5);
DECLARE @TB1_NO2 VARCHAR(5);
DECLARE @TB1_NO3 VARCHAR(5);
DECLARE @TB1_NO4 VARCHAR(5);
DECLARE @TB1_NO5 VARCHAR(5);
DECLARE @TB1_NO6 VARCHAR(5);DECLARE @TB2_NO1 VARCHAR(5);
DECLARE @TB2_NO2 VARCHAR(5);
DECLARE @TB2_NO3 VARCHAR(5);
DECLARE @TB2_NO4 VARCHAR(5);
DECLARE @TB2_NO5 VARCHAR(5);
DECLARE @TB2_NO6 VARCHAR(5);SET @TB1_ID = 1
WHILE(SELECT COUNT(1) FROM TB1 WHERE ID = @TB1_ID ) = 1
BEGIN
PRINT '@TB1_ID: '+CONVERT(VARCHAR,@TB1_ID)
SET @TB2_ID = 1
SELECT @TB1_NO1 = NO1,
@TB1_NO2 = NO2,
@TB1_NO3 = NO3,
@TB1_NO4 = NO4,
@TB1_NO5 = NO5,
@TB1_NO6 = NO6
FROM TB1 WHERE ID = @TB1_ID
WHILE(SELECT COUNT(1) FROM TB2 WHERE ID = @TB2_ID ) = 1
BEGIN
PRINT ' @TB2_ID: '+CONVERT(VARCHAR,@TB2_ID)
SET @COUNT = 0;
SELECT @TB2_NO1 = NO1,
@TB2_NO2 = NO2,
@TB2_NO3 = NO3,
@TB2_NO4 = NO4,
@TB2_NO5 = NO5,
@TB2_NO6 = NO6
FROM TB2 WHERE ID = @TB2_ID
SET @COUNT = 0
IF ( @TB1_NO1 = @TB2_NO1
or @TB1_NO1 = @TB2_NO2
or @TB1_NO1 = @TB2_NO3
or @TB1_NO1 = @TB2_NO4
or @TB1_NO1 = @TB2_NO5
or @TB1_NO1 = @TB2_NO6
) SET @COUNT = @COUNT + 1;PRINT @COUNT
IF ( @TB1_NO2 = @TB2_NO1
or @TB1_NO2 = @TB2_NO2
or @TB1_NO2 = @TB2_NO3
or @TB1_NO2 = @TB2_NO4
or @TB1_NO2 = @TB2_NO5
or @TB1_NO2 = @TB2_NO6
) SET @COUNT = @COUNT + 1;PRINT @COUNT
IF ( @TB1_NO3 = @TB2_NO1
or @TB1_NO3 = @TB2_NO2
or @TB1_NO3 = @TB2_NO3
or @TB1_NO3 = @TB2_NO4
or @TB1_NO3 = @TB2_NO5
or @TB1_NO3 = @TB2_NO6
) SET @COUNT = @COUNT + 1;PRINT @COUNT
IF ( @TB1_NO4 = @TB2_NO1
or @TB1_NO4 = @TB2_NO2
or @TB1_NO4 = @TB2_NO3
or @TB1_NO4 = @TB2_NO4
or @TB1_NO4 = @TB2_NO5
or @TB1_NO4 = @TB2_NO6
) SET @COUNT = @COUNT + 1;PRINT @COUNT
IF ( @TB1_NO5 = @TB2_NO1
or @TB1_NO5 = @TB2_NO2
or @TB1_NO5 = @TB2_NO3
or @TB1_NO5 = @TB2_NO4
or @TB1_NO5 = @TB2_NO5
or @TB1_NO5 = @TB2_NO6
) SET @COUNT = @COUNT + 1;PRINT @COUNT
IF ( @TB1_NO6 = @TB2_NO1
or @TB1_NO6 = @TB2_NO2
or @TB1_NO6 = @TB2_NO3
or @TB1_NO6 = @TB2_NO4
or @TB1_NO6 = @TB2_NO5
or @TB1_NO6 = @TB2_NO6
) SET @COUNT = @COUNT + 1;PRINT @COUNT
IF @COUNT = 0
UPDATE TB1 SET R0 = R0+1 WHERE ID = @TB1_ID
IF @COUNT = 1
UPDATE TB1 SET R1 = R1+1 WHERE ID = @TB1_ID
IF @COUNT = 2
UPDATE TB1 SET R2 = R2+1 WHERE ID = @TB1_ID
IF @COUNT = 3
UPDATE TB1 SET R3 = R3+1 WHERE ID = @TB1_ID
IF @COUNT = 4
UPDATE TB1 SET R4 = R4+1 WHERE ID = @TB1_ID
IF @COUNT = 5
UPDATE TB1 SET R5 = R5+1 WHERE ID = @TB1_ID
IF @COUNT = 6
UPDATE TB1 SET R6 = R6+1 WHERE ID = @TB1_ID SET @TB2_ID = @TB2_ID + 1
END
SET @TB1_ID = @TB1_ID + 1
END
--SELECT * FROM TB1
ID NO1 NO2 NO3 NO4 NO5 NO6 R0 R1 R2 R3 R4 R5 R6
----------- ----- ----- ----- ----- ----- ----- ----------- ----------- ----------- ----------- ----------- ----------- -----------
1 02 03 04 22 23 31 0 0 0 11 0 0 0(1 行受影响)
INSERT INTO TB1
SELECT '02','20','21','22','23','31'
。
。
INSERT INTO TB2
SELECT [NO1]
,[NO2]
,[NO3]
,[NO4]
,[NO5]
,[NO6]
FROM [SSBallDB].[dbo].[1062T]TB2有1062条数据
执行语句后结果是ID NO1 NO2 NO3 NO4 NO5 NO6 R0 R1 R2 R3 R4 R5 R6
----------- ----- ----- ----- ----- ----- ----- ----------- -------
1 02 20 21 22 23 31 354 475 205 24 3 1 0正确结果应该是ID NO1 NO2 NO3 NO4 NO5 NO6 R0 R1 R2 R3 R4 R5 R6
----------- ----- ----- ----- ----- ----- ----- ----------- -------
1 02 20 21 22 23 31 277 479 250 24 50 0 1
到底要如何修改呢?
DROP TABLE [dbo].[TB1]
GO
CREATE TABLE TB1 (ID INT IDENTITY (1,1),NO1 VARCHAR(5), NO2 VARCHAR(5), NO3 VARCHAR(5), NO4 VARCHAR(5), NO5 VARCHAR(5), NO6 VARCHAR(5))
GO
INSERT INTO TB1
SELECT '02','03','04','22','23','31'
GO
BEGIN TRANSACTION
GO
ALTER TABLE dbo.TB1 ADD
R0 INT DEFAULT 0 NOT NULL,
R1 INT DEFAULT 0 NOT NULL,
R2 INT DEFAULT 0 NOT NULL,
R3 INT DEFAULT 0 NOT NULL,
R4 INT DEFAULT 0 NOT NULL,
R5 INT DEFAULT 0 NOT NULL,
R6 INT DEFAULT 0 NOT NULL
GO
COMMIT
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[TB2]') AND type in (N'U'))
DROP TABLE [dbo].[TB2]
GO
CREATE TABLE TB2 (ID INT IDENTITY (1,1),NO1 VARCHAR(5), NO2 VARCHAR(5), NO3 VARCHAR(5), NO4 VARCHAR(5), NO5 VARCHAR(5), NO6 VARCHAR(5))
GO
INSERT INTO TB2
SELECT '01','02','03','04','05','06'
UNION ALL SELECT '01','02','03','04','05','07'
UNION ALL SELECT '01','02','03','04','05','08'
UNION ALL SELECT '01','02','03','04','05','09'
UNION ALL SELECT '01','02','03','04','05','10'
UNION ALL SELECT '01','02','03','04','05','11'
UNION ALL SELECT '01','02','03','04','05','12'
UNION ALL SELECT '01','02','03','04','05','13'
UNION ALL SELECT '01','02','03','04','05','14'
UNION ALL SELECT '01','02','03','04','05','15'
UNION ALL SELECT '01','02','03','04','05','16'--SELECT * FROM TB1
--SELECT * FROM TB2DECLARE @COUNT INT;DECLARE @TB1_ID INT;
DECLARE @TB2_ID INT;
DECLARE @TB1_NO1 VARCHAR(5);
DECLARE @TB1_NO2 VARCHAR(5);
DECLARE @TB1_NO3 VARCHAR(5);
DECLARE @TB1_NO4 VARCHAR(5);
DECLARE @TB1_NO5 VARCHAR(5);
DECLARE @TB1_NO6 VARCHAR(5);DECLARE @TB2_NO1 VARCHAR(5);
DECLARE @TB2_NO2 VARCHAR(5);
DECLARE @TB2_NO3 VARCHAR(5);
DECLARE @TB2_NO4 VARCHAR(5);
DECLARE @TB2_NO5 VARCHAR(5);
DECLARE @TB2_NO6 VARCHAR(5);SET @TB1_ID = 1
WHILE(SELECT COUNT(1) FROM TB1 WHERE ID = @TB1_ID ) = 1
BEGIN
PRINT '@TB1_ID: '+CONVERT(VARCHAR,@TB1_ID)
SET @TB2_ID = 1
WHILE(SELECT COUNT(1) FROM TB2 WHERE ID = @TB2_ID ) = 1
BEGIN
PRINT ' @TB2_ID: '+CONVERT(VARCHAR,@TB2_ID)select
@COUNT = count(1)
from
(select id,no1 from tb1
union
select id,no2 from tb1
union
select id,no3 from tb1
union
select id,no4 from tb1
union
select id,no5 from tb1
union
select id,no6 from tb1 where id = @TB1_ID) a
full join
(select id,no1 from tb2
union
select id,no2 from tb2
union
select id,no3 from tb2
union
select id,no4 from tb2
union
select id,no5 from tb2
union
select id,no6 from tb2 where id = @TB2_ID) b
on a.id = b.id
where a.no1 = b.no1
IF @COUNT = 0
UPDATE TB1 SET R0 = R0+1 WHERE ID = @TB1_ID
IF @COUNT = 1
UPDATE TB1 SET R1 = R1+1 WHERE ID = @TB1_ID
IF @COUNT = 2
UPDATE TB1 SET R2 = R2+1 WHERE ID = @TB1_ID
IF @COUNT = 3
UPDATE TB1 SET R3 = R3+1 WHERE ID = @TB1_ID
IF @COUNT = 4
UPDATE TB1 SET R4 = R4+1 WHERE ID = @TB1_ID
IF @COUNT = 5
UPDATE TB1 SET R5 = R5+1 WHERE ID = @TB1_ID
IF @COUNT = 6
UPDATE TB1 SET R6 = R6+1 WHERE ID = @TB1_ID SET @TB2_ID = @TB2_ID + 1
END
SET @TB1_ID = @TB1_ID + 1
END
SELECT * FROM TB1/*
ID NO1 NO2 NO3 NO4 NO5 NO6 R0 R1 R2 R3 R4 R5 R6
----------- ----- ----- ----- ----- ----- ----- ----------- ----------- ----------- ----------- ----------- ----------- -----------
1 02 03 04 22 23 31 0 0 0 11 0 0 0(1 行受影响)
*/
INSERT intO TB1
SELECT '02','20','21','22','23','31'
....
..
INSERT INTO TB2
SELECT [NO1]
,[NO2]
,[NO3]
,[NO4]
,[NO5]
,[NO6]
FROM [SSBallDB].[dbo].[1062T]TB2有1062条数据
执行语句后结果是ID NO1 NO2 NO3 NO4 NO5 NO6 R0 R1 R2 R3 R4 R5 R6
----------- ----- ----- ----- ----- ----- ----- ----------- -------
1 02 20 21 22 23 31 1062 0 0 0 0 0 0正确结果应该是ID NO1 NO2 NO3 NO4 NO5 NO6 R0 R1 R2 R3 R4 R5 R6
----------- ----- ----- ----- ----- ----- ----- ----------- -------
1 02 20 21 22 23 31 277 479 250 24 50 0 1
因为
select
@COUNT = count(1)
from
(select id,no1 from tb1
union
select id,no2 from tb1
union
select id,no3 from tb1
union
select id,no4 from tb1
union
select id,no5 from tb1
union
select id,no6 from tb1 where id = @TB1_ID) a
full join
(select id,no1 from tb2
union
select id,no2 from tb2
union
select id,no3 from tb2
union
select id,no4 from tb2
union
select id,no5 from tb2
union
select id,no6 from tb2 where id = @TB2_ID) b
on a.id = b.id
where a.no1 = b.no1
所以 R0=1062到底要如何修改呢?
因为一贴放不下,所以回贴中的 tb2 数据省略了大部分。
结果略有差异,请楼主查一下。IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[TB1]') AND type in (N'U'))
DROP TABLE [dbo].[TB1]
GO
CREATE TABLE TB1 (ID INT IDENTITY (1,1),NO1 VARCHAR(5), NO2 VARCHAR(5), NO3 VARCHAR(5), NO4 VARCHAR(5), NO5 VARCHAR(5), NO6 VARCHAR(5))
GO
INSERT INTO TB1
SELECT '02','20','21','22','23','31'
GO
BEGIN TRANSACTION
GO
ALTER TABLE dbo.TB1 ADD
R0 INT DEFAULT 0 NOT NULL,
R1 INT DEFAULT 0 NOT NULL,
R2 INT DEFAULT 0 NOT NULL,
R3 INT DEFAULT 0 NOT NULL,
R4 INT DEFAULT 0 NOT NULL,
R5 INT DEFAULT 0 NOT NULL,
R6 INT DEFAULT 0 NOT NULL
GO
COMMIT
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[TB2]') AND type in (N'U'))
DROP TABLE [dbo].[TB2]
GO
CREATE TABLE TB2 (ID INT IDENTITY (1,1),NO1 VARCHAR(5), NO2 VARCHAR(5), NO3 VARCHAR(5), NO4 VARCHAR(5), NO5 VARCHAR(5), NO6 VARCHAR(5))
GO
INSERT INTO TB2
select '10','11','12','13','26','28'
union all select '04','09','19','20','21','26'
union all select '01','07','10','23','28','32'
union all select '04','06','07','10','13','25'
union all select '04','06','15','17','30','31'
union all select '01','03','10','21','26','27'
union all select '01','09','19','21','23','26'
union all select '05','08','09','14','17','23'
union all select '05','09','18','20','22','30'
union all select '01','02','08','13','17','24'
union all select '04','05','11','12','30','32'
union all select '02','12','16','17','27','30'
union all select '08','13','17','21','23','32'
union all select '03','05','07','08','21','31'
union all select '04','11','19','25','26','32'
union all select '11','17','28','30','31','33'
union all select '05','08','18','23','25','31'
union all select '05','16','19','20','25','28'
union all select '04','08','12','13','16','33'
union all select '07','10','25','26','27','32'
union all select '14','15','18','25','26','30'
union all select '02','07','11','12','14','32'
union all select '01','10','20','22','26','31'
union all select '02','07','15','17','22','30'
union all select '01','05','11','13','14','27'
union all select '08','13','15','26','29','31'
union all select '01','11','14','17','27','28'
union all select '06','13','16','20','28','32'
union all select '02','07','15','26','29','32'
union all select '02','06','13','14','23','27'
union all select '11','17','20','22','28','32'
union all select '04','11','25','27','29','30'
union all select '01','07','14','20','27','30'
union all select '08','13','14','16','23','25'
union all select '03','04','05','08','10','11'
union all select '07','21','24','25','27','28'
union all select '09','14','17','18','26','32'
union all select '05','07','08','24','25','27'
union all select '03','05','23','24','27','32'
union all select '04','05','06','12','14','23'
union all select '02','03','17','18','24','25'
union all select '03','05','07','10','15','20'
union all select '02','08','10','19','20','32'
union all select '02','05','09','16','32','33'
union all select '03','07','14','15','17','32'
union all select '07','08','10','11','17','26'
union all select '03','17','26','28','32','33'
union all select '10','12','20','28','30','31'
union all select '03','06','07','13','24','25'
union all select '02','08','17','23','24','26'
union all select '04','13','15','17','24','27'
union all select '01','12','13','23','30','31'
union all select '15','19','20','21','28','29'
union all select '02','05','09','21','31','33'
union all select '06','13','16','21','28','31'
union all select '08','17','21','26','28','29'
.
.
.
.union all select '08','10','13','14','16','23'
union all select '02','03','09','24','26','27'
union all select '01','08','13','14','27','31'
union all select '02','20','21','22','23','31'DECLARE @COUNT INT;DECLARE @TB1_ID INT;
DECLARE @TB2_ID INT;
DECLARE @TB1_NO1 VARCHAR(5);
DECLARE @TB1_NO2 VARCHAR(5);
DECLARE @TB1_NO3 VARCHAR(5);
DECLARE @TB1_NO4 VARCHAR(5);
DECLARE @TB1_NO5 VARCHAR(5);
DECLARE @TB1_NO6 VARCHAR(5);DECLARE @TB2_NO1 VARCHAR(5);
DECLARE @TB2_NO2 VARCHAR(5);
DECLARE @TB2_NO3 VARCHAR(5);
DECLARE @TB2_NO4 VARCHAR(5);
DECLARE @TB2_NO5 VARCHAR(5);
DECLARE @TB2_NO6 VARCHAR(5);SET @TB1_ID = 1
WHILE(SELECT COUNT(1) FROM TB1 WHERE ID = @TB1_ID ) = 1
BEGIN
-- PRINT '@TB1_ID: '+CONVERT(VARCHAR,@TB1_ID)
SET @TB2_ID = 1
WHILE(SELECT COUNT(1) FROM TB2 WHERE ID = @TB2_ID ) = 1
BEGIN
-- PRINT ' @TB2_ID: '+CONVERT(VARCHAR,@TB2_ID)select
@COUNT = count(1)
from
(select no1 from tb1 where id = @TB1_ID
union all
select no2 from tb1 where id = @TB1_ID
union all
select no3 from tb1 where id = @TB1_ID
union all
select no4 from tb1 where id = @TB1_ID
union all
select no5 from tb1 where id = @TB1_ID
union all
select no6 from tb1 where id = @TB1_ID) a
,
(select no1 from tb2 where id = @TB2_ID
union all
select no2 from tb2 where id = @TB2_ID
union all
select no3 from tb2 where id = @TB2_ID
union all
select no4 from tb2 where id = @TB2_ID
union all
select no5 from tb2 where id = @TB2_ID
union all
select no6 from tb2 where id = @TB2_ID) b
where a.no1 = b.no1print '-------------------'
print @TB1_ID
print @TB2_ID
print @COUNT
IF @COUNT = 0
UPDATE TB1 SET R0 = R0+1 WHERE ID = @TB1_ID
IF @COUNT = 1
UPDATE TB1 SET R1 = R1+1 WHERE ID = @TB1_ID
IF @COUNT = 2
UPDATE TB1 SET R2 = R2+1 WHERE ID = @TB1_ID
IF @COUNT = 3
UPDATE TB1 SET R3 = R3+1 WHERE ID = @TB1_ID
IF @COUNT = 4
UPDATE TB1 SET R4 = R4+1 WHERE ID = @TB1_ID
IF @COUNT = 5
UPDATE TB1 SET R5 = R5+1 WHERE ID = @TB1_ID
IF @COUNT = 6
UPDATE TB1 SET R6 = R6+1 WHERE ID = @TB1_ID SET @TB2_ID = @TB2_ID + 1
END
SET @TB1_ID = @TB1_ID + 1
END
SELECT * FROM TB1
/*
ID NO1 NO2 NO3 NO4 NO5 NO6 R0 R1 R2 R3 R4 R5 R6
----------- ----- ----- ----- ----- ----- ----- ----------- ----------- ----------- ----------- ----------- ----------- -----------
1 02 20 21 22 23 31 277 479 250 50 5 0 1(1 row(s) affected)
*/
/*
ID NO1 NO2 NO3 NO4 NO5 NO6 R0 R1 R2 R3 R4 R5 R6
----------- ----- ----- ----- ----- ----- ----- ----------- ----------- ----------- ----------- ----------- ----------- -----------
1 02 20 21 22 23 31 277 479 250 50 5 0 1
*/
你11楼的“正确答案” R0+R1+R2+R3+R4+R5+R6 = 277+479+250+24+50+0+1 =1081,与1062条数据不符啊。
而我的答案 R0+R1+R2+R3+R4+R5+R6 = 277+479+250+50+5+0+1 = 1062,好像合理一点吧。
19楼的(Dlsongzhi1984)的方案是可行的,发到我的邮箱的方案很细致,有详细的注释,谢谢你和大家的帮助,检验19楼数据验证正确,谢谢所有帮助我的大师们,我要结贴了