如何利用SQL进行推理

Mathilda ·

更新时间:2024-09-21

· 959 次阅读

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

楼层安排如下：