PostgreSQL:当x = a和x = b时,如何从表中选择2个不同的计数
发布时间:2020-12-13 16:08:08 所属栏目:百科 来源:网络整理
导读:亲爱的朋友Stackoverflowers, 如何从场景中的同一个表中选择2个不同的计数: x=a and x=b 具体来说,(WHEN type = subdomain)AND(WHEN subtype = subdomain)? 将它们添加到一起以创建’totalcount’? 我的尝试(供你参考): SELECT description,type,count(1
亲爱的朋友Stackoverflowers,
如何从场景中的同一个表中选择2个不同的计数: x=a and x=b 具体来说,(WHEN type = subdomain)AND(WHEN subtype = subdomain)? 将它们添加到一起以创建’totalcount’? 我的尝试(供你参考): SELECT description,type,count(1),subtype,count(2) FROM mapping,(dotcom WHERE type = subdomain) AS typecount,(dotcom WHERE subtype = subdomain) AS subtypecount GROUP BY description,subtype HAVING count(1)>1 AND count(2)>1 ORDER BY count(*) DESC LIMIT 10 第二次尝试: SELECT description FROM mapping,SUM(WHEN subdomain = type OR subdomain = subtype) AS count(1) GROUP BY description,subtype HAVING count(1)>1 ORDER BY count(*) DESC LIMIT 10 解决方法
如果你有Postgres 9.4或更新版本,你可以使用FILTER,它比CASE WHEN … END语法更容易阅读.
SELECT description,COUNT(*) FILTER (WHERE subdomain = type) AS typecount,COUNT(*) FILTER (WHERE subtype = subdomain) AS subtypecount FROM mapping GROUP BY description,subtype; 应该注意的是,count(1)和count(2)并不像你想象的那样; count是一个聚合函数,它计算每一行中给定值是否为空,并且看到你给它一个整数字面值,它实际上只计算返回的行数. (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |