SQL分组排序 -转载

时间:2022-03-16 22:32:22

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

 

表[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 )  

 

分组:

[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