oracle-sql统计列m下的不重复列n的数目

最新推荐文章于 2024-03-19 15:02:28 发布

Leon_190

最新推荐文章于 2024-03-19 15:02:28 发布

阅读量1k

点赞数

分类专栏：技术分享文章标签： oracle sql group by count distinct

本文链接：https://blog.csdn.net/Leon_190/article/details/71109405

版权

技术分享专栏收录该内容

17 篇文章 0 订阅

订阅专栏

/* 统计列m下的不重复n的数目 */

传统逻辑写法：

--1
SELECT "m", count(1) "count"
FROM(
	SELECT "m", "n", count(1) "count"
	FROM (
		SELECT 'a' "m", '1' "n" FROM DUAL
		UNION ALL 
		SELECT 'a' "m", '1' "n" FROM DUAL
		UNION ALL 
		SELECT 'a' "m", '2' "n" FROM DUAL
		UNION ALL 
		SELECT 'b' "m", '1' "n" FROM DUAL
		UNION ALL 
		SELECT 'b' "m", '2' "n" FROM DUAL
		UNION ALL 
		SELECT 'b' "m", '2' "n" FROM DUAL
	) x
	GROUP BY "m", "n")
GROUP BY "m"
--2
SELECT "m", count(1) "count" FROM (
	SELECT DISTINCT "m", "n"
		FROM (
			SELECT 'a' "m", '1' "n" FROM DUAL
			UNION ALL 
			SELECT 'a' "m", '1' "n" FROM DUAL
			UNION ALL 
			SELECT 'a' "m", '2' "n" FROM DUAL
			UNION ALL 
			SELECT 'b' "m", '1' "n" FROM DUAL
			UNION ALL 
			SELECT 'b' "m", '2' "n" FROM DUAL
			UNION ALL 
			SELECT 'b' "m", '2' "n" FROM DUAL
		) x
) GROUP BY "m"

简单写法：

--3
SELECT "m", count(DISTINCT("n")) "count"
	FROM (
		SELECT 'a' "m", '1' "n" FROM DUAL
		UNION ALL 
		SELECT 'a' "m", '1' "n" FROM DUAL
		UNION ALL 
		SELECT 'a' "m", '2' "n" FROM DUAL
		UNION ALL 
		SELECT 'b' "m", '1' "n" FROM DUAL
		UNION ALL 
		SELECT 'b' "m", '2' "n" FROM DUAL
		UNION ALL 
		SELECT 'b' "m", '2' "n" FROM DUAL
	) x
GROUP BY "m"