SQL Server How to use multiple with statements along with UNION ALL in SQL?

ocebsuys 于 2023-04-19 发布在其他

关注(0)|答案(3)|浏览(128)

WITH L1 AS
(
   SELECT
)
SELECT A FROM L1

UNION ALL

SELECT A FROM TABLE

UNION ALL

WITH L2 AS
(
   SELECT
)
SELECT A FROM L2

UNION ALL

WITH L3 AS
(
   SELECT
)
SELECT A FROM L3

I get an error

Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon."

Please help

sql-server

来源：https://stackoverflow.com/questions/25317210/how-to-use-multiple-with-statements-along-with-union-all-in-sql

3条答案

按热度按时间

0dxa2lsx1#

You cannot use WITH in the middle of a query expression. WITH is used to build up intermediate queries for use by other queries immediately after (meaning it cannot be used by multiple independent queries).

So you probably want something like:

WITH L1 
AS
(
    SELECT ...
),
L2 AS
(
    SELECT ...
),
L3 AS
(
    SELECT ...
)

// begin final query    
SELECT A FROM L1
UNION ALL
SELECT A FROM TABLE
UNION ALL
SELECT A FROM L2
UNION ALL
SELECT A FROM L3

赞(0）回复(0）举报 2023-04-19

68de4m5k2#

The syntax is

With l1 ( a ) as ( Select ... )
   , l2 ( a ) as ( ... )
  Select ... From ...
  Union
  Select ... From ...

赞(0）回复(0）举报 2023-04-19

58wvjzkj3#

Two ways to do this:

SELECT A FROM L1
UNION ALL
SELECT A FROM L2
UNION ALL
SELECT A FROM L3

WITH CTE_L AS
(
    SELECT A FROM L1
    UNION ALL
    SELECT A FROM L2
    UNION ALL
    SELECT A FROM L3
)
SELECT * FROM CTE_L

赞(0）回复(0）举报 2023-04-19

我来回答

SQL Server How to use multiple with statements along with UNION ALL in SQL?

3条答案

相关问题

热门标签

最新问答