求助查错。。。sql语句写的没错，居然得不到正确结果！

求财务部收入最高的员工姓名
方法一：
select name
from salary s1,employees,departments
where employees.employeeid=s1.employeeid
and employees.departmentid=departments.departmentid and departmentname='财务部' 
and not exists
(select * 
from salary s2
where s2.employeeid=s1.employeeid
and s2.income>s1.income)方法二：
create view max_income
as
select employees.employeeid,name,income
from salary s1,employees,departments
where employees.employeeid=s1.employeeid
and employees.departmentid=departments.departmentid and departmentname='财务部'
 
select name from max_income m1
where not exists
(select * from max_income m2
where m1.employeeid=m1.employeeid
and m2.income>m1.income)方法一的结果不正确，方法二的结果是正确的，但是二者其实就是一种思想演变的两种不同的写法而已呀，为什么第一种不行呢！
创建表的SQL语句如下USE YGGL
CREATE TABLE Employees
           (   EmployeeID char(6) primary key,
               Name char(10) NOT NULL,
               education char(4) not null,
               workyear tinyint null,
               Address varchar(50) NOT NULL,
               Zip Char(6) NULL,
               PhoneNumber char(12) NULL,  
               EmailAddress varchar(20) NULL,   
               DepartmentID char(3) NOT NULL,           
               Birthday datetime NOT NULL,
               Sex bit NOT NULL,
           )
           
create table departments
   ( departmentID char(3) primary key,
    departmentname varchar(30),
    Note text)
go
create table salary
    (
   employeeID char(6) primary key,
   inCome float,
   outCome float 
)
go
insert into employees values('000001','王林','大专',8,'中山路32-1-508','210003','3355668',null,'2','1956-1-23',1)
insert into employees values('010008','伍容华','本科',3,'北京东路100-2','210001','3321321',null,'1','1966-3-28',1)
insert into employees values('020010','王向荣','硕士',2,'四牌路10-10-108','210006','3792361',null,'1','1972-12-9',1)
insert into employees values('020018','李丽','大专',6,'中山东路102-2','210002','3416601','[email protected]','1','1950-7-30',0)
insert into employees values('102201','刘明','本科',3,'虎距路100-2','210013','3606608',null,'5','1962-10-18',1)
insert into employees values('102208','朱俊','硕士',2,'牌楼巷5-3-1806','210013','3606608','[email protected]','5','1962-10-18',1)
insert into employees values('108991','钟敏','硕士',4,'中山路108-3-105','210004','4808817','[email protected]','5','1955-09-28',0)
insert into employees values('111006','张石兵','本科',1,'解放路34-9-1-203','210010','4563418','[email protected]','5','1964-10-01',1)
insert into employees values('210678','林涛','大专',2,'中山北路247-2-303','210008','3467337',null,'3','1967-04-02',1)
insert into employees values('302566','李玉珉','本科',3,'热和路209-3','210018','8765991','[email protected]','4','1958-09-20',1)
insert into employees values('308759','叶凡','本科',2,'北京西路3-7-502','210001','3608901',null,'4','1968-11-18',1)
insert into employees values('504209','陈林琳','大专',5,'汉中路120-4-102','210002','4468158',null,'4','1959-09-03',0)insert into departments values('1','财务部',null)
insert into departments values('2','人力资源部',null)
insert into departments values('3','经理办公室',null)
insert into departments values('4','研发部',null)
insert into departments values('5','市场部',null)insert into salary values('000001',2100.8,123.09)
insert into salary values('010008',1582.62,88.03)
insert into salary values('102201',2569.88,185.65)
insert into salary values('111006',1987.01,79.58)
insert into salary values('504209',2066.15,108.0)
insert into salary values('302566',2980.7,210.2)
insert into salary values('108991',3259.98,281.52)
insert into salary values('020010',2860.00,198.0)
insert into salary values('020018',2347.68,180.0)
insert into salary values('308759',2351.98,199.08)
insert into salary values('210678',2240.00,121.0)
insert into salary values('102208',1980.00,100.0)

解决方案 »

  1.   

    select b.name
    from salary as  a,employees as b,departments as c
    where b.employeeid=a.employeeid
    and b.departmentid=c.departmentid and c.departmentname='财务部'  
    and not exists
    (select *  
    from salary s1 inner join employees as s2 on s2.employeeid=s1.employeeid
    where s2.departmentid=b.departmentid
    and s1.income>a.income)
      

  2.   

    本帖最后由 roy_88 于 2011-11-01 22:22:23 编辑
      

  3.   

    --这样即可.
    select top 1 e.* , d.* , s.* 
    from employees e, departments d, salary s
    where d.departmentname = '财务部' and e.employeeid = s.employeeid and e.departmentid = d.departmentid
    order by s.income desc/*
    EmployeeID Name       education workyear Address                                            Zip    PhoneNumber  EmailAddress         DepartmentID Birthday                                               Sex  departmentID departmentname                 Note                                                                                                                                                                                                                                                             employeeID inCome                                                outCome                                               
    ---------- ---------- --------- -------- -------------------------------------------------- ------ ------------ -------------------- ------------ ------------------------------------------------------ ---- ------------ ------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------- ----------------------------------------------------- ----------------------------------------------------- 
    020010     王向荣        硕士        2        四牌路10-10-108                                       210006 3792361      NULL                 1            1972-12-09 00:00:00.000                                1    1            财务部                            NULL                                                                                                                                                                                                                                                             020010     2860.0                                                198.0（所影响的行数为 1 行）
    */
      

  4.   


    感谢，总算明白了！个人觉得top 1不是很适合查询max类的问题