A Sql Stumper

无意中发现Joe Celko 在http://www.simple-talk.com/sql/t-sql-programming/celkos-sql-stumper-the-class-scheduling-problem/ post了一个问题。然后有在http://weblogs.sqlteam.com/peterl/archive/2010/01/23/Celko-Stumper---The-Class-Scheduling-Problem.aspx 发现一种解决方案。于是乎自己也弄了2个解决方案。

方案1：使用cursor，这种方式最简单，典型的过程式解决方案：

DECLARE @classstate TABLE(
	room_nbr CHAR(2) PRIMARY KEY,
	class_nbr CHAR(2)
)

DECLARE @roomnbr AS CHAR(2)
DECLARE @roomz AS INT

DECLARE room_cursor CURSOR FOR
	SELECT room_nbr, room_size
	FROM Rooms
	ORDER BY room_size DESC

OPEN room_cursor
FETCH NEXT FROM room_cursor INTO @roomnbr, @roomz

WHILE @@FETCH_STATUS = 0
BEGIN 
	DECLARE @classnbr AS CHAR(2)
	SET @classnbr = null
	SELECT TOP 1 @classnbr = class_nbr
	FROM Classes c
	WHERE @roomz > class_size AND c.class_nbr NOT IN(
		SELECT class_nbr
		FROM @classstate)

	IF @classnbr IS NOT NULL
		INSERT INTO @classstate VALUES(@roomnbr,
			@classnbr)
	
	FETCH NEXT FROM room_cursor INTO @roomnbr, @roomz	
END

CLOSE room_cursor
DEALLOCATE room_cursor

SELECT c.*, r.*
FROM Classes c 
JOIN @classstate m ON c.class_nbr = m.class_nbr
JOIN Rooms r ON m.room_nbr = r.room_nbr
ORDER BY c.class_nbr, r.room_nbr

方案2：大家看看，有问题指出来：

DECLARE @source TABLE(
	room_nbr CHAR(2),
	room_size int,
	class_nbr CHAR(2),
	class_size int,
	recID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED)
	
INSERT @source(room_nbr, room_size, class_nbr,class_size)
SELECT r.room_nbr, r.room_size, c.class_nbr, c.class_size
FROM dbo.Rooms r
INNER JOIN dbo.Classes c
ON c.class_size <= r.room_size
ORDER BY r.room_size DESC, c.class_size DESC

;with cte1(rn, rs, cn, cs) as(
	SELECT room_nbr, room_size, class_nbr, class_size
	FROM @source
	WHERE recID = 1
	
	UNION ALL
	
	SELECT D.room_nbr, D.room_size, D.class_nbr, D.class_size
	FROM
	(
		SELECT s2.room_nbr, s2.room_size, s2.class_nbr, s2.class_size
		FROM @source s2 JOIN cte1 c ON s2.room_size < c.rs AND
			s2.room_nbr <> c.rn AND s2.class_nbr <> c.cn
	) as D
)

SELECT d2.rn, d2.rs, d2.cn, d2.cs
FROM(
SELECT c.rn, c.rs, c.cn, c.cs, ROW_NUMBER() OVER(PARTITION BY c.rn ORDER BY c.cn) AS cc
FROM cte1  c JOIN(
	SELECT rn, MAX(cs) as m
	FROM cte1
	GROUP BY rn) d
	ON c.rn =d.rn and c.cs = d.m) d2
WHERE d2.cc = 1
ORDER BY rs DESC