一个分组时间函数查询语句
mysql数据查询：
t_user表：数据如下
login_name?? user_sex??? user_birthday
admin?????????????? 男 ???????????? 1970-01-07
eee?????????????????? 男 ????????????? 1970-01-07
rrr ??????????????????? 女 ????????????? 1970-01-21
test ???????????????? 女 ???????????? 1970-01-21
ttt ??????????????????? 男 ????????????? 1970-01-15
www ?????????????? 男 ????????????? 1970-01-15
?
要求查询出距今出生日期最近的性别分别为男女的人的信息，即要求查询出名字为ttt,www,rrr,test的人的信息
sql语句如下：
SELECT t.login_name,t.user_sex,t.user_birthday FROM t_user t LEFT JOIN (SELECT MIN(TIMESTAMPDIFF(DAY,a.user_birthday,CURRENT_DATE())) AS mina,a.user_sex AS sex FROM t_user a GROUP BY a.user_sex) AS aaONTIMESTAMPDIFF(DAY,user_birthday,CURRENT_DATE()) = aa.mina WHERE t.user_sex = aa.sex?
?
?
查询结果如下：
login_name?? user_sex??? user_birthday
rrr?????????????? ? ? ?? 女 ???????????? 1970-01-21
test ????????????????? 女 ????????????? 1970-01-21
ttt ???????????????????? 男 ????????????? 1970-01-15
www ??????????????? 男 ?????????????? 1970-01-15
?
