假设我有一个表( most / MYSQL ),它有两个列( Time 'hh:mm:ss‘,Value ),并且我希望为每一组行获取最频繁的值。
例如,我有
Time | Value
4:35:49 | 122
4:35:49 | 122
4:35:50 | 121
4:35:50 | 121
4:35:50 | 111
4:35:51 | 122
4:35:51 | 111
4:35:51 | 111
4:35:51 | 132
4:35:51 | 132每一次我都想得到最经常的价值
Time | Value
4:35:49 | 122
4:35:50 | 121
4:35:51 | 132提前感谢
备注我需要得到与Excel解决方案:为每个组获取最常用的值。相同的结果
**我的SQL解决方案**
我找到了一种解决方案(来源),它可以很好地在mysql上工作,但是我无法让它在ms-access中工作:
select cnt1.`Time`,MAX(cnt1.`Value`)
from (select COUNT(*) as total, `Time`,`Value`
from `my_table`
group by `Time`,`Value`) cnt1,
(select MAX(total) as maxtotal from (select COUNT(*) as total,
`Time`,`Value` from `my_table` group by `Time`,`Value`) cnt3 ) cnt2
where cnt1.total = cnt2.maxtotal GROUP BY cnt1.`Time`发布于 2016-01-18 00:14:33
考虑一个INNER JOIN来匹配两个派生表子查询,而不是一个与WHERE子句匹配的子查询select语句列表。这一点已在MS Access中进行了测试:
SELECT MaxCountSub.`Time`, CountSub.`Value`
FROM
(SELECT myTable.`Time`, myTable.`Value`, Count(myTable.`Value`) AS CountOfValue
FROM myTable
GROUP BY myTable.`Time`, myTable.`Value`) As CountSub
INNER JOIN
(SELECT dT.`Time`, Max(CountOfValue) As MaxCountOfValue
FROM
(SELECT myTable.`Time`, myTable.`Value`, Count(myTable.`Value`) AS CountOfValue
FROM myTable
GROUP BY myTable.`Time`, myTable.`Value`) As dT
GROUP BY dT.`Time`) As MaxCountSub
ON CountSub.`Time` = MaxCountSub.`Time`
AND CountSub.CountOfValue = MaxCountSub.MaxCountOfValue发布于 2016-01-18 00:08:52
您可以通过这样的查询来做到这一点:
select time, value
from (select value, time from your_table
group by value , time
order by count(time) desc
) temp where temp.value = value
group by valuehttps://stackoverflow.com/questions/34845076
复制相似问题