SQL分组排序 -转载

不得不承认，我脑子梗塞的很严重。。。

 

表[gcc_blogInfo]

blogid        uid          blogtitle        createtime            issketch          checkright

1                1            "hello"          2010-04-01          2                    1

2                1             "hello2"       2010-04-02          2                    1

3                1             "hello3"       2010-04-03          2                    2

4                2              "hello4"      2010-04-01          2                    1

5                2              "hello5"      2010-04-05          2                    1

6                3              "hello6"      2010-04-01          1                    1

7                3              "hello7"       2010-04-02          2                    1

 

最后查询结果为：

blogid        uid          blogtitle        createtime            issketch          checkright

2                1             "hello2"       2010-04-02          2                    1

5                2              "hello5"      2010-04-05          2                    1

7                3              "hello7"       2010-04-02          2                    1

 

 

[c-sharp] view plaincopyprint?

1. /*比较好理解哈：就是通过嵌套查询以uid为分组依据，结合最大聚集函数max(...)的使用先得到满足条件的uid，createtime数据集合再以此为依据查询满足条件的值^-^*/select a.blogid,a.uid,a.blogtitle,a.createtime from[gcc_blogInfo] a,(select uid,max(createtime) createtime from [gcc_blogInfo] where issketch=2 and checkright=1 group by uid) b wherea.uid = b.uid and a.createtime = b.createtime/*e-e同事写的可牛啦，我至今未看懂语句短，功效好*/select * from gcc_blogInfo ta where createTime in (select max(createTime) from gcc_blogInfo tb where tb.uid=ta.uid and issketch=2 and checkright=1 )  

/*比较好理解哈：就是通过嵌套查询以uid为分组依据，结合最大聚集函数max(...)的使用先得到满足条件的uid，createtime数据集合再以此为依据查询满足条件的值^-^*/select a.blogid,a.uid,a.blogtitle,a.createtime from[gcc_blogInfo] a,(select uid,max(createtime) createtime from [gcc_blogInfo] where issketch=2 and checkright=1 group by uid) b wherea.uid = b.uid and a.createtime = b.createtime/*e-e同事写的可牛啦，我至今未看懂语句短，功效好*/select * from gcc_blogInfo ta where createTime in (select max(createTime) from gcc_blogInfo tb where tb.uid=ta.uid and issketch=2 and checkright=1 )

 

分组：

[A]表                                       [B]表

                       

要求输出每个学科前2名学生信息，分数

[sql] view plaincopyprint?

1. select b1.subject, b1.score, a.* from b b1 left join a on a.id = b1.id  
2.   
3. where b1.id in(  
4.   
5. select id from b where score in  
6.   
7. (select distinct top 2 score from b where subject=b1.subject order by score desc))  
8.   
9. order by b1.subject,b1.score desc  

select b1.subject, b1.score, a.* from b b1 left join a on a.id = b1.id where b1.id in( select id from b where score in (select distinct top 2 score from b where subject=b1.subject order by score desc)) order by b1.subject,b1.score desc