如何获取指定列去重结果并附带其他列及计数（含日期过滤）

需求说明 #

• 获取ColumnA去重后的结果，同时保留ColumnC、ColumnD列
• 统计每个ColumnA对应的datecreate在近3个月内的数据行数
• 注：假设同一ColumnA对应的ColumnC、ColumnD值完全一致，否则需额外逻辑处理

示例数据表 #

ColumnA     ColumnB ColumnC         ColumnD datecreate
cust_001    IDR     personal        active  2023-12-24 00:00:00.000
cust_001    JPY     personal        active  2023-08-11 00:00:00.000
cust_001    USD     personal        active  2024-01-07 00:00:00.000
cust_002    SAR     company         active  2023-01-01 00:00:00.000
cust_002    SGD     company         active  2023-10-11 00:00:00.000
cust_003    CAD     company         active  2024-01-01 00:00:00.000
cust_004    IDR     company         active  2024-01-05 00:00:00.000
cust_004    SAR     company         active  2024-01-03 00:00:00.000
cust_005    EUR     company         active  2024-01-09 00:00:00.000

期望查询结果 #

columna     columnc     columnd count
cust_001    personal    active  2   
cust_002    company     active  1
cust_003    company     active  1
cust_004    company     active  2
cust_005    company     active  1

实现SQL语句 #

根据不同数据库，计算近3个月的语法略有差异：

MySQL/MariaDB

SELECT 
    ColumnA AS columna,
    ColumnC AS columnc,
    ColumnD AS columnd,
    COUNT(*) AS count
FROM 
    your_table_name
WHERE 
    datecreate >= DATE_SUB(CURDATE(), INTERVAL 3 MONTH)
GROUP BY 
    ColumnA, ColumnC, ColumnD;

SQL Server

SELECT 
    ColumnA AS columna,
    ColumnC AS columnc,
    ColumnD AS columnd,
    COUNT(*) AS count
FROM 
    your_table_name
WHERE 
    datecreate >= DATEADD(MONTH, -3, GETDATE())
GROUP BY 
    ColumnA, ColumnC, ColumnD;

PostgreSQL

SELECT 
    ColumnA AS columna,
    ColumnC AS columnc,
    ColumnD AS columnd,
    COUNT(*) AS count
FROM 
    your_table_name
WHERE 
    datecreate >= CURRENT_DATE - INTERVAL '3 months'
GROUP BY 
    ColumnA, ColumnC, ColumnD;

语句说明 #

1. WHERE子句：筛选出datecreate在近3个月内的数据，排除不符合时间范围的记录
2. GROUP BY子句：按ColumnA、ColumnC、ColumnD分组，确保同一ColumnA对应的ColumnC、ColumnD值被保留
3. COUNT(*)：统计每个分组内的记录行数，即每个ColumnA符合条件的数据量

内容的提问来源于stack exchange，提问作者Nisemono