表名 table_2 有3列数据,depid,price,和name,值如下:
depid price name
01 500 aa
02 300 bb
01 550 cc
03 1100 ee
02 178 ee
04 220 ff
03 455 gg按depid分组查询price最高的人的姓名,查询结果应显示如下
depid price name
01 550 cc
02 300 bb
03 1100 ee
04 220 ff
过滤出各个部门工资最高的人员我使用
select depid ,max(price) as 最高工资 from table_2 group by depid 仅能查询到depid和price两项,name这项加不上去。 清大家帮下忙
depid price name
01 500 aa
02 300 bb
01 550 cc
03 1100 ee
02 178 ee
04 220 ff
03 455 gg按depid分组查询price最高的人的姓名,查询结果应显示如下
depid price name
01 550 cc
02 300 bb
03 1100 ee
04 220 ff
过滤出各个部门工资最高的人员我使用
select depid ,max(price) as 最高工资 from table_2 group by depid 仅能查询到depid和price两项,name这项加不上去。 清大家帮下忙
如果没有标识,那就放弃group by 用exists子查询来做
where not exists(select null from tb b where t.depid =b.depid and t.price <b.price )
insert into tb values('01', 500 ,'aa')
insert into tb values('02', 300 ,'bb')
insert into tb values('01', 550 ,'cc')
insert into tb values('03', 1100 ,'ee')
insert into tb values('02', 178 ,'ee')
insert into tb values('04', 220 ,'ff')
insert into tb values('03', 455 ,'gg')
goselect t.* from tb t where price = (select max(price) from tb where depid = t.depid) order by t.depid
/*
depid price name
---------- ----------- ----------
01 550 cc
02 300 bb
03 1100 ee
04 220 ff(所影响的行数为 4 行)
*/select t.* from tb t where not exists (select 1 from tb where depid = t.depid and price > t.price) order by t.depid
/*
depid price name
---------- ----------- ----------
01 550 cc
02 300 bb
03 1100 ee
04 220 ff(所影响的行数为 4 行)
*/
drop table tb
select * from table_2
where price in
(select max(price) as price from table_2
group by depid)
select t.* from tb t where not exists (select 1 from tb where depid = t.depid and price > t.price) order by t.depid这条语句怎么理解呀?
别人告诉你,你也忘的快。