你印象最深的一道SQL题目是什么?
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)
赞0
踩0