如何利用SQL进行推理_mssql2008_

 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