能用SQL语句完成吗？我都有点晕了

一、用SQL语句求得1-33自然数里选6所有组合数，并建立表[master].[dbo].[33选6所有组合数]（号码升序排列）,列分别为H1  H2 H3 H4 H5 H6；
二、把[master].[dbo].[33选6所有组合数]里的H1 H2 H3 H4 H5 H6六个列数据分别和SSBallDB].[dbo].[历史号码]里的6列NO1 NO2 NO3 NO4 NO5 NO6逐一比较，
如果H1  H2 H3 H4 H5 H6和NO1 NO2 NO3 NO4 NO5 NO6没有一个相同的,提取H1  H2 H3 H4 H5 H6中的没有一个重复数据出现的次数就记做R0,
如果H1  H2 H3 H4 H5 H6和NO1 NO2 NO3 NO4 NO5 NO6有1个相同的,提取H1  H2 H3 H4 H5 H6中的有1个重复数据出现的次数就记做R1,
如果H1  H2 H3 H4 H5 H6和NO1 NO2 NO3 NO4 NO5 NO6有2个相同的,提取H1  H2 H3 H4 H5 H6中的有2个重复数据出现的次数就记做R2,
如果H1  H2 H3 H4 H5 H6和NO1 NO2 NO3 NO4 NO5 NO6有3个相同的,提取H1  H2 H3 H4 H5 H6中的有3个重复数据出现的次数就记做R3,
如果H1  H2 H3 H4 H5 H6和NO1 NO2 NO3 NO4 NO5 NO6有4个相同的,提取H1  H2 H3 H4 H5 H6中的有4个重复数据出现的次数就记做R4,
如果H1  H2 H3 H4 H5 H6和NO1 NO2 NO3 NO4 NO5 NO6有5个相同的,提取H1  H2 H3 H4 H5 H6中的有5个重复数据出现的次数就记做R5,
如果H1  H2 H3 H4 H5 H6和NO1 NO2 NO3 NO4 NO5 NO6有6个相同的,提取H1  H2 H3 H4 H5 H6中的有6个重复数据出现的次数就记做R6,例如：
SSBallDB].[dbo].[历史号码]数据表里有1058条记录，选定表[master].[dbo].[33选6所有组合数]里的第一条数据 '01 02 03 04 05 06'和SSBallDB].[dbo].[历史号码]数据表里1058条记录一一比较，
如果01 02 03 04 05 06和NO1 NO2 NO3 NO4 NO5 NO6没有一个相同的,提取01 02 03 04 05 06中的没有一个重复数据出现的次数851就记做R0,
如果01 02 03 04 05 06和NO1 NO2 NO3 NO4 NO5 NO6有1个相同的,提取01 02 03 04 05 06中的有1个重复数据出现的次数182就记做R1,
如果01 02 03 04 05 06和NO1 NO2 NO3 NO4 NO5 NO6有2个相同的,提取01 02 03 04 05 06中的有2个重复数据出现的次数20就记做R2,
如果01 02 03 04 05 06和NO1 NO2 NO3 NO4 NO5 NO6有3个相同的,提取01 02 03 04 05 06中的有3个重复数据出现的次数5就记做R3,
如果01 02 03 04 05 06和NO1 NO2 NO3 NO4 NO5 NO6有4个相同的,提取01 02 03 04 05 06中的有4个重复数据出现的次数0就记做R4,
如果01 02 03 04 05 06和NO1 NO2 NO3 NO4 NO5 NO6有5个相同的,提取01 02 03 04 05 06中的有5个重复数据出现的次数0就记做R5,
如果01 02 03 04 05 06和NO1 NO2 NO3 NO4 NO5 NO6有6个相同的,提取01 02 03 04 05 06中的有6个重复数据出现的次数0就记做R6,不难发现R0+R1+R2+R3+R4+R5+R6=851+182+20+5+0+0+0=1058（因为是与1058条记录一一比较）再举一个例子：
SSBallDB].[dbo].[历史号码]数据表里有1058条记录，选定表[master].[dbo].[33选6所有组合数]里的第897064条数据 '08 11 12 14 18 22'和SSBallDB].[dbo].[历史号码]数据表里1058条记录一一比较，
如果08 11 12 14 18 22和NO1 NO2 NO3 NO4 NO5 NO6没有一个相同的,提取08 11 12 14 18 22中的没有一个重复数据出现的次数828就记做R0,
如果08 11 12 14 18 22和NO1 NO2 NO3 NO4 NO5 NO6有1个相同的,提取08 11 12 14 18 22中的有1个重复数据出现的次数189就记做R1,
如果08 11 12 14 18 22和NO1 NO2 NO3 NO4 NO5 NO6有2个相同的,提取08 11 12 14 18 22中的有2个重复数据出现的次数32就记做R2,
如果08 11 12 14 18 22和NO1 NO2 NO3 NO4 NO5 NO6有3个相同的,提取08 11 12 14 18 22中的有3个重复数据出现的次数8就记做R3,
如果08 11 12 14 18 22和NO1 NO2 NO3 NO4 NO5 NO6有4个相同的,提取08 11 12 14 18 22中的有4个重复数据出现的次数0就记做R4,
如果08 11 12 14 18 22和NO1 NO2 NO3 NO4 NO5 NO6有5个相同的,提取08 11 12 14 18 22中的有5个重复数据出现的次数0就记做R5,
如果08 11 12 14 18 22和NO1 NO2 NO3 NO4 NO5 NO6有6个相同的,提取08 11 12 14 18 22中的有6个重复数据出现的次数1就记做R6,同样发现R0+R1+R2+R3+R4+R5+R6=828+189+32+8+0+0+1=1058（因为是与1058条记录一一比较）以此类推，把选定表[master].[dbo].[33选6所有组合数]里的所有记录和SSBallDB].[dbo].[历史号码]数据表里所有记录一一比较，
求得R0  R1  R2  R3  R4  R5  R6的记录写到表[master].[dbo].[33选6所有组合数]里（表[master].[dbo].[33选6所有组合数]里创建增加的列R0  R1  R2  R3  R4  R5  R6）
这样的功能用SQL语句怎么实现呢？

