你好，有人能帮我在Joins而不是Sub查询中写这个查询吗

Question

SELECT ST.Id,
ST.Label,
ST.Asset,
CASE
WHEN ST.RFID = '' THEN 'NULL'
ELSE ST.RFID
END AS RFID,
CASE
WHEN ST.Type = 'O' THEN 'Odometer'
ELSE 'Engine Hours'
END AS AccumulatorType,
ST.AppId,
ST.Timestamp,
ST.Accumulator,
CASE
WHEN ST.Flag = 0 THEN 'Disabled'
WHEN ST.Flag = 1 THEN 'Active'
WHEN ST.Flag = 3 THEN 'Rented'
WHEN ST.Flag = 4 THEN 'Bypass'
WHEN ST.Flag = 5 THEN 'Tanker'
END AS TYPE,
(SELECT COUNT(*)
FROM sync
WHERE RowId = ST.Id
AND DefinitionId = 1
AND Status = 1) AS Updated,
(SELECT COUNT(*)
FROM sync
WHERE RowId = ST.Id
AND DefinitionId = 1
AND Status = 0) AS Remaining
FROM SecondaryTags AS ST
WHERE AppId = @AppId

Answer 1

为什么要重写逻辑？因为您在外部查询中进行了筛选，所以子查询可能是计算性能最高的方法。

您可以通过确保拥有写入索引来加快子查询的速度。在这种情况下，您需要sync(RowId, DefinitionId, StatusId):上的索引

create index idx_sync_rowid_definitionid_statusid
on sync(RowId, DefinitionId, StatusId)

您可以将查询重写为：

select . . .,
s.updated, s.remaining
from SecondaryTags st join
(select rowid, sum(status = 1) as updated, sum(status = 0) as remaining
from sync s
where s.definitionId = 1
group by s.rowid
) s
on s.rowid = st.id
where st.AppId = @AppId;

不过，从业绩的角度来看，我认为指数是个更好的主意。

Answer 2

select st.*
, s.updated
, s.remaining
from SecondaryTags st 
join (select rowid
, sum(status = 1) as updated
, sum(status = 0) as remaining
from sync s
where s.definitionId = 1
group 
by s.rowid
) s
on s.rowid = st.id
where st.AppId = 2;