mysql> select * from emp where orgid not in(select orgid from org where orgname = 'zong');
+------+-------+----------+---------+--------+
| id | empno | empname | empwage | orgid |
+------+-------+----------+---------+--------+
| 1 | 1000 | zhangsan | 3000 | 900100 |
| 2 | 1001 | lisi | 5000 | 900100 |
| 3 | 1002 | wangwu | 5000 | 800100 |
| 4 | 1003 | zhaoliu | 8000 | 900100 |
+------+-------+----------+---------+--------+
4 rows in set (0.00 sec)mysql> select empno,max(empwage) from emp where orgid not in(select orgid from org where orgname = 'zong');
+-------+--------------+
| empno | max(empwage) |
+-------+--------------+
| 1000 | 8000 |
+-------+--------------+
1 row in set (0.00 sec)
请问为什么第二个sql语句查出的empno是1000而不是1003,不理解???
+------+-------+----------+---------+--------+
| id | empno | empname | empwage | orgid |
+------+-------+----------+---------+--------+
| 1 | 1000 | zhangsan | 3000 | 900100 |
| 2 | 1001 | lisi | 5000 | 900100 |
| 3 | 1002 | wangwu | 5000 | 800100 |
| 4 | 1003 | zhaoliu | 8000 | 900100 |
+------+-------+----------+---------+--------+
4 rows in set (0.00 sec)mysql> select empno,max(empwage) from emp where orgid not in(select orgid from org where orgname = 'zong');
+-------+--------------+
| empno | max(empwage) |
+-------+--------------+
| 1000 | 8000 |
+-------+--------------+
1 row in set (0.00 sec)
请问为什么第二个sql语句查出的empno是1000而不是1003,不理解???
select empno,max(empwage) from emp where orgid not in(select orgid from org where orgname = 'zong') group by empno;
SELECT T2.empno, T2.empwage FROM
(
SELECT MAX(empwage) AS empwage FROM emp WHERE orgid NOT IN(SELECT orgid FROM org WHERE orgname = 'zong')
) AS T1
INNER JOIN
emp AS T2
ON T1.empwage = T2.empwage
LIMIT 0, 1 -- 如果最高工资有多人时需要忽略,只取一条,添加上这句。
order by empwage desc limit 2;