若何應用SQL停止推理

若何應用SQL停止推理。本站提示廣大學習愛好者：（若何應用SQL停止推理）文章只能為提供參考，不一定能成為您想要的結果。以下是若何應用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 ttb
VALUES ( '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

樓層支配以下：

經由過程以上的代碼的引見，願望對年夜家的進修有所贊助。