數據庫環(huán)境：SQL SERVER 2008R2

有如下需求：
Baker, Cooper, Fletcher, Miller and Smith住在一座房子的不同樓層。
Baker 不住頂層。Cooper不住底層。
Fletcher 既不住頂層也不住底層。Miller住得比Cooper高。
Smith住的樓層和Fletcher不相鄰。
Fletcher住的樓層和Cooper不相鄰。
用SQL寫出來
 
解題思路:
先實現所有人住樓層的排列組合，然后把條件套進去即求得。如何實現排列組合，
 
1.基礎數據準備
--準備基礎數據，用A、B、C、D、E分別表示Baker, Cooper, Fletcher, Miller and Smith

CREATE TABLE ttb ( subname VARCHAR(1) , realname VARCHAR(10) )INSERT INTO ttbVALUES ( 'A', 'Baker' ), ( 'B', 'Cooper' ), ( 'C', 'Fletcher' ), ( 'D', 'Miller' ), ( 'E', 'Smith' )

2.生成所有可能情況的排列組合
--生成A、B、C、D、E所有的排列組合

WITH x0AS ( SELECT CONVERT(VARCHAR(10), 'A') AS hidUNION ALLSELECT CONVERT(VARCHAR(10), 'B') AS hidUNION ALLSELECT CONVERT(VARCHAR(10), 'C') AS hidUNION ALLSELECT CONVERT(VARCHAR(10), 'D') AS hidUNION ALLSELECT CONVERT(VARCHAR(10), 'E') AS hid), x1AS ( SELECT hidFROM x0WHERE LEN(hid) <= 5UNION ALLSELECT CONVERT(VARCHAR(10), a.hid + b.hid) AS hidFROM x0 aINNER JOIN x1 b ON CHARINDEX(a.hid, b.hid, 1) = 0) SELECT hid AS name INTO #tt FROM x1 WHERE LEN(hid) = 5 ORDER BY hid

3.加入條件，找出滿足要求的樓層安排

WITH x2AS ( SELECT nameFROM #ttWHERE SUBSTRING(name, 5, 1) <> 'A'--Baker 不住頂層AND SUBSTRING(name, 1, 1) <> 'B'--Cooper不住底層AND ( SUBSTRING(name, 1, 1) <> 'C'AND SUBSTRING(name, 5, 1) <> 'C'--Fletcher 既不住頂層也不住底層)AND name LIKE '%B%D%'--Miller住得比Cooper高AND name NOT LIKE '%CE%' AND name NOT LIKE '%EC%' --Smith住的樓層和Fletcher不相鄰AND name NOT LIKE '%BC%' AND name NOT LIKE '%CB%' --Fletcher住的樓層和Cooper不相鄰), x3--生成樓層號AS ( SELECT number AS id ,SUBSTRING(x2.name, number, 1) AS nameFROM master.dbo.spt_valuesINNER JOIN x2 ON 1 = 1WHERE type = 'P'AND number <= 5AND number >= 1) SELECT a.id AS 樓層,b.realname AS 姓名 FROM x3 aINNER JOIN ttb b ON b.subname = a.name ORDER BY id

樓層安排如下：

通過以上的代碼的介紹，希望對大家的學習有所幫助。