求每个部门员工工资最高的员工姓名，工资及部门

[shaojianling]

如题。

[dawugui]

select * , 工资 = (select max(工资) from tb where 员工ID = t.员工ID) from tb t

[dawugui]

select * , 工资 = (select max(工资) from tb where 部门ID = t.部门ID) from tb t

[dawugui]

我2楼错了,4楼才对.不好意思.

[shaojianling]

晕了。这种还用得着表结构吗。

[lxuan_025]

select distinct name,department,wage as wage from tb1 t where wage = (select max(wage) from tb1 where department = t.department)

[edithbianyuhua]

发表于：2008-09-17 21:47:184楼 得分:0
select * , 工资 = (select max(工资) from tb where 部门ID = t.部门ID) from tb t


select * from tb where 工资=（select max(工资) from tb ）

[等不到来世]

select * from tb a
where not exists (select 1 from tb where 部门=a.部门 and 员工<>a.员工 and 工资>a.工资)

[.NET快速开发框架]

select 姓名,max(工资) as 工资,部门 from 部门 group by 部门


欢迎光临这儿,有很多数据库与.NET的知道,来看吧....
http://blog.csdn.net/chinahuyong

[realgz]

有对的么？没看出来。。。。

缺少条件：如果工资最高的人有两个怎么办？表结构怎么样？

同意
[Quote=引用 3 楼 liangCK 的回复:]
这种如题的帖子..

建议版主删了..
[/Quote]

[LIUMQX]

学习之中。

[wuyi8808]

-- 先查询出每个部门的最高工资：
select 部门,max(工资) as 最高工资
from 工资表
group by 部门

-- 然后呢？

[中国风]

參照:
http://topic.csdn.net/u/20080626/00/43d0d10c-28f1-418d-a05b-663880da278a.html

[生活真美好]

SELECT bm.bmid, bm.bmmc, max(rygz) as bmmaxgz
, (select top 1 ryxm from rytable where rygz = (SELECT max(rygz) FROM rytable where bmid = bm.bmid) and bmid = bm.bmid) as bmmaxryxm
FROM bmtable as bm INNER JOIN
rytable as ry ON bm.bmid = ry.bmid
group by bm.bmid, bm.bmmc

[生活真美好]

CREATE TABLE [dbo].[bmtable] (
[bmid] [int] IDENTITY (1, 1) NOT NULL ,
[bmmc] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[rytable] (
[ryid] [int] IDENTITY (1, 1) NOT NULL ,
[bmid] [int] NOT NULL ,
[ryxm] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[rygz] [int] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[bmtable] ADD
CONSTRAINT [PK_bmtable] PRIMARY KEY CLUSTERED
(
[bmid]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[rytable] ADD
CONSTRAINT [DF_rytable_rygz] DEFAULT (0) FOR [rygz],
CONSTRAINT [PK_rytable] PRIMARY KEY CLUSTERED
(
[ryid]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[rytable] ADD
CONSTRAINT [FK_rytable_bmtable] FOREIGN KEY
(
[bmid]
) REFERENCES [dbo].[bmtable] (
[bmid]
)
GO

[生活真美好]

测试数据

部门表
1 bm1
2 bm2
3 bm3

人员表
1 1 ry11 1
2 1 ry12 10
3 1 ry13 10
4 2 ry21 2
5 2 ry22 3
7 3 ty31 5

[生活真美好]

测试结果
bmid bmmc bmmaxgz bmmaxryxm
----------- -------------------------------------------------- ----------- --------------------------------------------------
1 bm1 10 ry12
2 bm2 3 ry22
3 bm3 5 ty31

（所影响的行数为 3 行）

[了無痕]

select 部门,max(工资) as 最高工资,姓名 from 工资表 group by 部门

[生活真美好]

楼主不厚道，这么难的题，分少还不结贴

呵呵

[sumaoyi]

真是强， 没表也能做 HOHO

[li_wenbiao]

SELECT a.*
FROM employee a
INNER JOIN ( SELECT MAX(emp_wage) wage ,
dept_id deptid
FROM dbo.employee
GROUP BY dept_id
) b ON a.emp_wage = b.wage
AND a.dept_id = b.deptid