查詢1
SELECT id, nick_name, avatar, cuRealType, real_due_time, bond, payGoods,
(SELECT ISNULL(SUM(stars) / COUNT(id), 0) AS Expr1
FROM dbo.dt_app_evaluate
WHERE (manager_id = a.id)) AS evaluate,
(SELECT COUNT(id) AS Expr1
FROM dbo.dt_app_follow
WHERE (manager_id = a.id)) AS follow,
(SELECT TOP (1) shopName
FROM dbo.dt_app_attestation
WHERE (status = 1) AND (user_id = a.id)
ORDER BY id DESC) AS shopName,
(SELECT TOP (1) shopLogo
FROM dbo.dt_app_attestation AS dt_app_attestation_2
WHERE (status = 1) AND (user_id = a.id)
ORDER BY id DESC) AS shopLogo,
(SELECT TOP (1) shopContent
FROM dbo.dt_app_attestation AS dt_app_attestation_1
WHERE (status = 1) AND (user_id = a.id)
ORDER BY id DESC) AS shopContent
FROM dbo.dt_users AS a
查詢2
SELECT a.id, a.nick_name, a.avatar, a.cuRealType, a.real_due_time, a.bond, a.payGoods, ISNULL(SUM(b.stars) / COUNT(b.id), 0) AS evaluate, COUNT(c.id) AS follow, d.shopName, d.shopLogo,
d.shopContent
FROM dbo.dt_users AS a LEFT OUTER JOIN
dbo.dt_app_evaluate AS b ON b.manager_id = a.id LEFT OUTER JOIN
dbo.dt_app_follow AS c ON c.manager_id = a.id LEFT OUTER JOIN
(SELECT TOP (1) user_id, shopName, shopLogo, shopContent
FROM dbo.dt_app_attestation
WHERE (status = 1)
ORDER BY id DESC) AS d ON d.user_id = a.id
GROUP BY a.id, a.nick_name, a.avatar, a.cuRealType, a.real_due_time, a.bond, a.payGoods, d.shopName, d.shopLogo, d.shopContent
上面哪個查詢效率更高?
有沒有更優化的陳述句?
uj5u.com熱心網友回復：
哪個效率高你運行一下就知道了，但第二個的結果很可能和第一個的不一樣，因為(SELECT TOP(1)user_id,
shopName,
shopLogo,
shopContent
FROM dbo.dt_app_attestation
WHERE (status = 1)
ORDER BY id DESC) as d
得到的資料很可能d.user_id = a.id不成立
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/48016.html
標籤：基礎類
上一篇：SQL服務意外停止，事物ID 19019 ，日志在下面請大家幫看看
下一篇：有關于區塊鏈和安卓開發