公用表表达式 [英] Common Table Expression

问题描述

用ctss作为

（

（选择COUNT（st.id）作为计数，

lcty.name作为地点，

spec.speciality as Specality，

0 as Doctor，

0 as unknownspecordoc，

0 unknownLocality





来自ST_Search_Data作为st



内部加入SpecialityMaster作为st.specalityId的规格= spec.id



内连接lcty.id = st.localityId上的lcty位置



其中

（st.specalityId为NOT NULL且st.specalityId！=''且st.localityId不为NULL且st.localityId！=''）



group BY spec.speciality，lcty.name）



）



select ct。 *，ROW_NUMBER（）OVER（通过ct.counts DESC订购）作为行

来自ctss作为ct

其中（ct.row> = 1）和（ct.row< ; = 5）





嗨朋友，


在上面的代码中，我怀疑如何获得行号？

在我的任务中，我必须得到行号以进行分页从sql本身..但在上面的查询中，使用group by函数来获取没有特殊性和局部性的数量..如何在按函数使用时获取行？...在​​最后一行，其中（ct.row> = 1）和（ct.row< = 5）没有这一行我的查询正确执行，但是在最后一行，我得到错误无效的列名行。

with ctss as
(
(select COUNT(st.id) as counts,
lcty.name as Locality,
spec.speciality as Specality,
0 as Doctor,
0 as unknownspecordoc ,
0 unknownLocality


from ST_Search_Data as st

inner join SpecialityMaster as spec on st.specalityId=spec.id

inner join Locality as lcty on lcty.id=st.localityId

where
(st.specalityId is NOT NULL and st.specalityId !='' and st.localityId is not NULL AND st.localityId !='')

group BY spec.speciality , lcty.name)

)

select ct.*,ROW_NUMBER() OVER(ORDER BY ct.counts DESC) as row
from ctss as ct
where (ct.row>=1)and (ct.row<=5)


hi friends,

in this above code , i have doubt about how to get row number ?
in my task, i have to get row number for pagination from sql itself.. but in above query ,there used group by function for getting no.of counts of specality and locality.. how can i get row when used group by function?.. at last line , where (ct.row>=1)and (ct.row<=5) without this line my query excuted correctly, but with the last line , am getting error invalid column name row.

推荐答案

ct.row在当前环境中不存在（好神我一直在查看错误消息太长）



你需要3个查询



您按查询分组

嵌套在一个应用行号的查询中

将这些2嵌套在查询中第1-5行选择

ct.row does not exist in the current context (good god I've been looking at error messages too long)

You need 3 queries

You group by query
nest that inside a a query that applies the row number
nest those 2 inside a query that gets your row 1-5 select

这篇关于公用表表达式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！