select distinct top 15 ca,cb from db where ca not in(select distinct top 15 ca from db order by cb desc) order by cb desc
出现错误:
如果指定了 SELECT DISTINCT,那么 ORDER BY 子句中的项就必须出现在选择列表中改一下:
select distinct top 15 ca,cb from db where ca not in(select distinct top 15 ca,cb from db order by cb desc) order by cb desc
有出现错误:
当没有用 EXISTS 引入子查询时,在选择列表中只能指定一个表达式。 大家看看该怎么处理?
出现错误:
如果指定了 SELECT DISTINCT,那么 ORDER BY 子句中的项就必须出现在选择列表中改一下:
select distinct top 15 ca,cb from db where ca not in(select distinct top 15 ca,cb from db order by cb desc) order by cb desc
有出现错误:
当没有用 EXISTS 引入子查询时,在选择列表中只能指定一个表达式。 大家看看该怎么处理?
from (
select top 15 ca,cb
from db
where ca not in(
select top 15 ca
from db
order by cb desc)
order by cb desc
) as t
where not exists
(select distinct top 15 ca,cb from db where ca=t.ca order by cb desc)
order by cb desc
from db
where ca not in(
select top 15 ca
from db
order by cb desc)
order by cb desc 好像这样就可以了。子查询内不需要distinct
另外子查询内需要distinct,否则就会出现重复数据,因为表中有CA重复的情况
http://topic.csdn.net/u/20091130/21/fb718680-98ff-4afb-98d8-cff2f8293ed5.html?24281
表db
CA CB
M1 100
M2 21
M3 45
M4 56
M1 55
M1 99
M1 67
M2 56先这几条吧,可能您觉得这些数据有重复,实际上,我要的东西与CB没有多大关系,但是又要按CB排序。DB表实际上是一个通过查询获得的表
select distinct top 3 ca,cb from db
where ca not in(select distinct top 3 ca,cb from db order by cb desc)
order by cb desc
出现错误就在distinct 上,如果有其它办法,例如实时获得表数据在表中的位置(行),就好办了查询到的行<先前查询过的行
--多多动手,相互提升
select ca,cb from
(select ca,cb,row_number() over(partition by ca order by cb desc) rn from db) a
where rn between 16 and 30