求這樣的一條sql語句？！

molan68 2008-03-10 02:47:47

原表:
Machine Item smallValue bigValue
送板機長 50 500
送板機寬 50 400
印刷機長 50 500
Reflow 長 50 800
Reflow 寬 50 500
PCBPI PI 0 25

可見machine中的Item有相同與不同項，並都有他們的最小值(smallValue)與最大值(bigValue)。
如果問長=60的machine？那麼得到：

送板機
印刷機
PCBPI
Reflow

（因為前三種machine的Item-長都滿足60在smallValue與bigValue之間,PCBPI沒有長,默認符合）

如果問長=400和寬=450和PI=20的machine？那麼得到：

印刷機
PCBPI
Reflow

因為「送板機」寬不在50-400之間，而「印刷機」沒有寬,默認符合，「PCBPI」沒有長寬，默認符合，「Reflow」符合。

求這樣的一條sql語句？！

...全文

102 点赞收藏 7

写回复

7 条回复

切换为时间正序

当前发帖距今超过3年，不再开放新的回复

发表回复

JJ_Net 2008-03-10

create table t(Machine varchar(20), Item varchar(20),smallValue int,bigValue int)
insert into t
select '送板機','長',50,500 union
select '送板機','寬',50,400 union
select '印刷機','長',50,500 union
select 'Reflow','長',50,800 union
select 'Reflow','寬',50,500 union
select 'PCBPI','PI',0,25
--如果問長=60的machine
select distinct machine from t where item = '長' and smallValue < 60 and bigvalue > 60 and machine in (select machine from t where item = '長' )
union
select distinct machine from t where machine not in (select machine from t where item = '長' )

--如果問長=400和寬=450和PI=20的machine
同样的方法，嵌套2层，有点麻烦

回复赞

JiangHongTao 2008-03-10

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

回复赞

dawugui 2008-03-10

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

回复赞

dawugui 2008-03-10



--上面还漏了个条件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

回复赞

dawugui 2008-03-10

--怎么只贴了一部分?

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

回复赞

dawugui 2008-03-10

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 行）

*/