这是我的表
CREATE TABLE `tabletest`
( `id` bigint(20) NOT NULL ,
`type` int(11) NOT NULL DEFAULT '0' ,
`parent_id` bigint(20) NOT NULL DEFAULT '0' ,
PRIMARY KEY (`id`),
KEY `idx_parent_id_type` (`parent_id`,`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
数据:
type parent_id
101 0
101 4
101 6
101 7
101 9
这是sql1:
select count(DISTINCT parent_id) from tabletest where type = 101
结果1:
3
这是sql2:
select count(DISTINCT parent_id,type) from tabletest where type = 101
结果2:
5
为什么?因为钥匙?
这是一个已知的MySQL错误,您可以通过其他引擎(spark-sql或其他(进行检查。尝试更改MySQL版本。查看此
好吧,我用你的代码和数据集复制了你的测试,我的结果是(在MariaDB5.5.52下(:
select count(DISTINCT parent_id) from tabletest where type = 101;
->5
select count(DISTINCT parent_id,type) from tabletest where type = 101
->5
MySQL的版本是什么?
试试这个:
SELECT DISTINCT(A.type),COUNT(*) AS CountNumber FROM tabletest AS A WHERE A.type=101 GROUP BY A.type