SELECT a.* FROM tb_ContractCategory a INNER JOIN ( SELECT ProcessNumber FROM tb_Contract a INNER JOIN ( SELECT ContractCode FROM tb_Contract WHERE ProcessNumber = 'NewContract000030' ) b ON a.ContractCode = b.ContractCode WHERE OperateType = 0 ) b ON a.ProcessNumber = b.ProcessNumber 看看数据是不是一样的,如果是的话,给我看看执行计划,ctrl+M,然后执行这个语句,上图
我用了一个例子测试了下select * from sales.SalesOrderDetail as a join Sales.SalesOrderHeader as b on a.SalesOrderID=b.SalesOrderID where a.SalesOrderID=43659select * from sales.SalesOrderDetail as a join Sales.SalesOrderHeader as b on a.SalesOrderID=b.SalesOrderID and a.SalesOrderID=43659select * from (select * from sales.SalesOrderDetail as a where a.SalesOrderID=43659) a join Sales.SalesOrderHeader as b on a.SalesOrderID=b.SalesOrderID效率完全一样。这个是必然还是偶尔?
select a.* from tb_ContractCategory a inner join (select ProcessNumber from tb_Contract where OperateType=0 ) b on a.ProcessNumber=b.ProcessNumber inner join (select ContractCode from tb_Contract where ProcessNumber='NewContract000030') c on a.ContractCode = c.ContractCode
CREATE INDEX ix_tb_Contract_1 ON tb_Contract(ProcessNumber,ContractCode)INCLUDE(OperateType) CREATE INDEX ix_tb_ContractCategory_1 ON tb_ContractCategory(ProcessNumber) 先加两个索引
FROM tb_ContractCategory a
INNER JOIN ( SELECT ProcessNumber
FROM tb_Contract a
INNER JOIN ( SELECT ContractCode
FROM tb_Contract
WHERE ProcessNumber = 'NewContract000030'
) b ON a.ContractCode = b.ContractCode
WHERE OperateType = 0
) b ON a.ProcessNumber = b.ProcessNumber
看看数据是不是一样的,如果是的话,给我看看执行计划,ctrl+M,然后执行这个语句,上图
像你这样的语法。把 这个条件放在 WHERE ProcessNumber = 'NewContract000030'你这里
和放在ON 后面 或者放在第二个WHERE 后面 这个对效率有影响吗?(对于inter join 来说)
像你这样的语法。把 这个条件放在 WHERE ProcessNumber = 'NewContract000030'你这里
和放在ON 后面 或者放在第二个WHERE 后面 这个对效率有影响吗?(对于inter join 来说)不好说,根据统计信息、索引等等情况才有意义
我用了一个例子测试了下select * from sales.SalesOrderDetail as a
join Sales.SalesOrderHeader as b
on a.SalesOrderID=b.SalesOrderID
where a.SalesOrderID=43659select * from sales.SalesOrderDetail as a
join Sales.SalesOrderHeader as b
on a.SalesOrderID=b.SalesOrderID
and a.SalesOrderID=43659select * from (select * from sales.SalesOrderDetail as a where a.SalesOrderID=43659) a
join Sales.SalesOrderHeader as b
on a.SalesOrderID=b.SalesOrderID效率完全一样。这个是必然还是偶尔?
像你这样的语法。把 这个条件放在 WHERE ProcessNumber = 'NewContract000030'你这里
和放在ON 后面 或者放在第二个WHERE 后面 这个对效率有影响吗?(对于inter join 来说)不好说,根据统计信息、索引等等情况才有意义以下是执行计划:
select a.* from tb_ContractCategory a
inner join (select ProcessNumber from tb_Contract where OperateType=0 ) b
on a.ProcessNumber=b.ProcessNumber
inner join (select ContractCode from tb_Contract where ProcessNumber='NewContract000030') c
on a.ContractCode = c.ContractCode
是的,因为我一张表里相同合同号的有2条记录,一条是原始的,一条是更新的,我现在只有更新的这条记录的ID,我需要先通过ID从更新的这条记录里获取合同号,然后在获取原始的那条记录的信息
CREATE INDEX ix_tb_ContractCategory_1 ON tb_ContractCategory(ProcessNumber)
先加两个索引