select count(*) as CountId, [FirstRouteNo],[ThroughRouteSid],[LastRouteNo],
(select top 1 [ThroughRouteJson]
from DirectTransfer as Subquery
where MainQuery.FirstRouteNo=Subquery.FirstRouteNo and
MainQuery.ThroughRouteSid = Subquery.ThroughRouteSid and
MainQuery.LastRouteNo = Subquery.LastRouteNo
) as DetailJson,
(select top 1 RouteMeter
from DirectTransfer as Subquery
where MainQuery.FirstRouteNo = Subquery.FirstRouteNo and
MainQuery.ThroughRouteSid = Subquery.ThroughRouteSid and
MainQuery.LastRouteNo = Subquery.LastRouteNo
) as RouteMeter
from DirectTransfer as MainQuery
group by MainQuery.[FirstRouteNo],MainQuery.[ThroughRouteSid],MainQuery.[LastRouteNo]
order by CountId desc
我想按此列分组 [FirstRouteNo],[ThroughRouteSid],[LastRouteNo] 然后计算多少记录。但我也想显示两列值，如 [ThroughRouteJson] 和 [RouteMeter] 任何一个记录。因为 [ThroughRouteJson]和 [RouteMeter] 的值几乎没有什么不同。所以我不能和他们分组。然后子查询只返回一个值。所以我写了两个子查询来得到我想要的。因为我的数据库表有超过 1 亿条记录。我想要高效。我怎样才能让这段代码变得更有效率，然后我才能得到相同的结果数据？
我建议你这样做：
select dt.*, dt2.DetailJson, dt2.RouteMeter
from (select count(*) as cnt, dt.FirstRouteNo, dt.ThroughRouteSid, dt.LastRouteNo
from DirectTransfer dt
group by dt.FirstRouteNo, dt.ThroughRouteSid, dt.LastRouteNo
) dt outer apply
(select top 1 ThroughRouteJson as DetailJson, RouteMeter
from DirectTransfer dt2
where dt.FirstRouteNo = dt.FirstRouteNo and
dt.ThroughRouteSid = dt.ThroughRouteSid and
dt.LastRouteNo = dt.LastRouteNo
) dt2
order by CountId desc;
你想要索引DirectTransfer(FirstRouteNo, ThroughRouteSid, LastRouteNo)。可能还有其他方法可以完成您想要的操作，但是您要尝试执行的操作有点不清楚。
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句