表结构是这样。
id EmpId SalayType SalayMoney1 1 类型1 300.00
2 1 类型2 100.00
3 1 类型3 300.00
4 2 类型1 300.00
5 2 类型2 100.00
6 2 类型3 10.00 select EmpId from EmpGongZiReport group by EmpId这里返回出1 2我想写个查询语句是根据条件返回出id
比如 类型1>200 并且类型3<20 返回出2 。如何写 谢谢。
id EmpId SalayType SalayMoney1 1 类型1 300.00
2 1 类型2 100.00
3 1 类型3 300.00
4 2 类型1 300.00
5 2 类型2 100.00
6 2 类型3 10.00 select EmpId from EmpGongZiReport group by EmpId这里返回出1 2我想写个查询语句是根据条件返回出id
比如 类型1>200 并且类型3<20 返回出2 。如何写 谢谢。
SELECT EmpID
FROM xx Where (SalyType='类型1'and SalayMonet>'200') or (SalyType='类型3'and SalayMonet<'20') Group by EmpId 不知道这样试合你不
要用or才对,and的话一个也没有
用并且就是用and,也就是说你的意思是查询出工资类型为类型1,SalayMoney>200并且工资类型为类型3,SalayMoney<20 的雇员:
select empid from EmpGongZiReport where (SalayType='类型1' and SalayMoney>200) and (SalayType='类型3' and SalayMoney <20) group by empid
where
(SalayType = '类型1' and SalayMoney >300)
or
(SalayType = '类型3' and SalayMoney <20)
group by empid
having count(*)>1
where empid in
(select empid from EmpGongZiReport a where (SalayType='类型1' and SalayMoney>200) group by empid)
and empid in
(select empid from EmpGongZiReport b where (SalayType='类型3' and SalayMoney <20) group by empid)
select EmpId from EmpGongZiReport where (SalayType='类型1' and SalayMoney>200 )and EmpId in
(select Empid from EmpGongZiReport where (SalayType='类型3' and SalayMoney<20))
create table #temp
(
id int identity
,EmpId int
,SalayType varchar(20)
,SalayMoney money
)
insert into #temp(EmpId,SalayType,SalayMoney)
values(1,'类型1',300)
insert into #temp(EmpId,SalayType,SalayMoney)
values(1,'类型2',100)
insert into #temp(EmpId,SalayType,SalayMoney)
values(1,'类型3',300)
insert into #temp(EmpId,SalayType,SalayMoney)
values(2,'类型1',300)
insert into #temp(EmpId,SalayType,SalayMoney)
values(2,'类型2',100)
insert into #temp(EmpId,SalayType,SalayMoney)
values(2,'类型3',10)
select EmpId from #temp where (SalayType='类型1' and SalayMoney>200 )and EmpId in
(select Empid from #temp where (SalayType='类型3' and SalayMoney<20))
from
(
select EmpId as EmpIdA
from EmpGongZiReport
where (SalayType='类型1' and SalayMoney > 200)
) as a,
(
select EmpId as EmpIdB
from EmpGongZiReport
where SalayType='类型3' and SalayMoney < 20
) as b
where EmpIdA=EmpIdB
where
(SalayType = '类型1' and SalayMoney >300)
and
(SalayType = '类型3' and SalayMoney <20)
group by empid
having count(*)>1
where
(SalayType = '类型1' and SalayMoney >300)
and
(SalayType = '类型3' and SalayMoney <20)
group by empid
(SalayType = '类型1' and SalayMoney >300)
and
(SalayType = '类型3' and SalayMoney <20) group by EmpId
from EmpGongZiReport
where (SalayType='类型1' and SalayMoney > 200) 再查询出符合条件而的记录集合B
select EmpId as EmpIdB
from EmpGongZiReport
where SalayType='类型3' and SalayMoney < 20 现在需要在两个集合中找出EmpId相等的值,如下select EmpIdA
from
(
select EmpId as EmpIdA
from EmpGongZiReport
where (SalayType='类型1' and SalayMoney > 200)
) as a,
(
select EmpId as EmpIdB
from EmpGongZiReport
where SalayType='类型3' and SalayMoney < 20
) as b
where EmpIdA=EmpIdB
可是 逻辑就只能有一个啊
条件也就只能有一个啊. 楼主明白了么?假设 如你所说 要一条sql 条件可能是 and 也可能是 or
那就是说 这条语句 产生的结果就是不定的.... 逻辑都是不定的...
这,,,,,,,不对吧.