请问这条sql 语句怎样优化呢?

[guolucky2003]

如题:
SELECT username, pid, style, datetime, SUM(price) AS sumprice,
(SELECT pic
FROM dbo.canpin
WHERE pid = t_price.pid) AS pic,
(SELECT city
FROM dbo.user_table
WHERE username = t_price.username) AS city
FROM dbo.t_price
GROUP BY username, pid, style, datetime

[rucypli]

首先canpin.pid 和 user_table.username加索引

[dawugui]

如果三表不能匹配,就这么写吧.

[dawugui]

--试试这个对不对,三表不能统一连接的话,也许不对.

SELECT username, pid, style, datetime, pic , city , SUM(price) AS sumprice, 
FROM dbo.t_price ,dbo.canpin , dbo.user_table
where dbo.t_price.pid = dbo.canpin.pid and dbo.t_price.username = dbo.user_table.username
GROUP BY username, pid, style, datetime , pic , city

[赵大米]

用下面的语句似乎你的结果应该不变

SELECT t_price.username,
t_price.pid,
t_price.style,
t_price.datetime,
canpin.pic,
user_table.city
SUM(t_price.price) AS sumprice
FROM dbo.t_price
JOIN dbo.canpin ON canpin.pid = t_price.pid
JOIN dbo.user_table ON user_table.username = t_price.username
GROUP BY
t_price.username,
t_price.pid,
t_price.style,
t_price.datetime,
canpin.pic,
user_table.city