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

 

 

/*比较好理解哈：就是通过嵌套查询以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名学生信息，分数

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