有查询
select * from Sales where (saleArea="beijing" or saleArea="shanghai") and saleAmount>1000 order by saleAmount
如只想让beijing和shanghai的纪录个出现5个(saleAmount最小的5个)
该怎样写?
请教各位大侠
...全文
35710打赏收藏
复杂查询中限制SQL SERVER返回的记录数
有查询 select * from Sales where (saleArea="beijing" or saleArea="shanghai") and saleAmount>1000 order by saleAmount 如只想让beijing和shanghai的纪录个出现5个(saleAmount最小的5个) 该怎样写? 请教各位大侠
create table #temp(RowNum int identify,Sales表的列)
insert into #temp /*对数据序列化*/
select Sales.*
from Sales ,SaleAreaTable
where Sales.SaleArea=SaleAreaTable.SaleArea and
Sales.aleAmount>1000
order by Sales.saleAmount
select a.需要输出的列
from (
select a2.*,(case /*确定是本地区的前5名*/
when count(*)<=5 then 1
else 0
)"IsOutput"
from #temp a1,#temp a2
where a1.RowNum<=a2.RowNum and /*此处必须是<=,否则会漏掉行*/
a1.SaleArea=a2.SaleArea
group by a2.SaleArea
select top 5 * from sales where salearea="beijing" and saleamount>1000
union
select top 5 * from sales where salearea="shanghai" and saleamount>1000
order by saleamount