我有两个表:
SELECT [DealerdistributionId]
,[DealerCode]
,[BarCode]
,[BarCode2]
,[Model]
,[DONumber]
,[DistributionDate]
FROM [tblDealerDistributionDetails]
产生这些结果
和另一个表:
SELECT [ProductRegID]
,[ProductType]
,[ProductModelID]
,[SalerID]
,[ProductID]
,[PhoneNumber]
,[RegistrationDate]
FROM [tblProductRegistration]
结果
第二表tblProductRegistration
记录ProductID
从BarCode
或BarCode2
从第一个表tblDealerDistributionDetails
,但只有一个BarCode
或BarCode2
。
我想找到哪个经销商在tblProductRegistration中输入多少数量的模型。
输出:tblProductRegistration中的DelarCode,Model,TotalEntry。
我已经试过了
select DealerCode, COUNT(*) as [Activated]
from tblDealerDistributionDetails
where
( BarCode in (select ProductID from tblProductRegistration where RegistrationDate >='2016-03-01' and RegistrationDate <='2016-03-02')
or
BarCode2 in (select ProductID from tblProductRegistration where RegistrationDate >='2016-03-01' and RegistrationDate <='2016-03-02') )
and Model= 'Olvio L12' group by DealerCode
对于特定的型号'Olvio L12',但会导致性能问题。我需要更快的查询性能
具有更快的查询性能。请提供帮助。
CREATE NONCLUSTERED INDEX ix_p
ON tblProductRegistration (RegistrationDate, ProductID)
GO
CREATE NONCLUSTERED INDEX ix_d
ON tblDealerDistributionDetails (model, DealerCode) INCLUDE (BarCode, BarCode2)
GO
SELECT DealerCode, COUNT_BIG(*) AS [Activated]
FROM tblDealerDistributionDetails d
WHERE model = 'Olvio L12'
AND EXISTS(
SELECT *
FROM tblProductRegistration r
WHERE r.RegistrationDate BETWEEN '20160301' AND '20160302'
AND r.ProductID IN (d.BarCode, d.BarCode2)
)
GROUP BY DealerCode
--OPTION(RECOMPILE)
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句