问题描述
用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.
推荐答案
这篇关于公用表表达式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！