sql - 使用多个 WITH tablename AS (...) 语句 SQL Server

Question

我正在尝试创建两个临时表并将它们与一个永久表连接起来。例如:

WITH temp1 AS (COUNT(*) AS count_sales, ID FROM table1 GROUP BY ID)
WITH temp2 AS (COUNT(*) AS count_somethingelse, ID FROM table2 GROUP BY ID)
SELECT *
FROM table3 JOIN table2 JOIN table1
     ON table1.ID = table2.ID = table3.ID

但似乎存在多个 WITH tablename AS (...) 语句的问题。我试了一个分号。

Answer 1

您的查询应该更像这样:

WITH temp1 AS (
      SELECT COUNT(*) AS count_sales, ID
      FROM table1
      GROUP BY ID
     ),
     temp2 AS (
      SELECT COUNT(*) AS count_somethingelse, ID
      FROM table2
      GROUP BY ID
     )
SELECT *
FROM temp2 JOIN 
     temp1
     ON temp1.ID = temp2.ID;

您的查询有多个错误。我建议您首先了解为什么这个版本可以工作——或者至少可以做一些 报告语法错误以外的事情。然后，再回去研究 SQL。