幫助編寫查詢，選擇組中的最小列

我有兩個簡單的表

CREATE TABLE user (
 id INT NOT NULL AUTO_INCREMENT,
 nickname varchar(35) NOT NULL, 
 PRIMARY KEY (id),
);

CREATE TABLE user_service (
 id INT NOT NULL AUTO_INCREMENT,
 user_id INT NOT NULL,
 service_type VARCHAR(10) NOT NULL,
 price numeric(15,2) NOT NULL,
 PRIMARY KEY (id),
 UNIQUE KEY uq_user_service (user_id, service_type),
 FOREIGN KEY (user_id) REFERENCES user(id) ON DELETE CASCADE
);

我試圖在一個查詢中檢索提供某些服務的使用者列表。查詢應返回使用者及其服務的最低價格

這是我寫的查詢，你可以看到我寫了一個子選擇，我想知道這是否是一種很好的做事方式？或者有沒有更有效的方法？

SELECT
   u.id,
   u.nickname,
   (select MIN(price) from user_service where user_id=u.id order by price desc) as minPrice
   FROM
     user u
     LEFT JOIN user_service us ON us.user_id = u.id
   WHERE us.service_type IN ('TYPE1','TYP2')
  group by u.id;

你可以試試這個：

SELECT
   u.id,
   u.nickname,
   t.minPrice
   FROM
     user u
     LEFT JOIN user_service us ON us.user_id = u.id
     INNER JOIN (
   SELECT user_id, MIN(price) minPrice
       FROM user_service
       GROUP BY user_id    
   ) t ON t.user_id = u.id AND us.price = t.minPrice
   WHERE us.service_type IN ('TYPE1','TYPE2') and u.id=1
  group by u.id;

引用自：https://dba.stackexchange.com/questions/122911