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
用: set rowcount n -- 返回前面n条记录....查询语句set rowcount 0 -- 参数复位
用ADO全部查出,然后显示时把不需要的滤掉快,还是按929的说法快
如果你只有两个明确的销售地区需要查询,929的方法是最优的如果有不确定多个销售地区需要查询 就可以采用先序列化后对序列排除即可假如你的销售地区在 SaleAreaTable表中存放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.saleAmountselect 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 ) a where IsOutput=1 drop table #temp
saleArea="beijing" 的出现5个
saleArea="shanghai的出现5个
拜托!拜托!
union
select top 5 * from sales where salearea="shanghai" and saleamount>1000
order by saleamount
set rowcount n
-- 返回前面n条记录....查询语句set rowcount 0 -- 参数复位
就可以采用先序列化后对序列排除即可假如你的销售地区在 SaleAreaTable表中存放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.saleAmountselect 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 ) a
where IsOutput=1
drop table #temp
少写一个与case 配对的end