mysql、oracle 分组查询，每组只取一条数据

1.情景展示

有这样一种需求：

将数据按照机构进行分组，然后取每个机构下只取一条记录，如何实现？

2.mysql

分组查询出来某字段，然后和原来的表进行关联查询。

方式一：通过内连接来实现

查看代码

select
	t3.invoicingpartycode,
	t3.invoicingpartyname,
	t2.einvoicecode,
	t2.einvoicenumber,
	t2.cardno,
	t2.payerpartycode,
	t2.payerpartyname 
from
	(
	select
		orgid,
		max(id) id 
	from
		cz_fet_main_mz 
	where
		cardtype = '01'
		and length(cardno)= 18 
		and kpstatus = 1 
		and date_format(issuetime, '%Y')= '2021' 
	group by
		orgid 
	) t,
	cz_fet_main_mz t2,
	cz_unitinfo t3 
where
	t2.orgid = t.orgid 
	and t2.id = t.id 
	and t3.orgcode = t2.orgid 
order by
	t3.checkcode

这里有两个核心点：

第一，分组查询出来的字段，需要确保该字段在表中必须具有数据唯一性，不然的话，分组查询没有意义；

比方说：时间，但时间也有可能重复，如果我们只精确到秒的话（毫秒也可能会重）；

如果表中没有定义唯一性字段，且确实不知道那个字段具有唯一性，最简单的方式就是表主键（表主键本身就具有唯一性）。

第二，将查询结果当做一张表，来和之前的表建立内连接。

上面的代码，其实还可以进行简化：

ORGID字段可以去掉，即：

查看代码

SELECT
	t3.INVOICINGPARTYCODE,
	t3.INVOICINGPARTYNAME,
	t2.EINVOICECODE,
	t2.EINVOICENUMBER,
	t2.CARDNO,
	t2.PAYERPARTYCODE,
	t2.PAYERPARTYNAME 
FROM
	(
	SELECT
		max( ID ) ID 
	FROM
		cz_fet_main_mz 
	WHERE
		CARDTYPE = 01 
		AND LENGTH( CARDNO )= 18 
		AND KPSTATUS = 1 
		AND DATE_FORMAT( ISSUETIME, '%Y' )= '2021' 
	GROUP BY
		ORGID 
	) t,
	cz_fet_main_mz t2,
	cz_unitinfo t3 
WHERE
	T2.ID = T.ID 
	AND t3.ORGCODE = t2.ORGID 
ORDER BY
	t3.CHECKCODE

执行所需时间：

代码简化前耗费时间：

由此可见，SQL不是越简化越快（另外，重复的where限制条件，加上也并不能提高查询速度）。

方式二：通过exits来实现

查看代码

SELECT
	t3.INVOICINGPARTYCODE,
	t3.INVOICINGPARTYNAME,
	t2.EINVOICECODE,
	t2.EINVOICENUMBER,
	t2.CARDNO,
	t2.PAYERPARTYCODE,
	t2.PAYERPARTYNAME 
FROM
	cz_fet_main_mz t2,
	cz_unitinfo t3 
WHERE
	t3.ORGCODE = t2.ORGID 
	AND EXISTS (
	SELECT
		1 
	FROM
		cz_fet_main_mz 
	WHERE
		CARDTYPE = 01
		AND LENGTH( CARDNO )= 18 
		AND KPSTATUS = 1 
		AND DATE_FORMAT( ISSUETIME, '%Y' )= '2021' 
	GROUP BY
		ORGID 
	HAVING
		ORGID = t2.ORGID 
		AND INVOICENO = t2.INVOICENO 
	) 
ORDER BY
	t3.CHECKCODE

使用exists()从逻辑上应该是行得通的，但是，我并没有测试出来这种方式的真实性。

原因在于：数量太大，查询结果太慢，用了700多秒还没执行完，所以，放弃了；

如果你的数据量小的话，可以试试看，如果可行的话，欢迎在评论区留言，我替大家谢谢你。

2025-01-15 17:22:33

方式三：使用窗口函数row_number()

ROW_NUMBER() 是一个窗口函数，用于为结果集中的每一行分配唯一的行号，通常与 OVER 子句一起使用，以便对特定分区内的行进行排序和编号。

语法：

ROW_NUMBER() OVER (
    [PARTITION BY partition_expression]
    ORDER BY sort_expression [ASC|DESC]
)

PARTITION BY：可选参数，用于将结果集划分为多个分区，并在每个分区内独立计算行号。

ORDER BY：必需参数，指定分区内行的排序规则。

select t.* from
	(select
		t1.einvoicecode,
		t1.einvoicenumber,
		t1.cardno,
		t1.payerpartycode,
		t1.payerpartyname,
		t2.invoicingpartycode,
		t2.invoicingpartyname,
		row_number() OVER (PARTITION BY t1.orgid ORDER BY T1.createTime DESC) AS rowno
	from
		cz_fet_main_mz t1,
		cz_unitinfo t2
	where
		t1.cardtype = '01'
		and length(t1.cardno)= 18
		and t1.kpstatus = 1
		and t1.issuetime >= '2021-01-01'
		and t1.issuetime < '2022-01-01'
		and t2.orgcode = t1.orgid) t
where t.rowno = 1

该查询使用了一个子查询来对每个 orgid 分区的数据按 createTime 进行降序排列，并选取每个分区内最新的记录（即 rowno = 1 的记录）。

关键点：

使用 ROW_NUMBER() 函数为每个 orgid 分区内的记录分配一个行号，按 createTime 降序排列。

从子查询的结果中进一步筛选出每个 orgid 分区内最新的记录（rowno = 1）。

这样一来，即使t1表存在重复的orgid，也没关系。

3.oracle

内连接的语法与mysql一致，这里不再赘述。

4.扩展

关于计数查询count(1)

count(1)函数，如果表中有索引的话，会自动使用索引字段进行查询；

如果没有额外创建索引的话，会自动使用主键索引字段进行查询；（主键是唯一索引）

如果对没有索引的字段进行计数的话，就会全表执行。

没有使用这种方式的必要，还浪费时间。

关于索引

如上面所示，要进行分组查询的表，一共有1163万条数据；

分组和查询的字段必须得走索引，如果不添加索引的话，那得查到什么时候？（数据少的话，有没有索引或者走不走索引都无所谓）

写在最后

　　哪位大佬如若发现文章存在纰漏之处或需要补充更多内容，欢迎留言！！！

 相关推荐：

• 个人主页
• mysql sum()按条件求和、count()按条件计数