MySQL中根据某个字段去重，日期不同的场景下，获取最小日期的数据

最新推荐文章于 2023-05-26 09:06:32 发布

怪咖基德

最新推荐文章于 2023-05-26 09:06:32 发布

阅读量572

点赞数

分类专栏： SQL 文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/new_guaika/article/details/130014362

版权

SQL 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

SELECT DISTINCT
	'法院公告' AS type_name,
	'法院公告信息非被告' AS NAME,
	'a841a09e633410f50017b4ad06abca09' AS type,
	'f8a4fb6963368c1200008a1c4337440b' AS subclass,
	a.case_no,
	CONCAT(
		'执行案号:',
		IFNULL( a.case_no, '' ),
		'；执行法院:',
	IFNULL( a.court, '' )) content,
	a.sort_time,
	a.credit_code AS social_credit_code,
	'${parentIds}' AS parent_ids,- 1 AS score,
	c.LEVEL,
CASE
		c.LEVEL 
		WHEN 100 THEN
		'低' 
		WHEN 50 THEN
		'中' 
		WHEN 1 THEN
		'高' ELSE '' 
	END AS level_description,
	0 AS deduct 
FROM
	report_business.fahai_sifa_fygg a
	JOIN report_business.fahai_sifa_fygg_party b ON a.fygg_id = b.fygg_id 
	AND a.credit_code = b.credit_code 
	AND b.party_position_t != 'd'
	JOIN report_business.risk_type c ON c.NAME = '法院公告信息非被告' 
	AND c.score IS NOT NULL 
WHERE
	a.credit_code = '911100007693890511' 
	AND a.case_no IS NOT NULL
	AND a.sort_time >= (SELECT date_format(DATE_SUB(CURDATE(),interval 24 month),'%Y-%m-%d') FROM DUAL)
ORDER BY case_no,sort_time

在这里插入图片描述
修改结果：

WITH temp AS (
		SELECT DISTINCT
	'法院公告' AS type_name,
	'法院公告信息非被告' AS NAME,
	'a841a09e633410f50017b4ad06abca09' AS type,
	'f8a4fb6963368c1200008a1c4337440b' AS subclass,
	a.case_no,
	CONCAT(
		'执行案号:',
		IFNULL( a.case_no, '' ),
		'；执行法院:',
	IFNULL( a.court, '' )) content,
	a.sort_time,
	a.credit_code AS social_credit_code,
	'${parentIds}' AS parent_ids,- 1 AS score,
	c.LEVEL,
CASE
		c.LEVEL 
		WHEN 100 THEN
		'低' 
		WHEN 50 THEN
		'中' 
		WHEN 1 THEN
		'高' ELSE '' 
	END AS level_description,
	0 AS deduct 
FROM
	report_business.fahai_sifa_fygg a
	JOIN report_business.fahai_sifa_fygg_party b ON a.fygg_id = b.fygg_id 
	AND a.credit_code = b.credit_code 
	AND b.party_position_t != 'd'
	JOIN report_business.risk_type c ON c.NAME = '法院公告信息非被告' 
	AND c.score IS NOT NULL 
WHERE
	a.credit_code = '911100007693890511' 
	AND a.case_no IS NOT NULL
	AND a.sort_time >= (SELECT date_format(DATE_SUB(CURDATE(),interval 24 month),'%Y-%m-%d') FROM DUAL)
ORDER BY case_no,sort_time
),
temp1 AS (
SELECT  c.* ,
	if(@case_no=c.case_no, @rank:=@rank+1, @rank:=1) as rn,
	@case_no:=c.case_no
 FROM temp c,(SELECT @rownum:=0, @case_no:=null, @rank:=0) d
)
SELECT * FROM temp1
WHERE rn = 1