MySQL SQL 计算多列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28228133/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Count multiple columns
提问by jason brown
I have a query that returns multiple columns currently, below is an example.
我有一个当前返回多列的查询，下面是一个示例。
What I am looking to do is count the number of times ClientID and ServerID pair up. I am looking to get the ServerID that most serves that Client.
我想要做的是计算 ClientID 和 ServerID 配对的次数。我正在寻找最适合该客户端的 ServerID。
ClientID, ServerID, Last.Date
1 AB 1/27/2015
2 DS 1/27/2015
1 JK 1/27/2015
1 AB 1/24/2015
2 PD 1/24/2015
2 DS 1/23/2015
What I want:
我想要的是：
ClientID ServerID Last.Date ConnectionCount
ClientID ServerID Last.Date ConnectionCount
1 AB 1/27/2015 2
2 DS 1/27/2015 2
I know I need to use the Count function, but the issue is Count(ClientID+ServerID) is not valid and I am not sure how count based on just two columns.
我知道我需要使用 Count 函数，但问题是 Count(ClientID+ServerID) 无效，我不确定如何仅基于两列进行计数。
Thanks ahead of time
提前致谢
~Jason
~杰森
采纳答案by radar
you can use group byand get maximum connections and then further do one more group byon the client to get the server that serves the most
您可以使用group by并获得最大连接数，然后group by在客户端上再做一个，以获得服务最多的服务器
SELECT clientId, serverId,
max(connectionCount) as ConnectionCount, LastDate
from
(
select clientId, ServerID,
count(*) as ConnectionCount,
max(LastDate) as LastDate
from Table1
group by clientId, ServerID
) t
group by clientId
回答by Barmar
You can GROUP BYmultiple columns, to get the count of each combination.
您可以GROUP BY多列，以获取每个组合的计数。
SELECT ClientID, ServerID, MAX(`Last.Date`) AS `Last.Date`, COUNT(*) AS ConnectionCount
FROM YourTable
GROUP BY ClientID, ServerID
回答by Brent Worden
You can use what some call a self-exclusion joinwhere you perform an outer join on a table to itself where you exclude all but one record for each set of matching records.
您可以使用某些人所说的自排除连接，在该连接中您对表执行外部连接到自身，其中为每组匹配记录排除除一条记录之外的所有记录。
To do the join, you must first come up with the table that is involved in the join. For your case, the following select statement will serve as the table:
要进行联接，您必须首先提出联接中涉及的表。对于您的情况，以下选择语句将用作表：
SELECT
ClientID
, ServerID
, count(*) as ConnectionCount
FROM Table1
GROUP BY ClientID, ServerID;
Using your example data, this will result in the follow result set:
使用您的示例数据，这将产生以下结果集：
ClientID ServerID LastDate ConnectionCount
1 AB 1/27/2015 2
2 DS 1/27/2015 2
1 JK 1/27/2015 1
2 PD 1/24/2015 1
With the above table, we construct the self outer join:
有了上表，我们构造了自外连接：
SELECT T1.* FROM
( SELECT
ClientID
, ServerID
, max(LastDate) as LastDate
, count(*) as ConnectionCount
FROM Table1
GROUP BY ClientID, ServerID
) T1
LEFT JOIN
( SELECT
ClientID
, ServerID
, count(*) as ConnectionCount
FROM Table1
GROUP BY ClientID, ServerID
) T2
ON (...some on clause...)
WHERE ...some where clause...
;
The ON clause is based on the fact you want to determine the maximum server connection count for each client. To do this you need to compare the connections counts in T1 to the connections counts in T2 by ClientId, or ON (T1.ClientId = T2.ClientId). But that is not a sufficient ON clause to return only the client, server combination with the maximum connections. To do that, you must exclude the records from the join where the T1 connection count is bigger than the T2 connection count for all records. Stated in another way is there exists no T2 row such that the T1 connection count is never smaller than T2 connection count.
ON 子句基于您要确定每个客户端的最大服务器连接数这一事实。为此，您需要通过 ClientId 或 将 T1 中的连接计数与 T2 中的连接计数进行比较ON (T1.ClientId = T2.ClientId)。但这不是一个足够的 ON 子句来仅返回具有最大连接数的客户端、服务器组合。为此，您必须从连接中排除所有记录的 T1 连接计数大于 T2 连接计数的记录。换句话说，不存在 T2 行，因此 T1 连接计数永远不会小于 T2 连接计数。
With that logic you can come up with the clauses of:
有了这个逻辑，你可以想出以下条款：
ON (T1.ClientId = T2.ClientId AND T1.ConnectionCount < T2.ConnectionCount)
WHERE T2.ClientId IS NULL
Putting everything together, the final SQL query is:
将所有内容放在一起，最终的 SQL 查询是：
SELECT T1.* FROM
( SELECT
ClientID
, ServerID
, max(LastDate) as LastDate
, count(*) as ConnectionCount
FROM Table1
GROUP BY ClientID, ServerID
) T1
LEFT JOIN
( SELECT
ClientID
, ServerID
, count(*) as ConnectionCount
FROM Table1
GROUP BY ClientID, ServerID
) T2
ON (T1.ClientId = T2.ClientId AND T1.ConnectionCount < T2.ConnectionCount)
WHERE T2.ClientId IS NULL
;
回答by Gnen Chantha
select ClientID,ServerID,Last.Date,count(ServerID) as count
From your table
group by ServerID
