如何利用SQL进行推理

数据库环境：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

楼层安排如下：

通过以上的代码的介绍，希望对大家的学习有所帮助。