平时只把数据库作为存储数据的工具，都是读出所有数据，然后再处理。这种把业务逻辑封装在程序中的方法可移植性比较好。

今天，为了快速实现oj暑期训练的一些功能，直接用gridview显示数据，要写若干查询语句，我竟然百度了好久才完成。整理一下资料放在这里，减少以后重构的时间。

 

查询语句顺序：

select...from...where...group by...having...order by...

执行顺序：

from...where...group by ...having...select...order by...

 

//查询今天签到的同学

select true_name from sign_in Where DATE_FORMAT((datetime),'%Y-%m-%d') = DATE_FORMAT(NOW(),'%Y-%m-%d') group by true_name

 

//查询今天没签到的同学(使用子查询)

select true_name from user where status=0 and true_name not in(select true_name from sign_in Where DATE_FORMAT((datetime),'%Y-%m-%d') = DATE_FORMAT(NOW(),'%Y-%m-%d') group by true_name)

 

//按今天签到次数排名

select true_name,datetime,ip_address,count(*) as num  from sign_in Where DATE_FORMAT((datetime),'%Y-%m-%d') = DATE_FORMAT(NOW(),'%Y-%m-%d') group by true_name order by num desc

 

//今天讲题

select * from teach Where DATE_FORMAT((datetime),'%Y-%m-%d') = DATE_FORMAT(NOW(),'%Y-%m-%d') 

 

//今天讲题 按老师分组

select * from teach Where DATE_FORMAT((datetime),'%Y-%m-%d') = DATE_FORMAT(NOW(),'%Y-%m-%d') group by name_teacher

 

//今天讲题 按老师分组 大于等于两题

select * from teach Where DATE_FORMAT((datetime),'%Y-%m-%d') = DATE_FORMAT(NOW(),'%Y-%m-%d') group by name_teacher having count(*)>=1

 

//今天每个老师讲题次数并排序15:29 2011-06-30

select name_teacher,count(*) as num from teach Where DATE_FORMAT((datetime),'%Y-%m-%d') = DATE_FORMAT(NOW(),'%Y-%m-%d') group by name_teacher order by num desc

 

//今天每个学生讲题次数并排序

select name_student,count(*) as num from teach Where DATE_FORMAT((datetime),'%Y-%m-%d') = DATE_FORMAT(NOW(),'%Y-%m-%d') group by name_student order by num desc