利用SQL進行推理

2024-07-21 02:46:32

字體：大中小

來源：轉載

供稿：網友

利用SQL進行推理

數據庫環境：SQL SERVER 2008R2

有如下需求：

Baker,Cooper,Fletcher,MillerandSmith住在一座房子的不同樓層。Baker不住頂層。Cooper不住底層。Fletcher既不住頂層也不住底層。Miller住得比Cooper高。Smith住的樓層和Fletcher不相鄰。Fletcher住的樓層和Cooper不相鄰。用SQL寫出來解題思路:先實現所有人住樓層的排列組合，然后把條件套進去即求得。如何實現排列組合，具體可以參考我前面的文章 http://www.cnblogs.com/boss-he/p/4534017.html1.基礎數據準備

--準備基礎數據，用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

樓層安排如下：