具有多个选择的单个CTE，在SQL Server中创建了两次CTE

yshpjwxd 于 2022-10-03 发布在 SQL Server

关注(0)|答案(2)|浏览(188)

要求是连接(配对)一些字段上的两个表，然后按顺序显示成对的结果。在CTE中正在进行配对。为一对分配一个GUID，然后使用该GUID按顺序显示每一对。对于Oracle(11.2)，它如预期的那样工作，但对于SQL Server(2017)，CTE似乎创建了两次，因为每一行都有自己的GUID。

我如何使用CTE来解决这个问题？为什么SQL Server会有这种行为？

SQL Serverhttp://sqlfiddle.com/#！18/21c167/1

已使用Oraclehttp://sqlfiddle.com/#！4/c49dbb/2*(DBMS_RANDOM.RANDOM代替SYSGUID()，因为SYSGUID()在我的开发机器上运行正常时，为SQLfiddle.com上的所有行返回相同的值)*

CREATE TABLE VEHICLE (CODE NUMERIC(3,0), [TYPE] VARCHAR(50), DRIVER_CATEGORY VARCHAR(2));
CREATE TABLE DRIVER (CODE NUMERIC(9,0), NAME VARCHAR(200), CATEGORY VARCHAR(2));

INSERT INTO VEHICLE VALUES (1, 'LIMOUSINE', 'A');
INSERT INTO VEHICLE VALUES (2, 'TRUCK', 'B');
INSERT INTO VEHICLE VALUES (3, 'SUV', 'C');
INSERT INTO VEHICLE VALUES (4, 'SEDAN', 'C');
INSERT INTO VEHICLE VALUES (5, 'SPORTS CAR', 'D');

INSERT INTO DRIVER VALUES (1, 'James', 'A');
INSERT INTO DRIVER VALUES (2, 'Robert', 'B');
INSERT INTO DRIVER VALUES (3, 'John', 'C');
INSERT INTO DRIVER VALUES (4, 'Jennifer', 'C');
INSERT INTO DRIVER VALUES (5, 'Patricia', 'D');
INSERT INTO DRIVER VALUES (6, 'Susan', 'A');     
INSERT INTO DRIVER VALUES (7, 'Lisa', 'B');     

  ;WITH MATCHPAIRS AS 
  ( 
      SELECT V.CODE VEHICLE_CODE, D.CODE DRIVER_CODE, V.TYPE TYPE, V.DRIVER_CATEGORY, D.NAME, D.CATEGORY, NewID() PAIRID
      FROM VEHICLE V INNER JOIN DRIVER D ON V.DRIVER_CATEGORY = D.CATEGORY
  )
    SELECT p.VEHICLE_CODE, p.DRIVER_CODE, P.PAIRID, V.TYPE TYPE_NAME, 0 RECORD_ORDER  
    FROM VEHICLE V INNER JOIN MATCHPAIRS P ON V.CODE = P.VEHICLE_CODE 
    UNION ALL  
    SELECT p.VEHICLE_CODE, p.DRIVER_CODE, P.PAIRID, D.NAME TYPE_NAME, 1 RECORD_ORDER  
    FROM DRIVER D INNER JOIN MATCHPAIRS P ON D.CODE = P.DRIVER_CODE
    ORDER BY PAIRID, RECORD_ORDER

sql-server

来源：https://stackoverflow.com/questions/73894601/single-cte-with-multiple-selects-cte-created-twice-in-sql-server

2条答案

按热度按时间

zf2sa74q1#

CTE不会被缓存，它们会为每个引用重新计算。

无论如何，NEWID有时会做一些奇怪的事情，当不在最外面的SELECT上使用时，所以最好使用临时表。CROSS APPLY或CROSS JOIN有时也不适用于NEWID。

或者，改用不同的唯一项。例如

;WITH MATCHPAIRS AS (
    SELECT
      V.CODE VEHICLE_CODE,
      D.CODE DRIVER_CODE,
      V.TYPE TYPE,
      V.DRIVER_CATEGORY,
      D.NAME,
      D.CATEGORY,
      ROW_NUMBER() OVER (ORDER BY v.CODE, D.CODE) PAIRID
    FROM VEHICLE V
    INNER JOIN DRIVER D ON V.DRIVER_CATEGORY = D.CATEGORY
)
SELECT
  p.VEHICLE_CODE,
  p.DRIVER_CODE,
  P.PAIRID,
  V1.TYPE_NAME,
  v1.RECORD_ORDER  
FROM MATCHPAIRS P
CROSS APPLY (VALUES
  (p.TYPE, 0),
  (p.NAME, 1)
) V1(TYPE_NAME, RECORD_ORDER)
ORDER BY
  PAIRID,
  RECORD_ORDER;

请注意，使用CROSS APPLY (VALUES取消透视，省去了两次读取CTE。

SQL小提琴

赞(0）回复(0）举报 2022-10-03

vsdwdz232#

最简单的解决方案是使用临时表而不是CTE。如果需要，可以将代码 Package 在存储过程中。

赞(0）回复(0）举报 2022-10-03