Sql如何提取众多分类的第一条信息

[slayerbb]

常用的文章系统，如何用一条sql语句提取出每个分类的第一条并按OrderCol字段排序。分类大概几十个，一共只要取top 10 这样

[wzy_love_sly]

哈转，但不知道谁把你的签名去了...........

[dobear_0922]

常用的文章系统，如何用一条sql语句提取出每个分类的第一条并按OrderCol字段排序。分类大概几十个，一共只要取top 10 这样

－－－－－－－－

select * from 文章表 a
where not exists(select 1 from 文章表 where 类别=a.类别 and OrderCol>a.OrderCol)
order by OrderCol desc

[slayerbb]

补充一条 ordercol 一样存在 重复现象。

[dawugui]

如果这样,那就判断是否有个字段能区分大小.如果不能,则使用临时表了.

将数据插入TMP

select * , id = identity(int , 1 , 1) into tmp from tb

[slayerbb]

分类 列 和 排序 列 同时相等的情况 很多

[slayerbb]

我现在脑袋有些糨糊，呵呵

[slayerbb]

有Id字段 identity 类型

[dawugui]

那就把上面的结果用同样的方法再用ID做一次.

[dawugui]

--取小
select top 10 m.* from
(
  select t.* from tb t where OrderCol = (select min(OrderCol) from tb where 分类 = t.分类) 
) m where id = (select min(id) from 
(
  select t.* from tb t where OrderCol = (select min(OrderCol) from tb where 分类 = t.分类) 
) n where m.分类 = n.分类
)
--取大
select top 10 m.* from
(
  select t.* from tb t where OrderCol = (select max(OrderCol) from tb where 分类 = t.分类) 
) m where id = (select max(id) from 
(
  select t.* from tb t where OrderCol = (select max(OrderCol) from tb where 分类 = t.分类) 
) n where m.分类 = n.分类
)

[fcuandy]

天天都有重复的问题要回答.

[-狙击手-]

关注

[slayerbb]

一定要这么麻烦的么。。。

[dawugui]

我看得这么麻烦.

[-狙击手-]

[code=SQL]declare @t table(type varchar(4),detail varchar(10))
insert @t select
'001','a' union select 
'001','sss' union select 
'001','gssfa' union select 
'002','fdsf' union select 
'002','fs' union select 
'002','gahas' union select 
'003','sdga' union select 
'003','hahasg' union select 
'003','gdsag'



select a.type,a.detail
from @t a 
join @t b
on a.type= b.type
group by a.type,a.detail
having count(case when a.detail <= b.detail then 1 else null end) < = 2 --可动态修改
order by a.type asc 

/*


type detail     
---- ---------- 
001  gssfa
001  sss
002  fs
002  gahas
003  hahasg
003  sdga

（所影响的行数为 6 行）
*/

select a.* from @t a 
where exists (select count(*) 
              from @t
              where type = a.type and detail > a.detail having Count(*) < 2) 
order by a.type



/*


type detail     
---- ---------- 
001  gssfa
001  sss
002  fs
002  gahas
003  hahasg
003  sdga

（所影响的行数为 6 行）
*/
select a.* from @t a 
where exists (select count(*) 
              from @t
              where type = a.type and detail < a.detail having Count(*) < 2) 
order by a.type
/*
type detail     
---- ---------- 
001  a
001  gssfa
002  fdsf
002  fs
003  gdsag
003  hahasg

（所影响的行数为 6 行）
*/

select a.* 
from @t a 
where detail in (select top 2 detail 
              from @t where type=a.type 
              order by detail desc) 
order by a.type,a.detail
/*
type detail     
---- ---------- 
001  gssfa
001  sss
002  fs
002  gahas
003  hahasg
003  sdga

（所影响的行数为 6 行）
*/
select a.* 
from @t a 
where detail in (select top 2 detail 
              from @t where type=a.type 
              order by detail asc) 
order by a.type,a.detail
/*

type detail     
---- ---------- 
001  a
001  gssfa
002  fdsf
002  fs
003  gdsag
003  hahasg

（所影响的行数为 6 行）
*/

[/code]

[wei123456]

学习

[pt1314917]

天天答。不答了。

[IT_zen]

mark

[slayerbb]

select top 10 m.id as id, rtrim(left(title,11)) as titles,Clicks,AddTime,Auther,meno,Classes,s.phone as phone,s.name as name from
(select t.* from tb t where refreshtime = (select max(OrderCol) from tb where userid = t.userid) ) m
inner join UserTable on s.userid=m.userid where m.id = (select max(id) from 
(select t.* from tb t where OrderCol = (select max(OrderCol) from tb where isshow=0 and userid = t.userid)) n 
where m.userid = n.userid)  
 order by OrderColdesc

现在使用的代码。。。
感觉又臭又长。。。

[alex_xjf]

选择多了点,,,不过不错的!

[napoleaon]

天天答。不答了。