求一优化SQL

[wxylvmnn]

一共涉及到了4个表。
简单说，就是用一个表取得的值，作为检索条件，去第2个表里再检索，依次类推。

WAREHOUSE
WAREHOUSE_LOCATION
WORK_CENTER
WIP_OPERATION

目标是，从WAREHOUSE中取出WarehouseType。

现在sql写成了这个奶奶样，咋能给优化一下。。。

select WarehouseType
from WAREHOUSE
where Facility = 'AA'
and Warehouse 
	= (select Warehouse
	from WAREHOUSE_LOCATION
	where ID 
	= (select PutAwayLocationID
		from WORK_CENTER
		where WorkCenter 
		= (select WorkCenter
			from WIP_OPERATION
			where WipOrderNo = 'BB'
			and WipOrderType = 'CC')))

[shoppo0505]

select WarehouseType from WAREHOUSE inner join WAREHOUSE_LOCATION on Warehouse.WAREHOUSE= WAREHOUSE_LOCATION.WAREHOUSE inner join WORK_CENTER ON WAREHOUSE_LOCATION.ID = WORK_CENTER.PutAwayLocationID inner join WIP_OPERATION ON WORK_CENTER.WorkCenter = WIP_OPERATION.WorkCenter AND WipOrderNo = 'BB' and WipOrderType = 'CC' where WAREHOUSE.Facility = 'AA'

[--小F--]

我只想知道一个事情，你这个如果WorkCenter ，ID这些字段得到的不是唯一值不报错吗？

[LongRui888]

建议用join来实现：

SELECT  WarehouseType
FROM    WAREHOUSE w
        INNER JOIN WAREHOUSE_LOCATION wl ON w.Warehouse = wl.Warehouse
        INNER JOIN WORK_CENTER wc ON wl.id = wc.PutAwayLocationID
        INNER JOIN WIP_OPERATION wo ON wc.WorkCenter = wo.WorkCenter
                                       AND wo.WipOrderNo = 'BB'
                                       AND wo.WipOrderType = 'CC'
WHERE   w.Facility = 'AA'

[LongRui888]

另外，可以考虑给 Facility，WarehouseType，Warehouse3字段加上联合索引，还有 WipOrderNo ，WipOrderType ，WorkCenter 3个字段的联合索引

[wxylvmnn]

引用 2 楼 fredrickhu 的回复:

我只想知道一个事情，你这个如果WorkCenter ，ID这些字段得到的不是唯一值不报错吗？

谢谢小F提醒， WIP_OPERATION的检索条件不是主键，其他的都是主键。加上个 TOP 1

select WarehouseType
from WAREHOUSE
where Facility = 'AA'
and Warehouse 
	= (select Warehouse
	from WAREHOUSE_LOCATION
	where ID 
	= (select PutAwayLocationID
		from WORK_CENTER
		where WorkCenter 
		= (select top 1 WorkCenter
			from WIP_OPERATION
			where WipOrderNo = 'BB'
			and WipOrderType = 'CC')))

[--小F--]

引用 5 楼 wxylvmnn 的回复:

[quote=引用 2 楼 fredrickhu 的回复:] 我只想知道一个事情，你这个如果WorkCenter ，ID这些字段得到的不是唯一值不报错吗？

谢谢小F提醒， WIP_OPERATION的检索条件不是主键，其他的都是主键。加上个 TOP 1

select WarehouseType
from WAREHOUSE
where Facility = 'AA'
and Warehouse 
	= (select Warehouse
	from WAREHOUSE_LOCATION
	where ID 
	= (select PutAwayLocationID
		from WORK_CENTER
		where WorkCenter 
		= (select top 1 WorkCenter
			from WIP_OPERATION
			where WipOrderNo = 'BB'
			and WipOrderType = 'CC')))

[/quote] 连子查询为连接查询 然后在连接字段加索引

[wxylvmnn]

引用 3 楼 yupeigu 的回复:

建议用join来实现：

SELECT  WarehouseType
FROM    WAREHOUSE w
        INNER JOIN WAREHOUSE_LOCATION wl ON w.Warehouse = wl.Warehouse
        INNER JOIN WORK_CENTER wc ON wl.id = wc.PutAwayLocationID
        INNER JOIN WIP_OPERATION wo ON wc.WorkCenter = wo.WorkCenter
                                       AND wo.WipOrderNo = 'BB'
                                       AND wo.WipOrderType = 'CC'
WHERE   w.Facility = 'AA'

WIP_OPERATION检索条件，是主键的一部分，取第一件的即可， SELECT WarehouseType改成 SELECT top 1 WarehouseType 是不是就OK了?