指定列去重

table task

taskid	userid	status	tasknumber	…
1	123	1	D202308	…
2	123	1	D202308	…
3	123	1	D202309	…
4	223	1	D202309	…

PARTITION BY ： 在分区内（如tasknumber列和userid列）进行计算
ROW_NUMBER() OVER ：从1开始，为每条分组记录返回一个数字

//给相同（tasknumber, userid)的记录编号
SELECT taskid, tasknumber, userid, 
		ROW_NUMBER() OVER (PARTITION BY tasknumber, userid ORDER BY tasknumber ASC) AS rn
FROM task t

查询结果

taskid	tasknumber	userid	rn
1	D202308	123	1
2	D202308	123	2
3	D202309	123	1
4	D202309	223	1

tasknumber和userid相同的task，只显示其中一个的taskid：

WITH temp_table AS (
	SELECT taskid, tasknumber, userid, status,
		ROW_NUMBER() OVER (PARTITION BY tasknumber, userid ORDER BY tasknumber) AS rn
	FROM task t 
) SELECT tt.taskid 
FROM temp_table tt INNER JOIN user_ u ON tt.userid = u.userid 
WHERE tt.rn = 1 AND DATE(u.submissiondate) >= '2023-08-01' AND DATE(u.submissiondate) <= '2023-08-31' AND 
		tt.status = 1

taskid
1
3
4

参考链接：
SQL：数据去重的三种方法