订单表Orders
order_id
item_sku_id (FK 表item_sku)
orderCount商品表Order_item
order_item_id
itemCD
itemName颜色表item_color
item_color_id
order_item_id (FK 商品表Order_item)
colorName尺寸表item_size
item_size_id
order_item_id (FK 商品表Order_item)
sizeName表item_sku
item_sku_id
item_color_id (FK 颜色表item_color)
item_size_id (FK 尺寸表item_size)商品工艺关系表item_process
item_process_id
process_id (FK 工艺表process)
order_item_id (FK 商品表Order_item)工艺表process
process_id
processName生产线表process_line
process_line_id
prodess_id (FK 工艺表process)
processLineNo生产预订表item_process_schedule
item_process_schedule_id
item_process_id (FK 商品工艺关系表item_process)
process_line_id (FK 生产线表process_line)生产预订详细表process_schedule_detail
process_schedule_detail_id
item_process_schedule_id (FK 生产预订表item_process_schedule)
item_color_id (FK 颜色表item_color)
item_size_id (FK 尺寸表item_size)
count生产实际表item_process_result
item_process_result_id
item_process_id (FK 商品工艺关系表item_process)
process_line_id (FK 生产线表process_line)生产实际详细表process_result_detail
process_result_detail_id
item_process_result_id (FK 生产实际表item_process_result)
item_color_id (FK 颜色表item_color)
item_size_id (FK 尺寸表item_size)
ACount
BCount
order_id
item_sku_id (FK 表item_sku)
orderCount商品表Order_item
order_item_id
itemCD
itemName颜色表item_color
item_color_id
order_item_id (FK 商品表Order_item)
colorName尺寸表item_size
item_size_id
order_item_id (FK 商品表Order_item)
sizeName表item_sku
item_sku_id
item_color_id (FK 颜色表item_color)
item_size_id (FK 尺寸表item_size)商品工艺关系表item_process
item_process_id
process_id (FK 工艺表process)
order_item_id (FK 商品表Order_item)工艺表process
process_id
processName生产线表process_line
process_line_id
prodess_id (FK 工艺表process)
processLineNo生产预订表item_process_schedule
item_process_schedule_id
item_process_id (FK 商品工艺关系表item_process)
process_line_id (FK 生产线表process_line)生产预订详细表process_schedule_detail
process_schedule_detail_id
item_process_schedule_id (FK 生产预订表item_process_schedule)
item_color_id (FK 颜色表item_color)
item_size_id (FK 尺寸表item_size)
count生产实际表item_process_result
item_process_result_id
item_process_id (FK 商品工艺关系表item_process)
process_line_id (FK 生产线表process_line)生产实际详细表process_result_detail
process_result_detail_id
item_process_result_id (FK 生产实际表item_process_result)
item_color_id (FK 颜色表item_color)
item_size_id (FK 尺寸表item_size)
ACount
BCount
(select Item=isnull(a.Item, b.Item), Process_ID=isnull(a.Process_ID, b.Process_ID),
ProcessName=isnull(a.ProcessName, b.ProcessName), ProcessLineNo=isnull(a.ProcessLineNo, b.ProcessLineNo),
ColorName=isnull(a.ColorName, b.ColorName),
Size=isnull(a.Sizename, b.SizeName), count=isnull(a.count,0),
ACount=isnull(b.ACount,0), BCount=isnull(b.BCount,0), OrderCount=isnull(a.OrderCount, b.OrderCount)
from (
select oi.Item, ps.Process_ID, ps.ProcessName, pl.ProcessLineNo, skuc.item_color_id, skus.item_size_id,
skuc.ColorName, skus.SizeName, count=sum(isnull(psd.count,0)) ,ods.OrderCount
from [order_item] oi
inner join item_color skuc on oi.order_item_id=skuc.order_item_id
inner join item_size skus on oi.order_item_id=skus.order_item_id
inner join item_process ip on oi.order_item_id=ip.order_item_id
inner join process ps on ip.process_id=ps.process_id
inner join process_line pl on ps.process_id=pl.process_id
inner join item_process_schedule ips on ip.item_process_id=ips.item_process_id and pl.process_line_id=ips.process_line_id
inner join process_schedule_detail psd on ips.item_process_schedule_id=psd.item_process_schedule_id
and skuc.item_color_id=psd.item_color_id and skus.item_size_id=psd.item_size_id
inner join item_sku sku on skuc.item_color_id=sku.item_color_id and skus.item_size_id=sku.item_size_id
left join orders ods on sku.item_sku_id=ods.item_sku_id
group by oi.Order_item_id, oi.Item, ps.Process_ID, ps.ProcessName ,pl.Process_line_id ,pl.ProcessLineNo ,
skuc.item_color_id, skus.item_size_id, skuc.ColorName,
skus.sizeName, ods.OrderCount
) a
full join
(
select oi.Item, ps.Process_ID, ps.ProcessName, pl.ProcessLineNo, skuc.item_color_id, skus.item_size_id,
skuc.ColorName, skus.SizeName,
ACount=sum(isnull(prd.ACount,0)), BCount=sum(isnull(prd.BCount,0)), ods.OrderCount
from [order_item] oi
inner join item_color skuc on oi.order_item_id=skuc.order_item_id
inner join item_size skus on oi.order_item_id=skus.order_item_id
inner join item_process ip on oi.order_item_id=ip.order_item_id
inner join process ps on ip.process_id=ps.process_id
inner join process_line pl on ps.process_id=pl.process_id
inner join item_process_result ipr on ip.item_process_id=ipr.item_process_id and pl.process_line_id=ipr.process_line_id
inner join process_result_detail prd on ipr.item_process_result_id=prd.item_process_result_id
and skuc.item_color_id=prd.item_color_id and skus.item_size_id=prd.item_size_id
inner join item_sku sku on skuc.item_color_id=sku.item_color_id and skus.item_size_id=sku.item_size_id
left join orders ods on sku.item_sku_id=ods.item_sku_id
group by oi.Order_item_id, oi.Item, ps.Process_ID, ps.ProcessName ,pl.Process_line_id ,pl.ProcessLineNo ,
skuc.item_color_id, skus.item_size_id, skuc.ColorName,
skus.SizeName, ods.OrderCount
) b on a.item=b.item and a.Process_id=b.Process_id and a.ProcessLineNo=b.ProcessLineNo
and a.item_color_id=b.item_color_id and a.item_size_id=b.item_size_id
) aa因为预订和实际生产可能对不起来,所以小弟用了full jion ,但是sql语句很复杂速度很慢。
有哪位高手能够帮小弟化繁为简。
有哪位高手能够帮小弟化繁为简。 -------------------------------------------------------------
看你的语句这么复杂,估计很难从里面总结出业务逻辑,
你最好是给出业务逻辑的描述,对照你的语句应该更容易一些。
难道只能Full jOIn 了吗?