sql如何从单个具有重复项的表中获得统计数据
时间：2010-11-08 来源：虎克
表中只有一个字符型字段CanonicalString和ID具有唯一值，其它字段都有不同程度的重复。需要的结果是统计每一个不同分类等级(界、门、纲、目、科、属)的下级分类单元有多少。例如：统计出每一个科(TaxonFamily)有多少个属(TaxonGenus),多少个分类群(CanonicalString)；统计每一个门有多纲、目、科、属等等，其它依次类推。
1、统计每一个科有多少分类群
SELECT TaxonFamily AS Family ,
COUNT(Id) AS TaxonNumber
FROM dbo.Taxons
GROUP BY TaxonFamily
ORDER BY TaxonNumber DESC
运行结果：
2、统计类群数目最多的10个科
SELECT TOP(10) TaxonFamily AS Family ,
COUNT(Id) AS TaxonNumber
FROM dbo.Taxons
GROUP BY TaxonFamily
ORDER BY TaxonNumber DESC
3、统计每一个科包括有多少个属
SELECT TaxonFamily AS Family ,
COUNT(DISTINCT ( TaxonGenus )) AS GenusNumber
FROM dbo.Taxons
GROUP BY TaxonFamily
ORDER BY GenusNumber DESC
运行结果：
4、统计每一个科的属和分类群的数目
SELECT TaxonFamily AS Family ,
COUNT(DISTINCT ( TaxonGenus )) AS GenusNumber ,
COUNT(Id) AS TaxonNumber
FROM dbo.Taxons
GROUP BY TaxonFamily
ORDER BY GenusNumber DESC
运行结果：
5、统计每一个属包括的分类群数目并显示这个属的科名
SELECT TaxonFamily AS Family, TaxonGenus AS Genus ,
COUNT(Id) AS TaxonNumber
FROM dbo.Taxons
GROUP BY TaxonGenus, TaxonFamily
ORDER BY TaxonNumber DESC
运行结果：
6、统计某一个科的属和分类群数目
SELECT TaxonFamily AS Family ,
COUNT(DISTINCT ( TaxonGenus )) AS GenusNumber ,
COUNT(Id) AS TaxonNumber
FROM dbo.Taxons
WHERE TaxonFamily = 'Pinaceae'
GROUP BY TaxonFamily
运行结果：
7、统计物种名字字段(CanonicalString)中空格出现的次数
SELECT Id ,
CanonicalString ,
LEN(CanonicalString) - LEN(REPLACE(CanonicalString, ' ', '')) AS 空格出现次数
FROM dbo.Taxons
运行结果如下：
上面这个语句的作用是当我们没有有效标记改类群的分类等级，种或者亚种、变种的时候，可以用来作为筛选记录的一个备选方法。
8、统计每一个科有多少属和种(不包括种下等级的情况)
SELECT TaxonFamily AS Family ,
COUNT(DISTINCT ( TaxonGenus )) AS GenusNumber ,
COUNT(Id) AS SpeciesNumber
FROM dbo.Taxons
WHERE LEN(CanonicalString) - LEN(REPLACE(CanonicalString, ' ', '')) = 1
GROUP BY TaxonFamily
相关阅读 更多 +
