如何在选择中使用CTE的结果？ [英] How to use the result of CTE in select ?

问题描述

<预> 
，其中Notesdate为
（
选择KeyID，InsertedDate，NOTEID，ROW_NUMBER（）结束（由InsertedDate desc按KEYID订单分区）作为ROWNUMBER来自Note 
）
  - -select inserteddate，KEYID来自Notesdate ND，其中rownumber = 1 
 
 SELECT Distinct 
 
 
 CASE WHEN LV.TypeName LIKE'％NOTES％'
 THEN 'Notes'END AS Name，
 
 
 CASE WHEN LV.TypeName LIKE'％NOTES％'
 THEN 
 
  -  THEN N.InsertedDate = （从Notesdate中选择inserteddate，其中ROWNUMBER = 1）
 
 FROM dbo.NOT N 
内部JOIN Notesdate ND On ND.NoteID = N.NoteID 
 
 WHERE 
 N.InsertedByUserID<>'0'

在第二个案例中，我想要为rownumber = 1获取Inserteddate的值。



如何实现这个目标？



我尝试过：

< pre> 
，其中Notesdate为
（
选择KeyID，InsertedDate，NOTEID，ROW_NUMBER（）结束（由InsertedDate desc按KEYID订单分区）作为ROWNUMBER来自Note 
）
  - -select inserteddate，KEYID来自Notesdate ND，其中rownumber = 1 
 
 SELECT Distinct 
 
 
 CASE WHEN LV.TypeName LIKE'％NOTES％'
 THEN 'Notes'END AS Name，
 
 
 CASE WHEN LV.TypeName LIKE'％NOTES％'
 THEN 
 
  -  THEN N.InsertedDate = （从Notesdate中选择inserteddate，其中ROWNUMBER = 1）
 
 FROM dbo.NOT N 
内部JOIN Notesdate ND On ND.NoteID = N.NoteID 
 
 WHERE 
 N.InsertedByUserID<>'0'

解决方案

查看此链接。希望这有帮助

公用表格表格（CTE简介） - 基本SQL [ ^ ]

<pre>
with Notesdate as
(
select KeyID,InsertedDate,NOTEID,ROW_NUMBER() over(Partition by KEYID order by InsertedDate desc) as ROWNUMBER from Note
)
--select inserteddate,KEYID from Notesdate ND where rownumber=1

SELECT Distinct		
	 
	
	          CASE WHEN LV.TypeName LIKE '%NOTES%'
	               THEN  'Notes' END AS Name,


            CASE WHEN LV.TypeName LIKE '%NOTES%'
			THEN 
			  
			  --     THEN N.InsertedDate= (Select  inserteddate from Notesdate where ROWNUMBER=1 ) 

FROM dbo.NOT N 
Inner JOiN Notesdate ND On ND.NoteID=N.NoteID

WHERE 
 N.InsertedByUserID<>'0'   

In the second case statment, I want to get the value of Inserteddate for rownumber=1.

How to achieve this?

What I have tried:

<pre>
with Notesdate as
(
select KeyID,InsertedDate,NOTEID,ROW_NUMBER() over(Partition by KEYID order by InsertedDate desc) as ROWNUMBER from Note
)
--select inserteddate,KEYID from Notesdate ND where rownumber=1

SELECT Distinct		
	 
	
	          CASE WHEN LV.TypeName LIKE '%NOTES%'
	               THEN  'Notes' END AS Name,


            CASE WHEN LV.TypeName LIKE '%NOTES%'
			THEN 
			  
			  --     THEN N.InsertedDate= (Select  inserteddate from Notesdate where ROWNUMBER=1 ) 

FROM dbo.NOT N 
Inner JOiN Notesdate ND On ND.NoteID=N.NoteID

WHERE 
 N.InsertedByUserID<>'0'   

解决方案

Check out this link. hope this helps
Common Table Expressions (Introduction to CTE's) - Essential SQL[^]

这篇关于如何在选择中使用CTE的结果？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！