34,576
社区成员
发帖
与我相关
我的任务
分享
m17.inv_part_number LIKE a.生产部件+'%'
LEFT(m17.inv_part_number, 12) = LEFT(a.生产部件, 12)
SELECT
a.生产部件,
a.客户名称,
a.订单类型,
a.下单时间,
b.套板尺寸X,
b.套板尺寸Y,
b.拼板尺寸X,
b.拼板尺寸Y,
b.大料尺寸长,
b.大料尺寸宽,
(SELECT TOP 1 SUBSTRING(d17.inv_part_description,1,CHARINDEX(' ',d17.inv_part_description)- 1)
FROM data0026 d26,data0025 d25, data0017 m17, data0017 d17
WHERE d26.parent_node_invent = d25.rkey AND d25.inventory_ptr = m17.rkey
AND d26.inventory_ptr = d17.rkey
AND d17.inv_part_number LIKE 'AA%'
AND LEFT(m17.inv_part_number, 12) = LEFT(a.生产部件, 12) COLLATE Chinese_PRC_BIN) AS 物料,
b.板料利用率,
b.拼板利用率
FROM GC_DATA001 a
LEFT JOIN PE_套板尺寸信息 b
ON a.生产部件 = b.型号 COLLATE Chinese_PRC_BIN
WHERE (下单时间 BETWEEN CONVERT(DATETIME, '2018-10-01', 102) AND CONVERT(DATETIME, '2018-10-08', 102))
试试这样呢 m17.inv_part_number LIKE LEFT(a.生产部件, 12)+'%'
--每个表添加一个可持久化计算列,并添加对应索引
--1.1
ALTER TABLE data0017 ADD inv_part_number_left12 AS LEFT(inv_part_number,12) PERSISTED
CREATE INDEX ix_data0017_inv_part_number_left12 ON data0017(inv_part_number_left12)
--1.2
ALTER TABLE GC_DATA001 ADD part_left12 AS LEFT([生产部件],12) PERSISTED
CREATE INDEX ix_GC_DATA001_part_left12 ON GC_DATA001(part_left12)
--如果下单时间没有索引,应该加一个:
CREATE INDEX ix_GC_DATA001_orderTime ON _GC_DATA001([下单时间])
--执行完上面的, 再执行查询
SELECT
a.生产部件,
a.客户名称,
a.订单类型,
a.下单时间,
b.套板尺寸X,
b.套板尺寸Y,
b.拼板尺寸X,
b.拼板尺寸Y,
b.大料尺寸长,
b.大料尺寸宽,
(SELECT TOP 1 SUBSTRING(d17.inv_part_description,1,CHARINDEX(' ',d17.inv_part_description)- 1)
FROM data0026 d26,data0025 d25, data0017 m17, data0017 d17
WHERE d26.parent_node_invent = d25.rkey AND d25.inventory_ptr = m17.rkey
AND d26.inventory_ptr = d17.rkey
AND d17.inv_part_number_left12 LIKE 'AA%' --反正左边 12 个字符逻辑也对得上,有索引的列效率更高
AND m17.inv_part_number_left12 = a.part_left12 COLLATE Chinese_PRC_BIN --这里改成了计算列直接连接
) AS 物料,
b.板料利用率,
b.拼板利用率
FROM GC_DATA001 a
LEFT JOIN PE_套板尺寸信息 b
ON a.生产部件 = b.型号 COLLATE Chinese_PRC_BIN
WHERE (下单时间 BETWEEN CONVERT(DATETIME, '2018-10-01', 102) AND CONVERT(DATETIME, '2018-10-08', 102))
m17.inv_part_number LIKE LEFT(a.生产部件, 12)+'%'