Execute the code, but it is not the desired results-CodePudding

Execute the following code, but not want

 if object_id (' tempdb for. Dbo. # tb1) is not null drop table # # tb1 - the select * from tb1 
Go 
The create table # tb1 (rids/int IDENTITY (1, 1) NOT NULL, n1 int, n2 int, n3 int, n4 interchange int, n5 int, n6 int, n7 int, n8 int, n9 int, n10 int, n11 int, n12 int, n13 int, n14 int, n15 int) 
Insert # tb1 
Select '3', '4', '5', '7', '9', ' ' ' ' ' ', ' ' ' ' ' ', ' ' ' ' ' ', 'union all 
Select '4', '5', 6 ', '7', '9', '12', '16', '18', 'the', '35', ' ' ' ' ' ' ' ', 'union all 
Select '3', '4', '5', '7', '9', '12', '16', '18', 'the', '26', 'and', ' ' ' ' ' ', 'union all 
Select '1', '2', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12', ' ', ' 'and' union all 
Select '9', '10', 11 ', '12' and '13', '15', '16', '18', 'the', '26', 'and', '29', 'and', '33', '35' 

; WITH CTE AS (select QTY from # tb1 unpivot (QTY for TYPE in (n1, n2 and n3 and n4 interchange, n5 and n6, n7, n8, n9, n10, next 11, n12, n13, n14, n15)) AS p where QTY<> 0) 

T the AS (
The select QTY as an rn from CTE 
) 
B.r SELECT a.r n, n, c.r n, d.r n, "e.r n 
The FROM t a 
The JOIN t b ON a.r nThe JOIN t c ON b.r nThe JOIN t d ON c.r nThe JOIN t e ON d.r n<"E.r n

Hope is
In turn calls rids=1 # tb1, rids=2, rids=3, rids=4, rids=5 operations, respectively, finally together,
Mistake is now: WITH CTE AS the code

 WITH CTE AS (select QTY from # tb1 unpivot (QTY for TYPE in (n1, n2 and n3 and n4 interchange, n5 and n6, n7, n8, n9, n10, next 11, n12, n13, n14, n15)) AS p where QTY<> 0)

Lead to Rid=1, rids=2, rids=3, rids=4, rids=5 n1, n2 and n3 and n4 interchange, n5 and n6, n7, n8, n9, n10, next 11, n12, n13, n14, n15 mixed together, so that the result of the operation is not want
Question: in turn calls rids=1 # tb1, rids=2, rids=3, rids=4, rids=5 n1, n2, n3 and n4 interchange, n5 and n6, n7, n8, n9, n10, next 11, n12, n13, n14, n15 operations, respectively, finally together,

CodePudding user response:

Generated when the CTE will include rids, behind associated query, will use rids associated fields

CodePudding user response:

1 # right, don't want to mix will have to distinguish between the fields of the

 
; WITH t AS (SELECT p.R id, QTY AS an rn 
The FROM # tb1 
UNPIVOT (QTY 
FOR the TYPE IN (n1, n2 and n3 and n4 interchange, n5 and n6, n7, n8, n9, n10, next 11, n12, n13, n14, n15)) AS p 
WHERE QTY & lt;> 0) 
The SELECT a.R id, a.R n, b.r n, c.r n, d.r n, "e.r n 
The FROM t a 
The JOIN t b ON a.r n & lt; B.r n AND b.r id=a.R id 
The JOIN t c ON b.r n & lt; C.r n AND c.r id=b.R id 
The JOIN t d ON c.r n & lt; D.r n AND d.r id=c.R id 
The JOIN ON d.r n t e & lt; "E.r n" AND "e.r id=d.R id;

CodePudding user response:

The

Hello World reference 2 floor, response:

Thank the bosses of meticulous knowledge

extension:
1. If will choose b1, then all the results must be contained in the b1,
2. If will choose b1, b2, so the results of all must contain b1, b2
3. If will choose b1, b2, b3, then the results of all must contain b1, b1, b3
# tb1 built table

# tb1 built predicate sentence

 if object_id (' tempdb for. Dbo. # tb1) is not null drop table # # tb1 - the select * from tb1 
Go 
Create table # tb1 (rids/int IDENTITY (1, 1) NOT NULL, int b1, b2 int, b3 int, b4 int, n1 int, n2 int, n3 int, n4 interchange int, n5 int, n6 int, n7 int, n8 int, n9 int, n10 int, n11 int) 
Insert # tb1 
Select '7', '21', ' 'and', '3', '4', '5', '17', '29', ' 'and', ' ' ' ' ' ', 'union all 
Select '3', '24', '25', ', '4', '5', '6', '7', '9', '12', '16', '18', 'the', '35', 'union all 
Select '29', ' ' ' ' ' ', '3', '4', '5', '7', '9', '12', '16', '18', 'the', '26', '28' union all 
Select "', ' ', ' 'and', '5', '6', '7', '8', '9', '10', '11', '12', ' ', ' 'and' union all 
Select "', ' ', ' ', ' 'and' 13 ', '15', '16', '18', '19', '26', '28', '29', 'and', '33', '35'

 
; WITH t AS (SELECT p.R id, QTY AS an rn 
The FROM # tb1 
UNPIVOT (QTY 
FOR the TYPE IN (b1, b2, b3, b4, n1, and n2 and n3 and n4 interchange, n5 and n6, n7, n8, n9, n10, next 11)) AS p 
WHERE QTY & lt;> 0) 
The SELECT a.R id, a.R n, b.r n, c.r n, d.r n, "e.r n -- -- -- -- -- how to recognize the b1, b2, b3, b4? 
The FROM t a 
The JOIN t b ON a.r n & lt; B.r n AND b.r id=a.R id 
The JOIN t c ON b.r n & lt; C.r n AND c.r id=b.R id 
The JOIN t d ON c.r n & lt; D.r n AND d.r id=c.R id 
The JOIN ON d.r n t e & lt; "E.r n" AND "e.r id=d.R id;