利用SQL进行推理

数据库环境：SQL SERVER 2008R2

有如下需求：

--准备基础数据，用A、B、C、D、E分别表示Baker, Cooper, Fletcher, Miller and SmithCREATE 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 hid               UNION ALL               SELECT   CONVERT(VARCHAR(10), 'B') AS hid               UNION ALL               SELECT   CONVERT(VARCHAR(10), 'C') AS hid               UNION ALL               SELECT   CONVERT(VARCHAR(10), 'D') AS hid               UNION ALL               SELECT   CONVERT(VARCHAR(10), 'E') AS hid             ),        x1          AS ( SELECT   hid               FROM     x0               WHERE    LEN(hid) <= 5               UNION ALL               SELECT   CONVERT(VARCHAR(10), a.hid + b.hid) AS hid               FROM     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   name               FROM     #tt               WHERE    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 name               FROM     master.dbo.spt_values                        INNER JOIN x2 ON 1 = 1               WHERE    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

楼层安排如下：

(本文完)