CREATE TABLE ttb
 (
  subname VARCHAR(1) ,
  realname VARCHAR(10)
 )
INSERT INTO ttb
VALUES ( 'A', 'Baker' ),
  ( 'B', 'Cooper' ),
  ( 'C', 'Fletcher' ),
  ( 'D', 'Miller' ),
  ( 'E', 'Smith' )

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

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