首页 文章

MySQL - 按组排除最近一个月

提问于
浏览 次
1

Problem:

Employee表保存一年的工资信息 .

编写一个SQL来获取员工在3个月内的工资累计总和，但不包括最近一个月 .

结果应以'Id'升序显示，然后按'Month'降序显示 .

Employee table:

| Id | Month | Salary |
|----|-------|--------|
| 1  | 1     | 20     |
| 2  | 1     | 20     |
| 1  | 2     | 30     |
| 2  | 2     | 30     |
| 3  | 2     | 40     |
| 1  | 3     | 40     |
| 3  | 3     | 60     |
| 1  | 4     | 60     |
| 3  | 4     | 70     |

My Code:

SELECT t1.Id, t1.Month, 
  (SELECT SUM(Salary) 
   FROM  Employee AS t2
   WHERE t1.Id = t2.Id
   AND   t1.Month >= t2.Month) AS Salary
FROM Employee t1
WHERE Month <> (SELECT 
            MAX(Month) 
            FROM Employee
            GROUP BY t1.Id)
ORDER BY Id, Month DESC;

My Output:

| Id | Month | Salary |
|----|-------|--------|
| 1  | 3     | 90     |
| 1  | 2     | 50     |
| 1  | 1     | 20     |
| 2  | 2     | 50     |
| 2  | 1     | 20     |
| 3  | 3     | 100    |
| 3  | 2     | 40     |

Expected:

| Id | Month | Salary |
|----|-------|--------|
| 1  | 3     | 90     |
| 1  | 2     | 50     |
| 1  | 1     | 20     |
| 2  | 1     | 20     |
| 3  | 3     | 100    |
| 3  | 2     | 40     |

我使用了 MAX() 和 GROUP BY() 函数来排除每个组的最近一个月，但它不适用于Id = 2 .

有关如何摆脱以下行的建议吗？

| 2  | 2     | 50     |

提前致谢 .

mysql sql group-by subquery

4 回答

  • 1

    我认为这个答案最接近你在原始查询中尝试做的事情：

    SELECT t1.id, t1.month,
    (SELECT SUM(salary)
       FROM employee t2
      WHERE t1.id = t2.id
        AND t1.month >= t2.month
        AND t1.month - t2.month < 3) AS salary
  FROM employee t1
 WHERE month <> (SELECT MAX(month)
                   FROM employee t3
                  WHERE t3.id = t1.id)
 ORDER by id, month desc;

    第二眼看，你实际上非常接近 . 我认为问题是“GROUP BY t1.Id”行实际上并没有对任何内容进行分组，因为t1.Id对于任何给定的子查询都是常量，因为在outtermost select语句中定义了“t1” . 将其替换为where子句，并在SUM（）查询中将总数限制为3个月，您就在那里 .

    回复于
  • 2

    要获得过去3个月的累计金额，不包括每个ID的最近一个月，您可以使用

    SELECT t1.Id, t1.Month, SUM(t2.Salary) 
FROM Employee t1
JOIN Employee t2 ON t1.Id = t2.Id AND t1.Month - t2.Month <= 2 AND t1.Month - t2.Month >= 0
JOIN (SELECT id, MAX(month) as max_mth from Employee GROUP BY id) tmax on tmax.id=t1.id AND tmax.max_mth<>t1.month
GROUP BY t1.Id, t1.Month
ORDER BY t1.Id, t1.Month DESC;
    回复于
  • 1

    试试这个：

    SELECT t1.id, t1.month,
    (SELECT SUM(salary)
       FROM employee t2
      WHERE t1.id = t2.id
        AND t1.month >= t2.month
        AND t1.month - t2.month < 3) AS salary
  FROM (
    SELECT * FROM employee p
     WHERE month <> (select MAX(month)
      FROM employee c where c.id = p.id)) t1
ORDER BY id, month desc;

    输出是：

    +------+-------+--------+
| id   | month | salary |
+------+-------+--------+
|    1 |     3 |     90 |
|    1 |     2 |     50 |
|    1 |     1 |     20 |
|    2 |     1 |     20 |
|    3 |     3 |    100 |
|    3 |     2 |     40 |
+------+-------+--------+

    您遇到的问题是您只删除了所有员工的最后一个月 . 我相信你想要的是删除每个员工的上个月，即使上个月是几个月前 . 此解决方案创建一个派生表，其中每个员工缺少上个月，并使用该表代替您的t1员工表 .

    回复于
  • 2

    试试这个查询：

    SELECT e.Id, e.Month, SUM( e2.Salary ) AS 'Salary'

FROM 
    Employee AS e

    INNER JOIN Employee AS e2
        ON e2.Id = e.Id
        AND e2.Month <= e.Month

WHERE 
    e.Month <> ( SELECT MAX( [Month] ) FROM Employee WHERE Id = e.Id )

GROUP BY
    e.Id, e.Month

ORDER BY 
    e.Id, e.Month DESC

    输出是：

    +----+-------+--------+
| Id | Month | Salary |
+----+-------+--------+
|  1 |     3 |     90 |
|  1 |     2 |     50 |
|  1 |     1 |     20 |
|  2 |     1 |     20 |
|  3 |     3 |    100 |
|  3 |     2 |     40 |
+----+-------+--------+
    回复于

相关问题