计数错误导致ms访问

Question

我写了一个SQL查询，试图通过森林类型获得一组树木的一些信息。然而，当我运行这个查询时，Count(T_Plots.ID_plot) AS [Plots/Forest Type]返回与Count(T_Trees.ID_Tree) AS [Trees/Forest Type]相同的数字。这些数字太高了，因为整个T_Plots表只有175条记录，而在结果表中，它返回的结果高达4290条。

我忽略了什么导致这些错误的数字，我如何得到每个森林类型的正确地块数量?

SELECT
T_Plots.Forest_type,
Count(T_Plots.ID_plot) AS [Plots/Forest Type],
Count(T_Trees.ID_Tree) AS [Trees/Forest Type],
[Trees/Forest Type]/[Plots/Forest Type]*10 AS [Trees/Ha],
Avg([T_Trees.DBH (cm)])/100*Avg([T_Trees.Height (m)])*0.7*[Trees/Ha] AS [Volume (m3)/Ha],
3.142*(((Avg([T_Trees.DBH (cm)])/2)^2)/100)*[Trees/Ha] AS [BA (m2)/Ha]
FROM T_Plots INNER JOIN T_Trees
ON T_Plots.ID_plot = T_Trees.ID_plot
GROUP BY T_Plots.Forest_type

Images:T_Plots T_Trees Results

Answer 1

我想DISTINCT可以解决你的问题。试一试:

SELECT
T_Plots.Forest_type,
Count(Distinct T_Plots.ID_plot) AS [Plots/Forest Type],
Count(Distinct T_Trees.ID_Tree) AS [Trees/Forest Type],
[Trees/Forest Type]/[Plots/Forest Type]*10 AS [Trees/Ha],
Avg([T_Trees.DBH (cm)])/100*Avg([T_Trees.Height (m)])*0.7*[Trees/Ha] AS [Volume (m3)/Ha],
3.142*(((Avg([T_Trees.DBH (cm)])/2)^2)/100)*[Trees/Ha] AS [BA (m2)/Ha]
FROM T_Plots INNER JOIN T_Trees
ON T_Plots.ID_plot = T_Trees.ID_plot
GROUP BY T_Plots.Forest_type