如何利用SQL进行推理

【 tulaoshi.com - 编程语言 】

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

楼层安排如下：

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