How to use SQL for reasoning and SQL for reasoning

How to use SQL for reasoning and SQL for reasoning

Database environment: SQL SERVER 2008R2

There are the following requirements:
Baker, Cooper, Fletcher, Miller and Smith live on different floors of a house.
Baker cannot live on the top layer. Cooper is not at the bottom.
Fletcher does not support the top layer or the bottom layer. Miller lives higher than Cooper.
The floor where Smith lives is not adjacent to Fletcher.
The floor of Fletcher is not adjacent to Cooper.
Written in SQL
 
Solution:
First, the arrangement and combination of everyone's living floors are realized, and then the conditions are obtained by setting them in. How to arrange and combine,
 
1. Basic data preparation
-- Prepare basic data and use A, B, C, D, and E to represent Baker, Cooper, Fletcher, Miller and Smith respectively.

 

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

2. generate an arrangement and combination of all possible conditions
-- Generate all the permutation and combinations of A, B, C, D, and 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. Add conditions to find the floor arrangement that meets the requirements

 

WITH x2 AS (SELECT name FROM # tt where substring (name, 5, 1) <> 'A' -- Baker cannot hold the top layer and substring (name, 1, 1) <> 'B' -- Cooper does not support the underlying AND (SUBSTRING (name, 1, 1) <> 'C' AND SUBSTRING (name, 5, 1) <> 'C' -- Fletcher can neither hold the top layer nor the bottom layer) AND name LIKE '% B % D %' -- Miller lives higher than Cooper AND name NOT LIKE '% CE %' AND name NOT LIKE '% EC %' -- Smith lives on the floor NOT adjacent to Fletcher AND name not like '% BC %' AND name not like '% CB %' -- the floor of Fletcher is NOT adjacent to Cooper ), x3 -- generate floor number 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. id AS floor, B. realname AS name FROM x3 a inner join ttb B ON B. subname =. name order by id

The floor schedule is as follows:

Through the introduction of the above code, I hope to help you learn.