根据其他 CTE 的结果制作 CTE

2021-09-10 00:00:00 sql tsql sql-server

我有几个加入了 CTE.类似的东西:

I have several joined CTE. Something like:

;With CT1 AS(SELECT ..)
, CT2 AS(select)
SELECT *.T1,*T2 FROM CT1 T1 INNER JOIN CT2 T2 WHERE (some Condition ) GROUP BY (F1,F2, etc)

现在我需要将此查询的结果连接到另一个 CTE.最好的方法是什么?我可以用这个查询的结果做一个 CTE 吗?任何帮助将不胜感激.

Now I need to join the result of this query to another CTE. What’s the best way? Can I make a CTE with the result of this Query? Any help would be greatly appreciated.

推荐答案

您可以继续根据先前定义的 CTE 创建新的 CTE.根据 CTE 规则，它们可以加入或以其他方式合并.

You can keep creating new CTEs based on previously defined ones. They may joined or otherwise combined, subject to the rules for CTEs.

; with
  ArabicRomanConversions as (
    select *
      from ( values
        ( 0, '', '', '', '' ), ( 1, 'I', 'X', 'C', 'M' ), ( 2, 'II', 'XX', 'CC', 'MM' ), ( 3, 'III', 'XXX', 'CCC', 'MMM' ), ( 4, 'IV', 'XL', 'CD', '?' ),
        ( 5, 'V', 'L', 'D', '?' ), ( 6, 'VI', 'LX', 'DC', '?' ), ( 7, 'VII', 'LXX', 'DCC', '?' ), ( 8, 'VIII', 'LXXX', 'DCCC', '?' ), ( 9, 'IX', 'XC', 'CM', '?' )
        ) as Placeholder ( Arabic, Ones, Tens, Hundreds, Thousands )
      ),
  Numbers as (
    select 1 as Number
    union all
    select Number + 1
      from Numbers
      where Number < 3999 ),
  ArabicAndRoman as (
    select Number as Arabic,
      ( select Thousands from ArabicRomanConversions where Arabic = Number / 1000 ) +
      ( select Hundreds from ArabicRomanConversions where Arabic = Number / 100 % 10 ) +
      ( select Tens from ArabicRomanConversions where Arabic = Number / 10 % 10 ) +
      ( select Ones from ArabicRomanConversions where Arabic = Number % 10 ) as Roman
      from Numbers ),
  Squares as (
    select L.Arabic, L.Roman, R.Arabic as Square, R.Roman as RomanSquare
      from ArabicAndRoman as L inner join
        ArabicAndRoman as R on R.Arabic = L.Arabic * L.Arabic
      where L.Arabic < 16 ),
  Cubes as (
    select S.Arabic, S.Roman, S.Square, S.RomanSquare, A.Arabic as Cube, A.Roman as RomanCube
      from Squares as S inner join
        ArabicAndRoman as A on A.Arabic = S.Square * S.Arabic )
  select *
    from Cubes
    order by Arabic
    option ( MaxRecursion 3998 )

相关文章