数据库环境：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 x0 AS ( 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), x1 AS ( SELECT hidFROM x0WHERE LEN(hid) = 5UNION ALLSELECT CONVERT(VARCHAR(10), a.hid + b.hid) AS hidFROM x0 a INNER 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 x2 AS ( 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_values INNER JOIN x2 ON 1 = 1WHERE type = 'P' AND number = 5 AND number = 1) SELECT a.id AS 楼层, b.realname AS 姓名 FROM x3 a INNER JOIN ttb b ON b.subname = a.name ORDER BY id
楼层安排如下：
通过以上的代码的介绍，希望对大家的学习有所帮助。
