求一sql语句

[purexiafeng]

表结构如下:

create table salary
(
empid int,
empname varchar(50),
changtime datetime,
salary money
)

insert into salary values(2,'ela','2007-08-20',100)
insert into salary values(1,'sam','2007-06-20',50)
insert into salary values(2,'ela','2007-05-20',70)
insert into salary values(3,'richer','2007-08-21',300)
insert into salary values(4,'bacom','2007-06-20',400)
insert into salary values(2,'sam','2007-07-20',200)

要求:用一句sql查询出所有员工最新时间的工资

谢谢帮忙!

[fzcheng]

create table salary 
( 
  empid    int, 
  empname  varchar(50), 
  changtime datetime, 
  salary    money 
) 

insert into salary values(2,'ela','2007-08-20',100) 
insert into salary values(1,'sam','2007-06-20',50) 
insert into salary values(2,'ela','2007-05-20',70) 
insert into salary values(3,'richer','2007-08-21',300) 
insert into salary values(4,'bacom','2007-06-20',400) 
insert into salary values(2,'sam','2007-07-20',200) 

--要求:用一句sql查询出所有员工最新时间的工资 

SELECT * FROM salary a WHERE NOT EXISTS
        (SELECT * FROM salary WHERE empid=a.empid AND changtime>a.changtime)

[hyde100]

create table #salary 
( 
  empid    int, 
  empname  varchar(50), 
  changtime datetime, 
  salary    money 
) 

insert into #salary values(2,'ela','2007-08-20',100) 
insert into #salary values(1,'sam','2007-06-20',50) 
insert into #salary values(2,'ela','2007-05-20',70) 
insert into #salary values(3,'richer','2007-08-21',300) 
insert into #salary values(4,'bacom','2007-06-20',400) 
insert into #salary values(2,'sam','2007-07-20',200) 

select 
	*
from #salary as t1
where not exists
(
	select 1 
	from #salary as t2 
	where t2.empname= t1.empname and t1.changtime < t2.changtime 
)

drop table #salary 
结果：
2	ela	2007-08-20 00:00:00.000	100.00
3	richer	2007-08-21 00:00:00.000	300.00
4	bacom	2007-06-20 00:00:00.000	400.00
2	sam	2007-07-20 00:00:00.000	200.00

[CN_SQL]

select
  a.*
from salary a
where not exists(
	select *
	from salary
	where empname = a.empname
		and changtime > a.changtime)

[Garnett_KG]

SELECT a.*
FROM   salary a,
       (
       SELECT empid,MAX(changtime) AS MAXTime
       FROM salary
       GROUP BY empid
       )
WHERE  a.empid=b.empid
       AND a.changtime=b.MAXTime

[hsie168518]

两个人用一个empid ？

[bill024]

select * from salary a where not exists
(select 1 from salary where changtime>a.changtime and empname=a.empname)

[CN_SQL]

select
  a.*
from salary a
where not exists(
	select *
	from salary
	where empname = a.empname
		and changtime > a.changtime)

[flyidealism]

select empid,max(changtime) as changtime,max(salary) as salary,
from salary
group by empid

[水族杰纶]

[Quote=引用 6 楼 hsie168518 的回复:]

两个人用一个empid    ？
[/Quote]
--果然
create table salary 
( 
  empid    int, 
  empname  varchar(50), 
  changtime datetime, 
  salary    money 
) 

insert into salary values(2,'ela','2007-08-20',100) 
insert into salary values(1,'sam','2007-06-20',50) 
insert into salary values(2,'ela','2007-05-20',70) 
insert into salary values(3,'richer','2007-08-21',300) 
insert into salary values(4,'bacom','2007-06-20',400) 
insert into salary values(2,'sam','2007-07-20',200) 
select * from  salary  s where  not exists (select 1 from salary where  empname=s. empname and changtime>s.changtime)
drop table  salary 
/*
empid       empname                                            changtime                                              salary                
----------- -------------------------------------------------- ------------------------------------------------------ --------------------- 
2           ela                                                2007-08-20 00:00:00.000                                100.0000
3           richer                                             2007-08-21 00:00:00.000                                300.0000
4           bacom                                              2007-06-20 00:00:00.000                                400.0000
2           sam                                                2007-07-20 00:00:00.000                                200.0000
*/

