34,837
社区成员




create table tb(Machine varchar(10) , Item varchar(10) , smallValue int , bigValue int)
insert into tb values('送板機' , '長' ,50 ,500)
insert into tb values('送板機' , '寬' ,50 ,400)
insert into tb values('印刷機' , '長' ,50 ,500)
insert into tb values('Reflow' , '長' ,50 ,800)
insert into tb values('Reflow' , '寬' ,50 ,500)
insert into tb values('PCBPI' , 'PI' , 0 , 25)
create table tw(item varchar(10),v int)
insert into tw select '長',400
union select '寬',450
union select 'PI',20
--如果問長=400和寬=450和PI=20的machine?
go
select distinct machine from tb a where not exists
(select 1 from tb b where a.machine = machine and exists
(select 1 from tw where b.item = item and v not between a.smallvalue and bigvalue))
/*
machine
----------
PCBPI
Reflow
印刷機
*/
drop table tb,tw
create table tb(Machine varchar(10) , Item varchar(10) , smallValue int , bigValue int)
insert into tb values('送板機' , '長' ,50 ,500)
insert into tb values('送板機' , '寬' ,50 ,400)
insert into tb values('印刷機' , '長' ,50 ,500)
insert into tb values('Reflow' , '長' ,50 ,800)
insert into tb values('Reflow' , '寬' ,50 ,500)
insert into tb values('PCBPI' , 'PI' , 0 , 25)
go
--如果問長=400和寬=450和PI=20的machine?那麼得到:
select distinct machine from
(
select machine from
(
select distinct machine from tb where Item = '長' and smallValue <= 400 and bigValue >= 400
union all
select distinct machine from tb where Item = '寬' and smallValue <= 450 and bigValue >= 450
) t
group by machine having count(*) = 2
union all
select distinct machine from tb where machine not in (select distinct machine from tb where Item = '寬')
union all
select distinct machine from tb where Item = 'PI'
) t
/*
machine
----------
PCBPI
Reflow
印刷機
*/
drop table tb
--上面还漏了个条件Item = '長'
create table tb(Machine varchar(10) , Item varchar(10) , smallValue int , bigValue int)
insert into tb values('送板機' , '長' ,50 ,500)
insert into tb values('送板機' , '寬' ,50 ,400)
insert into tb values('印刷機' , '長' ,50 ,500)
insert into tb values('Reflow' , '長' ,50 ,800)
insert into tb values('Reflow' , '寬' ,50 ,500)
insert into tb values('PCBPI' , 'PI' , 0 , 25)
go
--如果問長=60的machine?那麼得到:
select distinct machine from tb where Item = '長' and smallValue <= 60 and bigValue >= 60
union all
select distinct machine from tb where machine not in (select distinct machine from tb where Item = '長')
/*
machine
----------
Reflow
送板機
印刷機
PCBPI
(所影响的行数为 4 行)
*/
drop table tb
create table tb(Machine varchar(10) , Item varchar(10) , smallValue int , bigValue int)
insert into tb values('送板機' , '長' ,50 ,500)
insert into tb values('送板機' , '寬' ,50 ,400)
insert into tb values('印刷機' , '長' ,50 ,500)
insert into tb values('Reflow' , '長' ,50 ,800)
insert into tb values('Reflow' , '寬' ,50 ,500)
insert into tb values('PCBPI' , 'PI' , 0 , 25)
go
--如果問長=60的machine?那麼得到:
select distinct machine from tb where smallValue <= 60 and bigValue >= 60
union all
select distinct machine from tb where machine not in (select distinct machine from tb where Item = '長')
/*
machine
----------
Reflow
送板機
印刷機
PCBPI
(所影响的行数为 4 行)
*/
drop table tb
create table tb(Machine varchar(10) , Item varchar(10) , smallValue int , bigValue int)
insert into tb values('送板機' , '長' ,50 ,500)
insert into tb values('送板機' , '寬' ,50 ,400)
insert into tb values('印刷機' , '長' ,50 ,500)
insert into tb values('Reflow' , '長' ,50 ,800)
insert into tb values('Reflow' , '寬' ,50 ,500)
insert into tb values('PCBPI' , 'PI' , 0 , 25)
go
--如果問長=60的machine?那麼得到:
select distinct machine from tb where smallValue <= 60 and bigValue >= 60
union all
select distinct machine from tb where machine not in (select distinct machine from tb where Item = '長')
drop table tb
/*
machine
----------
Reflow
送板機
印刷機
PCBPI
(所影响的行数为 4 行)
*/