我需要使用两个表创建一个包含最多出现次数的数据行

Question

我需要进行一个查询，为我提供列上最常见的值，出现次数最多的。

例如：

Name   Grade     Gender
--------------------------
Jeff   100       Male
Daniel 100       Male
Linda   80       Female
Jeff    90       Male

该查询将为我提供一个数据行，其中包含姓名-Jeff Grade-100性别-男性

到目前为止，我的问题是：

SELECT
PhonesTBL.OperatingSystem, PhonesTBL.Memory, 
PhonesTBL.BatterySize, PhonesTBL.CameraQuality, PhonesTBL.Processor, 
PhonesTBL.ScreenSize, PhonesTBL.PhoneType 
FROM  
PhonesTBL 
INNER JOIN 
HistoryTBL ON PhonesTBL.PhoneID = HistoryTBL.PhoneID 
WHERE 
UserID = Uid 
GROUP BY
OperatingSystem, Memory, BatterySize, CameraQuality, Processor, 
ScreenSize, PhoneType 
ORDER BY
COUNT(*) DESC

但它返回的值与我想要的值完全不同。

寻求帮助，Ohad

Answer 1

你的问题真的没有意义。您有与查询无关的示例数据。让我使用样本数据。

在MS Access中，最简单的方法是将模式(您想要的统计名称(放在单独的行中：

(select top (1) "name" as which, name
from t
group by name
order by count(*) desc, name
) union all
(select top (1) "grade" as which, grade
from t
group by grade
order by count(*) desc, grade
) union all
(select top (1) "gender" as which, gender
from t
group by gender
order by count(*) desc, gender
);