Question

怎么给这个SQL语句进行下优化，能让他运行速度更快。。在线等

SELECT a.POSID ,
a.StoreID ,
[FinalStatus] ,
a.[BranchID] ,
a.CreatedDate ,
c.DeletedDate
FROM [POSMacStatus] a
INNER JOIN ( SELECT POSID ,
StoreID ,
MAX(ts) ts ,
[BranchID]
FROM POSMacStatus
GROUP BY [BranchID],
POSID ,
StoreID
) b ON a.POSID = b.POSID
AND a.StoreID = b.StoreID
AND a.ts = b.ts
AND a.BranchID = b.BranchID
INNER JOIN dbo.PlazaStoreInfo c ON b.BranchID = c.BranchID
AND b.StoreID = c.StoreID
c.DeletedDate怎么报错了呢。。。

Answer 1

抱歉，没仔细看，试试这个：
SELECT a.POSID ,
a.StoreID ,
[FinalStatus] ,
a.[BranchID] ,
a.CreatedDate ,
b.DeletedDate
FROM [POSMacStatus] a
INNER JOIN ( SELECT bb.POSID ,
bb.StoreID ,
MAX(bb.ts) ts ,
bb.[BranchID],
cc.DeletedDate
FROM POSMacStatus bb
INNER JOIN dbo.PlazaStoreInfo cc
ON bb.[BranchID] = cc.[BranchID]
AND bb.StoreID = cc.StoreID
GROUP BY bb.[BranchID],
bb.POSID ,
bb.StoreID,
cc.DeletedDate
) b
ON a.POSID = b.POSID
AND a.StoreID = b.StoreID
AND a.ts = b.ts
AND a.BranchID = b.BranchID

Answer 2

SELECT a.POSID ,
a.StoreID ,
[FinalStatus] ,
a.[BranchID] ,
a.CreatedDate ,
c.DeletedDate
FROM [POSMacStatus] a
INNER JOIN ( SELECT bb.POSID ,
bb.StoreID ,
MAX(bb.ts) ts ,
bb.[BranchID]
FROM POSMacStatus bb
INNER JOIN dbo.PlazaStoreInfo cc
ON bb.[BranchID] = cc.[BranchID]
AND bb.StoreID = cc.StoreID
GROUP BY bb.[BranchID],
bb.POSID ,
bb.StoreID
) b
ON a.POSID = b.POSID
AND a.StoreID = b.StoreID
AND a.ts = b.ts
AND a.BranchID = b.BranchID