社区所有版块导航
Python
python开源   Django   Python   DjangoApp   pycharm  
DATA
docker   Elasticsearch  
aigc
aigc   chatgpt  
WEB开发
linux   MongoDB   Redis   DATABASE   NGINX   其他Web框架   web工具   zookeeper   tornado   NoSql   Bootstrap   js   peewee   Git   bottle   IE   MQ   Jquery  
机器学习
机器学习算法  
Python88.com
反馈   公告   社区推广  
产品
短视频  
印度
印度  
Py学习  »  DATABASE

MySQL子查询和分组依据

winnie damayo • 4 年前 • 1791 次点击  

我有这个表,我想按以下列对它进行分组,不重复:员工id、级别、生效日期。

| id | employee_id | level | approver_id | deleted_at | effectivity_date | created_at          | updated_at          |
|----|-------------|-------|-------------|------------|------------------|---------------------|---------------------|
| 25 | 13          | 1     | 50          | NULL       | 2022-02-16       | 2022-02-14 19:10:25 | 2022-02-14 19:10:25 |
| 24 | 13          | 3     | 27          | NULL       | 2022-02-14       | 2022-02-14 22:21:57 | 2022-02-14 22:21:57 |
| 23 | 13          | 2     | 6           | NULL       | 2022-02-14       | 2022-02-14 22:21:05 | 2022-02-14 22:21:05 |
| 22 | 13          | 1     | 8           | NULL       | 2022-02-14       | 2022-02-14 22:05:33 | 2022-02-14 22:05:33 |
| 21 | 13          | 1     | 4           | NULL       | 2022-02-14       | 2022-02-14 22:03:59 | 2022-02-14 22:03:59 |
| 20 | 27          | 1     | 48          | NULL       | 2022-02-14       | 2022-02-14 19:50:58 | 2022-02-14 19:50:58 |
| 19 | 8           | 1     | 27          | NULL       | 2022-02-14       | 2022-02-14 19:43:58 | 2022-02-14 19:43:58 |
| 17 | 13          | 1     | 8           | NULL       | 2022-02-14       | 2022-02-14 18:30:44 | 2022-02-14 18:31:34 |
| 16 | 13          | 1     | 44          | NULL       | 2022-02-16       | 2022-02-14 18:30:25 | 2022-02-14 18:30:25 |

这是我目前的SQL代码进度,我不知道这样做是否正确。

SELECT
    t1.employee_id,
    t1.level,
    t1.effectivity_date,
    t1.created_at
FROM leave_approvers t1
WHERE t1.employee_id = 13
AND (
    t1.employee_id,
    t1.level,
    t1.effectivity_date
) = ANY (
    SELECT 
        t2.employee_id,
        t2.level,
        t2.effectivity_date
    FROM leave_approvers t2
    WHERE t2.effectivity_date <= '2022-02-16'
    GROUP BY t2.employee_id, t2.level, t2.effectivity_date
)
ORDER BY t1.level, t1.effectivity_date ASC, t1.created_at ASC

以上代码的结果是,我不想重复行。

| employee_id | level | effectivity_date | created_at          |
|-------------|-------|------------------|---------------------|
| 13          | 1     | 2022-02-14       | 2022-02-14 18:30:44 |
| 13          | 1     | 2022-02-14       | 2022-02-14 22:03:59 |
| 13          | 1     | 2022-02-14       | 2022-02-14 22:05:33 |
| 13          | 1     | 2022-02-16       | 2022-02-14 18:30:25 |
| 13          | 1     | 2022-02-16       | 2022-02-14 19:10:25 |
| 13          | 2     | 2022-02-14       | 2022-02-14 22:21:05 |
| 13          | 3     | 2022-02-14       | 2022-02-14 22:21:57 |

我想要的结果是:

| employee_id | level | effectivity_date | created_at          |
|-------------|-------|------------------|---------------------|
| 13          | 1     | 2022-02-16       | 2022-02-14 19:10:25 |
| 13          | 2     | 2022-02-14       | 2022-02-14 22:21:05 |
| 13          | 3     | 2022-02-14       | 2022-02-14 22:21:57 |
Python社区是高质量的Python/Django开发社区
本文地址:http://www.python88.com/topic/131460
文章 [ 1 ]  |  最新文章 4 年前
D-Shih
Reply   •   1 楼
D-Shih    4 年前

如果您的MySQL版本不支持窗口功能,您可以尝试使用子查询来获取 MAX 生效日期然后自行加入

问题#1

SELECT t1.employee_id,
    t1.level,
    t1.effectivity_date,
    max(t1.created_at) created_at
FROM leave_approvers t1
INNER JOIN (
    SELECT level,employee_id,MAX(effectivity_date) max_effectivity_date
    FROM leave_approvers
    GROUP BY level,employee_id
) t2 
ON  t1.effectivity_date = t2.max_effectivity_date
AND t1.level = t2.level
AND t1.employee_id = t2.employee_id
WHERE t1.employee_id = 13
GROUP BY t1.employee_id,
    t1.level,
    t1.effectivity_date
ORDER BY t1.level, t1.effectivity_date ASC, max(t1.created_at) ASC;
雇员身份证 数量 生效日期 创建于
13 1. 2022-02-16 00:00:00 2022-02-14 19:10:25
13 2. 2022-02-14 00:00:00 2022-02-14 22:21:05
13 3. 2022-02-14 00:00:00

View on DB Fiddle