有两张表,一张是员工信息表T_EMP_INFO,字段有USER_ID,USER_NANE;一张是工资表T_WAGE_INFO,字段有ID,USER_ID(关联员工表字段),SALERY(工资),MONTH(月份);
1、查询每位员工最近一个月发工资的情况:
SELECT e.NAME,MAX(d.MONTH),d.*
from T_EMP_INFO e LEFT JOIN T_WAGE_INFO d on d.USER_ID= e.USER_ID
GROUP BY d.USER_ID
2、查询张三的发工资情况,按发放的时间由远到近:
SELECT e.NAME , d.*
FROM t_emp_info e LEFT JOIN T_WAGE_INFO d on d.USER_ID= e.USER_ID
WHERE d.NAME= '张三'
ORDER BY d.MONTH
3、查询所有工资发放情况,由近到远:
SELECT e. NAME, d.* FROM t_emp_info e
LEFT JOIN T_WAGE_INFO d ON d.USER_ID= e.USER_ID
ORDER BY d.NAME DESC,d.MONTH DESC