不得不承认，我脑子梗塞的很严重。。。
表[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]
- /*比较好理解哈：就是通过嵌套查询以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]
- 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
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