不知這樣的要求，怎樣寫SQL比較簡練，比較有效率呢？

[hycheng163]

/*
DROP TABLE #MFGBasic
DROP TABLE #MFGWork
DROP TABLE #RoutingBasic
DROP TABLE #RoutingDetail
*/
Create table #MFGBasic(MFGBasicid int identity(1,1),MFGCode varchar(10))
insert into #MFGBasic select 'AAA'
insert into #MFGBasic select 'BBB'

Create table #MFGWork(MFGWorkid int identity(1,1),MFGBasicid int,WorkNO varchar(10))
insert into #MFGWork select 1,'A01'
insert into #MFGWork select 1,'A02'
insert into #MFGWork select 1,'A03'
insert into #MFGWork select 1,'A04'

insert into #MFGWork select 2,'B01'
insert into #MFGWork select 2,'B02'
insert into #MFGWork select 2,'B03'

Create table #RoutingBasic(RoutingBasicid int identity(1,1),RoutingBasicNo varchar(10),MFGCode varchar(10))
insert into #RoutingBasic select 'RA1','AAA'
insert into #RoutingBasic select 'RA2','AAA'
insert into #RoutingBasic select 'RA3','AAA'
insert into #RoutingBasic select 'RB1','BBB'
insert into #RoutingBasic select 'RB2','BBB'
insert into #RoutingBasic select 'RB3','BBB'

Create table #RoutingDetail(RoutingDetailid int identity(1,1),RoutingBasicid int,RoutingDetailNO varchar(10) ,WorkNO varchar(10))
insert into #RoutingDetail select 1,'DA1','A03'
insert into #RoutingDetail select 1,'DA1','A02'
insert into #RoutingDetail select 1,'DA1','A01'
insert into #RoutingDetail select 1,'DA1','A04'

insert into #RoutingDetail select 2,'DA2','A01'
insert into #RoutingDetail select 2,'DA2','A02'
insert into #RoutingDetail select 2,'DA2','A03'

insert into #RoutingDetail select 3,'DA1','A03'
insert into #RoutingDetail select 3,'DA1','A04'
insert into #RoutingDetail select 3,'DA1','A01'
insert into #RoutingDetail select 3,'DA1','A02'

insert into #RoutingDetail select 4,'DB1','B01'
insert into #RoutingDetail select 4,'DB1','B02'

insert into #RoutingDetail select 5,'DB2','B03'
insert into #RoutingDetail select 5,'DB2','B02'
insert into #RoutingDetail select 5,'DB2','B01'

insert into #RoutingDetail select 6,'DB3','B02'


1.#MFGBasic與#MFGWork是一對多的關系，以MFGBasicid字段關聯
2.#RoutingBasic與#RoutingDetail是一對多的關系，以RoutingBasicid字段關聯
3.#MFGBasic與#RoutingBasic是一對多的關系，以MFGCode字段關聯.


--第1個SQL：
select Count(*) from #MFGWork where MFGBasicid =(select MFGBasicid from #MFGBasic where MFGCode='AAA')
/*結果
4
*/
--第2個SQL：
select Count(*) from #RoutingDetail where RoutingBasicid =(select RoutingBasicid from #RoutingBasic where RoutingBasicNo='RA1')
/*結果
4
*/

如果第1個SQL的結果與第2個SQL的結果相等，就將RoutingBasicNo選出來。
最後的結果應該是：
/*
RoutingBasicNo
--------------
RA1
RA3
RB2
*/
簡化點說就是：
A箱里有1號，2號，3號，4號等四個球。
A箱里的球有多種排序，但不論那種排序，但必須是這四個球不能缺少一個。
上面#MFGBasic中的
AAA分別有4個WorkNo,分別是A01,A02,A03,A04
而在#RoutingBasic，
AAA分別有3種不同的排序，分別是RA1,RA2,RA3，但因為RA2少了一個WorkNo：A04，而RA1與RA3都有全部4個WorkNo，所以選了RA1,RA1出來

[Zoezs]

没看懂。

[肥龙上天]

很长！~

[hycheng163]

--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 RoutingBasicNo from #RoutingBasic t
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 行受影响)
*/

[hycheng163]

上面我應該是表述不清，修改了一下，見下面

AAA箱里有A01號，A02號，A03號，A04號等,共四個球。
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'