我在MYSQL中使用子查询,子查询的结果返回的是多列
select * from test t where manid != id and t.salary > (select t1.salary from test t1 where t1.id = manid);
这个地方我需要使用>(大于)符号,但是报错提示我结果返回多列
select * from test t where manid != id and t.salary in (select t1.salary from test t1 where t1.id = manid);
改成in就没问题,但是我需要使用>来做判断啊
谢谢回答~!
select * from test t where manid != id and t.salary > (select t1.salary from test t1 where t1.id = manid);
这个地方我需要使用>(大于)符号,但是报错提示我结果返回多列
select * from test t where manid != id and t.salary in (select t1.salary from test t1 where t1.id = manid);
改成in就没问题,但是我需要使用>来做判断啊
谢谢回答~!
select * from test t
where manid != id and t.salary > (select max(t1.salary) salary from test t1 where t1.id = manid);
select * from test t
where manid != id and t.salary > all(select t1.salary salary from test t1 where t1.id = manid);
可以改成select * from test t where manid != id and t.salary in (select top 1 t1.salary from test t1 where t1.id = manid);
这样不会错,但可能不是理想的结果.
select t1.salary from test t1 where t1.id = manid
ID Salary ManID
100 6500 100
200 5500 200
101 6600 100
102 6200 100
103 5100 100
104 6700 100
201 5800 200
202 4200 200
203 3500 200
204 2100 200ManID和ID相等代表部门经理查出:薪水不少于所属部门的部门经理薪水的普通员工
我希望一条SQL能够取出来,有好的办法解决吗? 谢谢大家
create table test
(
ID varchar(50),
Salary int,
ManID varchar(50)
)insert into test
select '100' , 6500 ,'100' union all
select '200' , 5500 , '200' union all
select '101' ,6600 , '100' union all
select '102' , 6200 , '100' union all
select '103' , 5100 , '100' union all
select '104' , 6700 , '100' union all
select '201' , 5800 , '200' union all
select '202' , 4200 , '200' union all
select '203' ,3500 , '200' union all
select '204' , 2100 , '200'
select *
from test t
where manid != id and t.salary > (select max(t1.salary) salary from test t1 where left(t.id,1) = left(t1.manid,1) and t1.manid=t1.id);
drop table test
结果:ID Salary ManID
-----------------------
101 6600 100
104 6700 100
201 5800 200
先建一视图,保留所有经理的记录;名: v_tman
sql: select * from t where id=manid;然后用下面的sql就可以得到楼主的结果了.
select * from inner join v_tman v on t.manid=v.manid and t.salary>v.salary;
请问left(t.id,1) = left(t1.manid,1) 这是什么意思,第2个参数1又代表什么?
create table test2(id int,salary int,manid int)
go
insert into test2 select 100,6500,100
union all select 200,5500,200
union all select 101,6600,100
union all select 102,6200,100
union all select 103,5100,100
union all select 104,6700,100
union all select 201,5800,200
union all select 202,4200,200
union all select 203,3500,200
union all select 204,2100,200
go
select t.id,t.salary ,t.manid from test2 t where t.salary>(select salary from test2 where test2.id=t.manid)
go
drop table test2
if object_id('test') is not null
drop table test
create table test(
ID varchar(50),
Salary int,
ManID varchar(50))insert into test
select '100' , 6500 ,'100' union all
select '200' , 5500 , '200' union all
select '101' ,6600 , '100' union all
select '102' , 6200 , '100' union all
select '103' , 5100 , '100' union all
select '104' , 6700 , '100' union all
select '201' , 5800 , '200' union all
select '202' , 4200 , '200' union all
select '203' ,3500 , '200' union all
select '204' , 2100 , '200'select a.* from test a ,(
select * from test where id=manid
) b where a.Manid=b.manid and a.id<>b.id and a.salary>=b.salary/*
ID Salary ManID
-------------------------------------------------- ----------- --------------------------------------------------
101 6600 100
104 6700 100
201 5800 200(3 行受影响)
*/