/* 统计列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"