解决方案 »

免费领取超大流量手机卡，每月29元包185G流量+100分钟通话, 中国电信官方发货

SELECT TOP 33
[ID] = Identity(INT, 1, 1)
INTO
#tempTBForRand
FROM
SYSCOLUMNS A,SYSCOLUMNS B
CREATE TABLE #tempOrderTable
([ID] INT IDENTITY(1,1),
[H] INT
)
INSERT INTO #tempOrderTable ([H])
SELECT TOP 6  [ID] FROM #tempTBForRand ORDER BY NEWID()INSERT INTO #tempOrderTable ([H])
SELECT  [H]  FROM #tempOrderTable ORDER BY [H]
DELETE FROM #tempOrderTable WHERE [ID]<=6
IF not EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID(N'[master].[dbo].[33选6所有组合数]') AND OBJECTPROPERTY(ID, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [master].[dbo].[33选6所有组合数]
([H1] INT,[H2] INT,[H3] INT,[H4] INT,[H5] INT,[H6] INT)
ENDDECLARE  @sql VARCHAR(4000)
SET @sql = ''
SELECT @sql = @sql + ', SUM(CASE [H] WHEN ' +CONVERT(VARCHAR(2),[H])+ ' THEN [H] END) AS '''+CONVERT(VARCHAR(2),[H])+''''
FROM
(SELECT DISTINCT [H] from #tempOrderTable) AS A
SET @sql = 'INSERT INTO [master].[dbo].[33选6所有组合数] (H1,H2,H3,H4,H5,H6)
SELECT '+RIGHT(@sql,LEN(@sql)-1)+ ' FROM #tempOrderTable' EXEC (@sql)SELECT * FROM [master].[dbo].[33选6所有组合数]DROP TABLE #tempOrderTable
DROP TABLE #tempTBForRand
先提供上半部分
下班了，噢噢噢噢噢噢
明天再给你写啊
--(1-33)
SELECT TOP 33
[ID] = Identity(INT, 1, 1)
INTO
#tempTBForRand
FROM
SYSCOLUMNS A,SYSCOLUMNS B
CREATE TABLE #tempOrderTable
(
[ID] INT IDENTITY(1,1),
[H] INT
)
--6 rand ()
INSERT INTO
#tempOrderTable ([H])
SELECT
TOP 6 [ID]
FROM
#tempTBForRand
ORDER BY
NEWID()
--order by
INSERT INTO
#tempOrderTable ([H])
SELECT
[H]
FROM
#tempOrderTable
ORDER BY
[H]
--not order data delete
DELETE FROM #tempOrderTable WHERE [ID]<=6
--create table
IF NOT EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID(N'[master].[dbo].[33选6所有组合数]') AND OBJECTPROPERTY(ID, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [master].[dbo].[33选6所有组合数]
([H1] INT,[H2] INT,[H3] INT,[H4] INT,[H5] INT,[H6] INT)
END
--save temp data ,equal [master].[dbo].[33选6所有组合数]
CREATE TABLE #33To6
([H1] INT,[H2] INT,[H3] INT,[H4] INT,[H5] INT,[H6] INT)
--row to column
DECLARE @sql VARCHAR(4000)
SET @sql = ''
SELECT @sql = @sql + ', SUM(CASE [H] WHEN ' +CONVERT(VARCHAR(2),[H])+ ' THEN [H] END) AS '''+CONVERT(VARCHAR(2),[H])+''''
FROM
(SELECT DISTINCT [H] from #tempOrderTable) AS A
SET @sql = '--INSERT INTO [master].[dbo].[33选6所有组合数] (H1,H2,H3,H4,H5,H6)
INSERT INTO #33To6 (H1,H2,H3,H4,H5,H6)
SELECT '+RIGHT(@sql,LEN(@sql)-1)+ ' FROM #tempOrderTable' EXEC (@sql)
--save data to 33选6所有组合数
INSERT INTO [master].[dbo].[33选6所有组合数](H1,H2,H3,H4,H5,H6)
SELECT
H1,H2,H3,H4,H5,H6
FROM #33To6--equal data count
DECLARE @TempCnt TABLE([CNT] INT)
INSERT INTO @TempCnt
SELECT
CASE WHEN B.[R] LIKE '%,'+CONVERT(VARCHAR(2),[H1])+',%' THEN 1 ELSE 0 END+
CASE WHEN B.[R] LIKE '%,'+CONVERT(VARCHAR(2),[H2])+',%' THEN 1 ELSE 0 END+
CASE WHEN B.[R] LIKE '%,'+CONVERT(VARCHAR(2),[H3])+',%' THEN 1 ELSE 0 END+
CASE WHEN B.[R] LIKE '%,'+CONVERT(VARCHAR(2),[H4])+',%' THEN 1 ELSE 0 END+
CASE WHEN B.[R] LIKE '%,'+CONVERT(VARCHAR(2),[H5])+',%' THEN 1 ELSE 0 END+
CASE WHEN B.[R] LIKE '%,'+CONVERT(VARCHAR(2),[H6])+',%' THEN 1 ELSE 0 END CNT
FROM
#33To6  A
RIGHT JOIN
(SELECT
','+CONVERT(VARCHAR(2),NO1)+',' +CONVERT(VARCHAR(2),NO2)+',' +
CONVERT(VARCHAR(2),NO3)+',' +CONVERT(VARCHAR(2),NO4)+',' +
CONVERT(VARCHAR(2),NO5)+',' +CONVERT(VARCHAR(2),NO6)+',' [R]
FROM
[SSBallDB].[dbo].[历史号码]
) B
ON
1=1
--collection all equal data count
DECLARE @EndRs TABLE([R0] INT,[R1] INT,[R2] INT,[R3] INT,[R4] INT,[R5] INT,[R6] INT)
INSERT INTO @EndRs
SELECT
CASE CNT WHEN 0 THEN COUNT(CNT) ELSE 0 END [R0] ,
CASE CNT WHEN 1 THEN COUNT(CNT) ELSE 0 END [R1] ,
CASE CNT WHEN 2 THEN COUNT(CNT) ELSE 0 END [R2] ,
CASE CNT WHEN 3 THEN COUNT(CNT) ELSE 0 END [R3] ,
CASE CNT WHEN 4 THEN COUNT(CNT) ELSE 0 END [R4] ,
CASE CNT WHEN 5 THEN COUNT(CNT) ELSE 0 END [R5] ,
CASE CNT WHEN 6 THEN COUNT(CNT) ELSE 0 END [R6]
FROM
@TempCnt
GROUP BY
CNT--alter table
IF   NOT EXISTS(SELECT   1   FROM SYSCOLUMNS
                    WHERE   [ID]   =   object_id('[master].[dbo].[33选6所有组合数]')   and   [NAME]   =   'R0')
BEGIN
ALTER TABLE [master].[dbo].[33选6所有组合数]
ADD  [R0] INT
ALTER TABLE [master].[dbo].[33选6所有组合数]
ADD  [R1] INT
ALTER TABLE [master].[dbo].[33选6所有组合数]
ADD  [R2] INT
ALTER TABLE [master].[dbo].[33选6所有组合数]
ADD  [R3] INT
ALTER TABLE [master].[dbo].[33选6所有组合数]
ADD  [R4] INT
ALTER TABLE [master].[dbo].[33选6所有组合数]
ADD  [R5]INT
ALTER TABLE [master].[dbo].[33选6所有组合数]
ADD  [R6] INT

ENDUPDATE  [master].[dbo].[33选6所有组合数]
SET
[R0]=B.[R0],
[R1]=B.[R1],
[R2]=B.[R2],
[R3]=B.[R3],
[R4]=B.[R4],
[R5]=B.[R5],
[R6]=B.[R6]
FROM  [master].[dbo].[33选6所有组合数]  A
INNER JOIN (
SELECT
#33To6.H1,
#33To6.H2,
#33To6.H3,
#33To6.H4,
#33To6.H5,
#33To6.H6,
SUM(A.[R0]) R0,
SUM(A.[R1]) R1,
SUM(A.[R2]) R2,
SUM(A.[R3]) R3,
SUM(A.[R4]) R4,
SUM(A.[R5]) R5,
SUM(A.[R6])  R6
FROM #33To6,@EndRs  A
GROUP BY #33To6.H1, #33To6.H2, #33To6.H3, #33To6.H4, #33To6.H5, #33To6.H6
)B
ON
A.H1=B.H1
AND
A.H2=B.H2
AND
A.H3=B.H3
AND
A.H4=B.H4
AND
A.H5=B.H5
AND
A.H6=B.H6DROP TABLE #33To6
DROP TABLE #tempOrderTable
DROP TABLE #tempTBForRand
搂主自己整理一下吧
6楼kaixinyuanhang辛苦了，谢谢你的帮助，祝你开开心心每一天！
--创建表一：
IF OBJECT_ID('表一')>0
DROP TABLE 表一
GO
;WITH t AS
(
SELECT TOP 33 rn=ROW_NUMBER()OVER(ORDER BY @@spid)
FROM sysobjects
)
SELECT TOP 100 a.rn N01,b.rn N02,c.rn N03,d.rn N04,e.rn N05,f.rn N06,
R0=0,R1=0,R2=0,R3=0,R4=0,R5=0,R6=0
INTO 表一
FROM t a
JOIN t b ON a.rn<b.rn
JOIN t c ON b.rn<c.rn
JOIN t d ON c.rn<d.rn
JOIN t e ON d.rn<e.rn
JOIN t f ON e.rn<f.rn
ORDER BY 1,2,3,4,5,6
--创建表二：
IF OBJECT_ID('[表二]') IS NOT NULL
DROP TABLE [表二]
GO
CREATE TABLE [表二] ([N01] [nvarchar](10),[N02] [nvarchar](10),[N03] [int],[N04] [int],[N05] [int],[N06] [int])
INSERT INTO [表二]
SELECT '03','07','13','23','27','30' UNION ALL
SELECT '07','13','17','26','32','33' UNION ALL
SELECT '10','11','13','16','19','30' UNION ALL
SELECT '10','19','20','21','23','32' UNION ALL
SELECT '02','05','11','26','30','32' UNION ALL
SELECT '01','02','14','23','28','29' UNION ALL
SELECT '08','12','20','22','30','33' UNION ALL
SELECT '02','15','19','24','31','32'--SELECT * FROM [表二]---->SQL查询如下:--第三：创建一个表值函数
IF OBJECT_ID('f_table')>0
DROP FUNCTION f_table
GO
CREATE FUNCTION f_table(@1 INT,@2 INT,@3 INT,@4 INT,@5 INT,@6 INT,@type INT)
RETURNS @r TABLE(n VARCHAR(1000))
AS
BEGIN
DECLARE @t TABLE(n INT)
INSERT @t
SELECT @1 UNION ALL
SELECT @2 UNION ALL
SELECT @3 UNION ALL
SELECT @4 UNION ALL
SELECT @5 UNION ALL
SELECT @6 IF @type = 1
INSERT @r
SELECT '%,'+LTRIM(n)+',%' FROM @t IF @type = 2
INSERT @r
SELECT '%,'+LTRIM(a.n)+',%,'+LTRIM(b.n)+',%'
FROM @t a,@t b
WHERE a.n<b.n IF @type=3
INSERT @r
SELECT '%,'+LTRIM(a.n)+',%,'+LTRIM(b.n)+',%,'+LTRIM(c.n)+',%'
FROM @t a,@t b,@t c
WHERE a.n<b.n AND b.n<c.n IF @type=4
INSERT @r
SELECT '%,'+LTRIM(a.n)+',%,'+LTRIM(b.n)+',%,'+LTRIM(c.n)+',%,'+LTRIM(d.n)+',%'
FROM @t a,@t b,@t c,@t d
WHERE a.n<b.n AND b.n<c.n AND c.n<d.n IF @type=5
INSERT @r
SELECT '%,'+LTRIM(a.n)+',%,'+LTRIM(b.n)+',%,'+LTRIM(c.n)+',%,'+LTRIM(d.n)+',%,'+LTRIM(e.n)+',%'
FROM @t a,@t b,@t c,@t d,@t e
WHERE a.n<b.n AND b.n<c.n AND c.n<d.n AND d.n<e.n
IF @type=6
INSERT @r
SELECT '%,'+LTRIM(@1)+',%,'+LTRIM(@2)+',%,'+LTRIM(@3)+',%,'+LTRIM(@4)+',%,'+LTRIM(@5)+',%,'+LTRIM(@6)+',%'
RETURN
END
GO--SELECT * FROM f_table(1,2,3,4,5,6,1)
--第四：游标更新表一数据：
SET NOCOUNT ON
DECLARE @1 INT,@2 INT,@3 INT,@4 INT,@5 INT,@6 INT
DECLARE c CURSOR FOR
SELECT N01,N02,N03,N04,N05,N06
FROM 表一
FOR UPDATE OF R0,R1,R2,R3,R4,R5,R6
OPEN c
FETCH c INTO @1,@2,@3,@4,@5,@6
WHILE @@FETCH_STATUS=0
BEGIN
UPDATE 表一
SET    R1 = (
           SELECT COUNT(1)
           FROM 表二
           WHERE EXISTS(
                     SELECT 1
                     FROM dbo.f_table(@1, @2, @3, @4, @5, @6,1)
                     WHERE PATINDEX(
                               n, ','+LTRIM(N01)+',,'+LTRIM(N02)+',,'+LTRIM(N03)+',,'+
                               LTRIM(N04)+',,'+LTRIM(N05)+',,'+LTRIM(N06)+','
                           )>0
                 )
)
       ,R2 = (
           SELECT COUNT(1)
           FROM 表二
           WHERE EXISTS(
                     SELECT 1
                     FROM dbo.f_table(@1, @2, @3, @4, @5, @6,2)
                     WHERE PATINDEX(
                               n, ','+LTRIM(N01)+',,'+LTRIM(N02)+',,'+LTRIM(N03)+',,'+
                               LTRIM(N04)+',,'+LTRIM(N05)+',,'+LTRIM(N06)+','
                           )>0
                 )
       )
       ,R3 = (
           SELECT COUNT(1)
           FROM 表二
           WHERE EXISTS(
                     SELECT 1
                     FROM dbo.f_table(@1, @2, @3, @4, @5, @6,3)
                     WHERE PATINDEX(
                               n, ','+LTRIM(N01)+',,'+LTRIM(N02)+',,'+LTRIM(N03)+',,'+
                               LTRIM(N04)+',,'+LTRIM(N05)+',,'+LTRIM(N06)+','
                           )>0
                 )
       )
       ,R4 = (
           SELECT COUNT(1)
           FROM 表二
           WHERE EXISTS(
                     SELECT 1
                     FROM dbo.f_table(@1, @2, @3, @4, @5, @6,4)
                     WHERE PATINDEX(
                               n, ','+LTRIM(N01)+',,'+LTRIM(N02)+',,'+LTRIM(N03)+',,'+
                               LTRIM(N04)+',,'+LTRIM(N05)+',,'+LTRIM(N06)+','
                           )>0
                 )
       )
       ,R5 = (
           SELECT COUNT(1)
           FROM 表二
           WHERE EXISTS(
                     SELECT 1
                     FROM dbo.f_table(@1, @2, @3, @4, @5, @6,5)
                     WHERE PATINDEX(
                               n, ','+LTRIM(N01)+',,'+LTRIM(N02)+',,'+LTRIM(N03)+',,'+
                               LTRIM(N04)+',,'+LTRIM(N05)+',,'+LTRIM(N06)+','
                           )>0
                 )
       )
       ,R6 = (
           SELECT COUNT(1)
           FROM 表二
           WHERE EXISTS(
                     SELECT 1
                     FROM dbo.f_table(@1, @2, @3, @4, @5, @6,6)
                     WHERE PATINDEX(
                               n, ','+LTRIM(N01)+',,'+LTRIM(N02)+',,'+LTRIM(N03)+',,'+
                               LTRIM(N04)+',,'+LTRIM(N05)+',,'+LTRIM(N06)+','
                           )>0
                 )
       )
       ,R0 = (
           SELECT COUNT(1)
           FROM 表二
           WHERE NOT EXISTS(
                     SELECT 1
                     FROM dbo.f_table(@1, @2, @3, @4, @5, @6,1)
                     WHERE PATINDEX(
                               n, ','+LTRIM(N01)+',,'+LTRIM(N02)+',,'+LTRIM(N03)+',,'+
                               LTRIM(N04)+',,'+LTRIM(N05)+',,'+LTRIM(N06)+','
                           )>0
                 )
       )
    WHERE CURRENT OF c
    FETCH c INTO @1,@2,@3,@4,@5,@6
END
CLOSE c
DEALLOCATE c
SET NOCOUNT OFF
SELECT * FROM 表一