sql中如何计算计数？

Question

我有下表：

memberid  
2
2
3
4
3

...我想要以下结果：

memberid    count
2           2
3           1    ---Edit by gbn: do you mean 2?
4           1

我试图使用：

  SELECT MemberID, 
         COUNT(MemberID) 
    FROM YourTable 
GROUP BY MemberID

...但现在我想找到哪个记录具有最大计数。 IE：

memberid   count
2          2

Answer 1

这应该可以实现不需要子选择的技巧：

select top 1 memberid, COUNT(*) as counted 
from members
group by memberid
order by counted desc

Answer 2

可以很容易地完成：

SELECT TOP 1 MemberId, COUNT(*) FROM YourTable GROUP BY MemberId ORDER By 2 DESC

Answer 3

我相信原始发帖者要求 2 个结果集。

我知道获得此信息的唯一方法（在 SQL Server 中）是将原始记录转储到临时表中，然后对其执行 SELECT 和 MAX。我确实欢迎需要更少代码的答案！

-- Select records into a temp table
SELECT
    Table1.MemberId
    ,CNT = COUNT(*)
INTO #Temp
FROM YourTable AS Table1
GROUP BY Table1.MemberId
ORDER BY Table1.MemberId

-- Get original records
SELECT * FROM #Temp

-- Get max. count record(s) 
SELECT 
    Table1.MemberId
    ,Table1.CNT
FROM #Temp AS Table1
INNER JOIN (
    SELECT CNT = MAX(CNT)
    FROM #Temp
) AS Table2 ON Table2.CNT = Table1.CNT

-- Cleanup 
DROP TABLE #Temp

Answer 4

这个查询怎么样：

SELECT TOP 1 MemberID, 
       COUNT(MemberID) 
FROM YourTable 
GROUP BY MemberID
ORDER by count(MemberID) desc

Answer 5

选择计数（列名）
来自你的表；

Answer 6

您需要使用子选择：

SELECT MemberID, MAX(Count) FROM
    (SELECT MemberID, COUNT(MemberID) Count FROM YourTable GROUP BY MemberID)
GROUP BY MemberID

需要第二个分组依据来返回计数和 MemberID。

Answer 7

SELECT memberid, COUNT(*) FROM TheTable GROUP BY memberid

尽管如此，它不会满足您想要的输出，因为您有两次“memberid = 3”。

编辑：在问题更新后...

SELECT TOP 1 WITH TIES    --WITH TIES will pick up "joint top". 
    memberid, COUNT(*)
FROM
    TheTable 
GROUP BY 
    memberid
ORDER BY
    COUNT(*) DESC

Answer 8

SELECT MemberID, COUNT(MemberID) FROM YourTable GROUP BY MemberID

Answer 9

如果最大值并列（或更多）怎么办？您想显示其中一项还是全部？

这就是我要做的事情，

SELECT memberid, COUNT(1)
FROM members
GROUP BY memberid
HAVING COUNT(1) = (
            SELECT MAX(result.mem_count)
            FROM (  
                SELECT memberid, COUNT(1) as mem_count
                FROM members
                GROUP BY memberid
            ) as result
          )

但我希望看到一种更有效的方法。

Answer 10

这样做：

SELECT memberid, COUNT(memberid) AS [count] FROM [Table] GROUP BY memberid