求這樣的一條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 行)
*/
回复
深渊的水影 2008-03-10
「送板機」 没有PI 啊。。为什么不 默認符合??
回复
相关推荐
发帖
MS-SQL Server
创建于2007-09-28

3.3w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
帖子事件
创建了帖子
2008-03-10 02:47
社区公告
暂无公告