说明：有6个箱子以及6个球，分别从1到6编码，要将这6个球装入这6个箱子，要求每个箱子有且仅有一个球，并且每个球只能且必须属于一个箱子。而且每个球限制了能装入的箱号，比如示例中的数据表示1号球只能装入1、2、4号箱子里，其他的箱子不能装1号球。

箱子对应的表数据如下：

 附件: 您所在的用户组无法下载或查看附件

球对应的表数据如下：

 附件: 您所在的用户组无法下载或查看附件


球能装入的箱号对应的表如下：

 附件: 您所在的用户组无法下载或查看附件


要得到的结果如下：

 附件: 您所在的用户组无法下载或查看附件

要求：

1、使用基于集合的方式进行处理
2、不能使用表变量、临时表、while循环、自定义函数
3、可以使用CTE


利用下面的语句产生示例数据：

-- --------------------------------------------------
-- Create the 'Boxes' table
-- --------------------------------------------------
IF OBJECT_ID('TC22_Boxes','U') IS NOT NULL
BEGIN
    DROP TABLE TC22_Boxes
END
GO
 
CREATE TABLE TC22_Boxes
(
    BoxId INT IDENTITY PRIMARY KEY,
    BoxName VARCHAR(20)
)
GO
 
INSERT INTO TC22_Boxes (BoxName)
SELECT 'Box 1' UNION all
SELECT 'Box 2' UNION all
SELECT 'Box 3' UNION all
SELECT 'Box 4' UNION all
SELECT 'Box 5' UNION all
SELECT 'Box 6'
 
 
-- --------------------------------------------------
-- Create the 'Balls' table
-- --------------------------------------------------
IF OBJECT_ID('TC22_Balls','U') IS NOT NULL
BEGIN
    DROP TABLE TC22_Balls
END
GO
 
CREATE TABLE TC22_Balls(
    BallId INT IDENTITY PRIMARY KEY,
    BallName VARCHAR(20)
)
GO
 
INSERT INTO TC22_Balls (BallName)
SELECT 'Ball 1' UNION all
SELECT 'Ball 2' UNION all
SELECT 'Ball 3' UNION all
SELECT 'Ball 4' UNION all
SELECT 'Ball 5' UNION all
SELECT 'Ball 6'
 
-- --------------------------------------------------
-- Create the 'Preferences' table
-- --------------------------------------------------
IF OBJECT_ID('TC22_Preferences','U') IS NOT NULL
BEGIN
    DROP TABLE TC22_Preferences
END
GO
 
CREATE TABLE TC22_Preferences(
    PreferenceId INT IDENTITY PRIMARY KEY,
    BoxId INT,
    BallId INT
)
GO
 
INSERT INTO TC22_Preferences (BoxID, BallID) 
SELECT 1,1 UNION all
SELECT 2,1 UNION all
SELECT 2,3 UNION all
SELECT 3,2 UNION all
SELECT 3,3 UNION all
SELECT 4,1 UNION all
SELECT 4,2 UNION all
SELECT 4,3 UNION all
SELECT 4,4 UNION all
SELECT 4,5 UNION all
SELECT 4,6 UNION all
SELECT 5,4 UNION all
SELECT 5,5 UNION all
SELECT 6,5

with r as (
select top 1 a.BoxId,b.BallId,BoxIdFlag=cast(rtrim(b.BoxId) as varchar(10)),BallIdFlag=cast(rtrim(b.BallId) as varchar(10)),dep=0
from
(select top 1 BoxId from TC22_Preferences
group by BoxId
order by count(*),newid()) a inner join
TC22_Preferences b on a.BoxId=b.BoxId
order by newid()
union all
select b.BoxId,b.BallId,BoxIdFlag=cast(BoxIdFlag+rtrim(b.BoxId) as varchar(10)),BallIdFlag=cast(BallIdFlag+rtrim(b.BallId) as varchar(10)),dep=a.dep+1
from
r a inner join
TC22_Preferences b on charindex(rtrim(b.BoxId),a.BoxIdFlag)=0 and charindex(rtrim(b.BallId),a.BallIdFlag)=0
where a.dep<6
)
,r1 as (
select top 1 BoxIdFlag,BallIdFlag from r where dep=5
)
,r2 as (
select BoxIdFlag,BallIdFlag,BoxId=substring(BoxIdFlag,1,1),BallId=substring(BallIdFlag,1,1),dep=0
from r1 a
union all
select BoxIdFlag,BallIdFlag,BoxId=substring(BoxIdFlag,b.number+1,1),BallId=substring(BallIdFlag,b.number+1,1),dep=b.number
from r2 a inner join
master.dbo.spt_values b on a.dep+1=b.number and b.type='P' and b.number<6
)
select BoxId,BallId from r2 order by BoxId

最后的查询该这样
select b.BoxName,c.BallName from r2 a inner join
TC22_Boxes b on a.BoxId=b.BoxId inner join
TC22_Balls c on a.BallId=c.BallId
order by a.BoxId
是一个嵌套递归哦，本来是可以简化逻辑的，但cte的限制不得不选出更多的重复的记录，不知道有啥更好的方案没

说明：题目中提到的下面的3个要求为可选，但尽量满足：

1、使用基于集合的方式进行处理
2、不能使用表变量、临时表、while循环、自定义函数
3、可以使用CTE

另外：
1、球和箱子的数目不一定是6个，有可能更多，但球和箱子的数量是相同的。
2、球和箱子的编号可能不是连续的

本身我上面写的应该就可以了啊，弄个更通用的
with r as (
select b.BoxId,b.BallId,BoxIdFlag=cast(','+right(1000+b.BoxId,3) as varchar(200)),BallIdFlag=cast(','+right(1000+b.BallId,3) as varchar(200)),dep=1
from TC22_Preferences b
union all
select b.BoxId,b.BallId,BoxIdFlag=cast(BoxIdFlag+','+right(1000+b.BoxId,3) as varchar(200)),BallIdFlag=cast(BallIdFlag+','+right(1000+b.BallId,3) as varchar(200)),dep=a.dep+1
from
r a inner join
TC22_Preferences b on charindex(','+right(1000+b.BoxId,3),a.BoxIdFlag)=0 and charindex(','+right(1000+b.BallId,3),a.BallIdFlag)=0
)
,r1 as (
select top 1 BoxIdFlag,BallIdFlag from r where dep=(select count(*) from TC22_Boxes)
order by newid()
)
,rcount as (select _count=count(*) from TC22_Boxes)
,r2 as (
select BoxIdFlag,BallIdFlag,BoxId=substring(BoxIdFlag,2,3),BallId=substring(BallIdFlag,2,3),dep=0
from r1 a
union all
select BoxIdFlag,BallIdFlag,BoxId=substring(BoxIdFlag,b.number*4+2,3),BallId=substring(BallIdFlag,b.number*4+2,3),dep=b.number
from r2 a inner join
master.dbo.spt_values b on a.dep+1=b.number and b.type='P' and b.number<=(select _count from rcount)
)
select b.BoxName,c.BallName from r2 a inner join
TC22_Boxes b on a.BoxId=b.BoxId inner join
TC22_Balls c on a.BallId=c.BallId
order by 1

不使用临时表会付出更多的计算的代价，本身这个应该是从匹配最少的box开始，那样会最少的尝试代价

突然想到一点，可以在所有排序里指定顺序来避免过多的重复记录，在 r 里的递归语句中，关联条件加上这个就可以了
and b.BallId>right(a.BallIdFlag,3)