Question

sql语句，select not in

select top 30
[workerId],
[workerName],
t1.[departmentId],
[departmentName],
[telephone] from worker t1
left join department t2
on t1.departmentId = t2.departmentId
order by t1.[departmentId],[workerName]
where workerId not in (select top 10 workerId
from worker t1
left join department t2
on t1.departmentId = t2.departmentId
order by t1.[departmentId],[workerName])

服务器: 消息 156，级别 15，状态 1，行 10
在关键字 'where' 附近有语法错误。
服务器: 消息 156，级别 15，状态 1，行 14
在关键字 'order' 附近有语法错误。
应该怎么改呢？

Answer 1

NOT IN:通过 NOT IN关键字引入的子查询也返回一列零值或更多值

Persons 表:
Id LastName FirstName Address City
1 Adams John Oxford Street London
2 Bush George Fifth Avenue New York
3 Carter Thomas Changan Street Beijing

sql语句
SELECT * FROM Persons WHERE City NOT IN ('London','Beijing')

结果
Id LastName FirstName Address City
2 Bush George Fifth Avenue New York

Answer 2

select top 30
[workerId],
[workerName],
t1.[departmentId],
[departmentName],
[telephone] from worker t1
left join department t2
on t1.departmentId = t2.departmentId

where workerId not in (select top 10 workerId
from worker t1
left join department t2
on t1.departmentId = t2.departmentId
order by t1.[departmentId],[workerName])

order by t1.[departmentId],[workerName]

追问

我要把所有的员工按照，部门和姓名排序，分页显示，这种写法实现不了我的需求啊！

Answer 3

各自段加上所属表名，你代码中有的加了，有的没有加，所以混乱。
多表查询的，每个字段最好加上别名。

Answer 4

把 where之前的order by 放在语句最后。
还有not in 里面不需要order by了