--A箱
select * from #MFGBasic where MFGCode='AAA'--A箱有四個球:A01,A02,A03,A04 。共4個
select WorkNo from #MFGWork where MFGBasicid =(select MFGBasicid from #MFGBasic where MFGCode='AAA')--A箱有四個球有下面排列:RA1,RA2,RA3
select RoutingBasicNo from #RoutingBasic where MFGCode='AAA'--排列名RA1,具體A03,A02,A01,A04 。共4個
select WorkNO from #RoutingDetail where RoutingBasicid =(select RoutingBasicid from #RoutingBasic where RoutingBasicNo='RA1')--排列名RA2,具體A01,A02,A03。共有3個
select WorkNO from #RoutingDetail where RoutingBasicid =(select RoutingBasicid from #RoutingBasic where RoutingBasicNo='RA2')--排列名RA3,具體A03,A04,A01,A02 。共4個
select WorkNO from #RoutingDetail where RoutingBasicid =(select RoutingBasicid from #RoutingBasic where RoutingBasicNo='RA3')因排列名RA2,只有3個,不足四個球,所以排除RA2,只取出 RA1,RA3
select * from #MFGBasic where MFGCode='AAA'--A箱有四個球:A01,A02,A03,A04 。共4個
select WorkNo from #MFGWork where MFGBasicid =(select MFGBasicid from #MFGBasic where MFGCode='AAA')--A箱有四個球有下面排列:RA1,RA2,RA3
select RoutingBasicNo from #RoutingBasic where MFGCode='AAA'--排列名RA1,具體A03,A02,A01,A04 。共4個
select WorkNO from #RoutingDetail where RoutingBasicid =(select RoutingBasicid from #RoutingBasic where RoutingBasicNo='RA1')--排列名RA2,具體A01,A02,A03。共有3個
select WorkNO from #RoutingDetail where RoutingBasicid =(select RoutingBasicid from #RoutingBasic where RoutingBasicNo='RA2')--排列名RA3,具體A03,A04,A01,A02 。共4個
select WorkNO from #RoutingDetail where RoutingBasicid =(select RoutingBasicid from #RoutingBasic where RoutingBasicNo='RA3')因排列名RA2,只有3個,不足四個球,所以排除RA2,只取出 RA1,RA3
解决方案 »
- SQL SERVER2000利用日志文件恢复数据库
- SQL语句请教(急)
- ☆☆☆网上流传的SQL MD5 算法对中文字符串不能产生正确的 MD5 码(修改方法)☆☆☆
- 安装sql server连接的问题
- 求一条SQL语句.在线解决立刻给分..
- 如何选择出一条记录有多少个数据(这条记录的非空数据的列数)?
- 数据插入远程服务器的表中 ,这个表里面有自动增加的字段 `~
- VS2005安装时同时选择安装了SQL Express版,请问Express版能用做服务器吗,开发版行不行呢?
- SQL求救 (在线等待)
- sqlserver 2008 R2 安装报错:Could not open key:UNKNOWN...
- sql去掉重复记录的查询,急啊!
- 在MYSQL数据库中创建存储过程出错,应该是什么格式?
where (select count(*) from #MFGBasic a join #MFGWork b on a.MFGBasicid=b.MFGBasicid where a.MFGCode=t.MFGCode)
=(select count(*) from #RoutingDetail where RoutingBasicid=t.RoutingBasicid)
/*
RoutingBasicNo
--------------
RA1
RA3
RB2(3 行受影响)
*/
AAA箱里面的四個球,分別有三種方式的排序,它們的名稱分別是RA1,RA2,RA3排序名:RA1
A04,A02,A01,A03
排序名:RA2
A01,A03,A04
排序名:RA3
A04,A01,A02,A03現要求如下:
不管那種排序方式,AAA箱里面的四個球,都必須存在排列當中,
上面的排序名:RA2 因為A02沒有出現在排序,當中所以被過濾掉
而排序名:RA1、RA3這兩種方式的四個球都排列了。
所以結果:
----
RA1
RA3
------盒table
Create table #Box (Boxid int ,BoxName varchar(10))
insert into #Box select 1,'AAA'--球table
Create table #Ball(Ballid int,Boxid int,BallName varchar(10))
insert into #Ball select 1,1,'A01'
insert into #Ball select 2,1,'A02'
insert into #Ball select 3,1,'A03'
insert into #Ball select 4,1,'A04'--排列名頭表
Create table #RangBasic(RangBasicId int,RangName varchar(10),BoxName varchar(10))
insert into #RangBasic select 1,'RA1','AAA'
insert into #RangBasic select 2,'RA2','AAA'
insert into #RangBasic select 3,'RA3','AAA'--排列明細表
Create table #RangDetail(RangDetailid int,RangBasicId int,BallName varchar(10))
insert into #RangDetail select 1,1,'A04'
insert into #RangDetail select 2,1,'A02'
insert into #RangDetail select 3,1,'A01'
insert into #RangDetail select 4,1,'A03'insert into #RangDetail select 5,2,'A01'
insert into #RangDetail select 6,2,'A03'
insert into #RangDetail select 7,2,'A04'
insert into #RangDetail select 8,3,'A04'
insert into #RangDetail select 9,3,'A01'
insert into #RangDetail select 10,3,'A02'
insert into #RangDetail select 11,3,'A03'