[等不到来世]

select a.empname,a.salary from salary a
join (select empname,changtime=max(changtime) from salary group by empname) b
on a.empname=b.empname and a.changtime=b.changtime

[生活真美好]

select empname, max(changtime) as maxtime, (select salary from salary where changtime=max(ab.changtime) and empname = ab.empname) as newsalary from salary as ab group by empname

[生活真美好]

empname maxtime newsalary
-------------------------------------------------- ------------------------------------------------------ ---------------------
bacom 2007-06-20 00:00:00.000 400.0000
ela 2007-08-20 00:00:00.000 100.0000
richer 2007-08-21 00:00:00.000 300.0000
sam 2007-07-20 00:00:00.000 200.0000

（所影响的行数为 4 行）

[flyidealism]

select s.empid,empname,salary
from 
salary s inner join 
(
select empid,max(changtime) as changtime
from salary
group by empid
) a
on s.changtime=a.changtime and s.empid=a.empid

[wer123q]

 select * from salary aa where not exists(select * from salary where changtime>aa.changtime and 
  empid = aa.empid  )

[liujuns]

select a.*
from salary a
join
(Select empid, MAX(changtime) as maxchangtime from salary group by empid ) b on
a.changtime = b.maxchangtime and a.empid =b.empid

[时光瞄]

[Quote=引用 6 楼 hsie168518 的回复:]

两个人用一个empid    ？
[/Quote]
同问..

[tianyazifan]

create table salary 
( 
  empid    int, 
  empname  varchar(50), 
  changtime datetime, 
  salary    money 
) 

insert into salary values(2,'ela','2007-08-20',100) 
insert into salary values(1,'sam','2007-06-20',50) 
insert into salary values(2,'ela','2007-05-20',70) 
insert into salary values(3,'richer','2007-08-21',300) 
insert into salary values(4,'bacom','2007-06-20',400) 
insert into salary values(2,'sam','2007-07-20',200) 

select * from salary where changtime in
(
select max(changtime) from salary
	group by empname
)
drop table salary

结果：
2 ela 2007-08-20 00:00:00.000 100.00
1 sam 2007-06-20 00:00:00.000 50.00
3 richer 2007-08-21 00:00:00.000 300.00
4 bacom 2007-06-20 00:00:00.000 400.00
2 sam 2007-07-20 00:00:00.000 200.00

[tianyazifan]

create table salary 
( 
  empid    int, 
  empname  varchar(50), 
  changtime datetime, 
  salary    money 
) 

insert into salary values(2,'ela','2007-08-20',100) 
insert into salary values(1,'sam','2007-06-20',50) 
insert into salary values(2,'ela','2007-05-20',70) 
insert into salary values(3,'richer','2007-08-21',300) 
insert into salary values(4,'bacom','2007-06-20',400) 
insert into salary values(2,'sam','2007-07-20',200) 

select * from salary where changtime in
(
select max(changtime) from salary
	group by empname
)
drop table salary

结果：
2 ela 2007-08-20 00:00:00.000 100.00
1 sam 2007-06-20 00:00:00.000 50.00
3 richer 2007-08-21 00:00:00.000 300.00
4 bacom 2007-06-20 00:00:00.000 400.00
2 sam 2007-07-20 00:00:00.000 200.00

[denghz1982]

select empid,empname,changtime,salary from (select empid,empname,changtime,salary,max(changtime) over (partition by empname) as newtime from salary) a where a.changtime=a.newtime

[denghz1982]

create table salary 
( 
  empid    int, 
  empname  varchar(50), 
  changtime datetime, 
  salary    money 
) 

insert into salary values(2,'ela','2007-08-20',100) 
insert into salary values(1,'sam','2007-06-20',50) 
insert into salary values(2,'ela','2007-05-20',70) 
insert into salary values(3,'richer','2007-08-21',300) 
insert into salary values(4,'bacom','2007-06-20',400) 
insert into salary values(2,'sam','2007-07-20',200) 

select empid,empname,changtime,salary from
 (select empid,empname,changtime,salary,
   max(changtime) over (partition by empname) as newtime from salary) a 
       where a.changtime=a.newtime