我的数据看起来像这样:
Table
Group: "A" , Color: "Blue", Count: "400"
Group: "A" , Color: "Green", Count: "3"
Group: "A" , Color: "Yellow", Count: "6"
Group: "A" , Color: "Red", Count: "1"
Group: "B" , Color: "Purple", Count: "243"
Group: "B" , Color: "Green", Count: "2"
Group: "B" , Color: "Yellow", Count: "7"
我如何查询此数据以获得每个组最流行(按计数)的颜色。所以结果会是这样的:
Result
Group: "A", Color: "Blue"
Group: "B", Color: "Purple"
SELECT Group, Color
FROM MyTable t
INNER JOIN (SELECT Group, Max(CAST(Count as int)) as Ct
FROM MyTable
GROUP BY Group) Sub
ON sub.group = t.group
AND sub.ct = t.count
顺便说一句,不要命名字段GROUP
, or COUNT
。它们是关键词,会导致头痛和心碎。
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)