征服Bug_社区达人页

积极参与

1，求2004年一月份工资最低的员工姓名 mysql> select * from user u join salary s on u.id=s.userid and salary = (select min(salary) from salary where year=2004 and month=1) and year=2004 and month=1; +----+----------+------+------+--------+--------+-------+------+ | id | name | age | sex | userid | salary | month | year | +----+----------+------+------+--------+--------+-------+------+ | 1 | zhangsan | 27 | n | 1 | 1000 | 1 | 2004 | +----+----------+------+------+--------+--------+-------+------+ 1 row in set (0.00 sec) 2，删除User表中Sex为女的用户，在 Salary 表中的所有信息 delete from salary where userid=(select id from user where sex='v'); 3，求Sex 等于男的用户，2004 年第一季度的平均工资 mysql> select u.sex,avg(s.salary) from user u inner join salary s on u.id=s.userid and sex='n' and year=2004 and month between 1 and 3 group by u.sex; +------+---------------+ | sex | avg(s.salary) | +------+---------------+ | n | 1666.6667 | +------+---------------+ 1 row in set (0.00 sec) 4，求Sex 等于男并且2004年3 月份有涨工资的用户 mysql> select a.name from (select u.name,s.salary from salary s join user u on u.id=s.userid and u.sex ='n' and month=2) a,(select u.name,s.salary from salary s join user u on u.id=s.userid and u.sex='n' a nd month=3) b where a.name=b.name and b.salary>a.salary; +----------+ | name | +----------+ | zhangsan | +----------+ 1 row in set (0.00 sec)