mysql - 获取每个ID的最大值

This question already has an answer here:

SQL counting all rows instead of counting individual rows
（1个答案）
去年关门了。
我有这样的疑问：

BEGIN
  DROP TABLE IF EXISTS `tblquota`;
CREATE TABLE IF NOT EXISTS tblquota (id INT AUTO_INCREMENT PRIMARY KEY) AS
     SELECT c.id as user_id
         , c.`email`
         , h.`packageid` as pack_id
         , p.`name` as pack_name
         , max(CASE
             WHEN `name` = "Basic" THEN '100'
             WHEN `name` = "Silver" THEN '100'
             WHEN `name` = "Gold" THEN '100'
                 ELSE '10'
            END) as quota
     FROM `tblclients` c
     LEFT JOIN `tblhosting` h ON c.id = h.userid
     INNER JOIN `tblproducts` p ON h.packageid = p.id
     ORDER BY c.id;
END

在这种情况下，查询将只获取一个客户的最大值。我想得到每位顾客的价值。
表客户端：

id | email          | status
----------------------------
1  | [email protected] | Active
2  | [email protected] | Inactive
3  | [email protected] | Active

表tblhosting

id | userid | packageid | domainstatus
------------------------------------------------
1  | 1      | 3         | Active
2  | 1      | 2         | Active
3  | 2      | 1         | Active
4  | 2      | 2         | Active
5  | 2      | 3         | Inactive
6  | 3      | 1         | Active

表TBL产品

id | name
-----------
1  | Basic
2  | Silver
3  | Gold

我希望结果如下：

id | user_id | email          | pack_id | pack_name | quota
-----------------------------------------------------------
1  | 1       | [email protected] | 3       | Gold      | 1000
2  | 2       | [email protected] | 2       | Silver    | 100
3  | 3       | [email protected] | 1       | Basic     | 10

最佳答案

你要找的是Max OVER (PARTITION BY user_id)

关于mysql - 获取每个ID的最大值，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/53372125/