主页 / dba / 问题 / 122911
Accepted
Alexis
Asked: 2015-12-05 07:49:36 +0800 CST

帮助编写一个查询来选择组中的最小列

  • 772

我有两个简单的表

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;
subquery

1 个回答

  1. Best Answer

    你可以试试这个：

    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;
    • 1

相关问题