问题描述
我有几个加入了 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 )