单个查询中的Count和max（id） [英] Count and max (id) in single query

问题描述

我有一个如下所示的mysql表结构：

Hi,

I have a mysql table structure like below:

id, name, city
----------------
1, name1, city1
2, name2, city2
3, name3, city1
4, name4, city1,
5, name5, city2

我想获取特定城市的最大行ID的总行数。



Ex。对于city =city1

输出应该是：

I want to fetch total rows with max row id for a particular city.

Ex. For city = "city1"
Output should be:

totalrows, max(id), name
3, 4, name4

Ex。对于city =city2

输出应该是：

Ex. For city = "city2"
Output should be:

totalrows, max(id), name
2, 5, name5

如何在单个查询中获得此结果？



我尝试了什么：



我确实尝试了很多查询，但无法找到任何查询，最后在此提交问题。

How can I get this result in a single query?

What I have tried:

I did tried my self many query but unable to find any query and finally submit the question here.

推荐答案

您需要使用group by来获取初始数据，然后您可以将所有这些放入子查询中。以下示例在T-SQL中，并针对SQL Server进行了测试，因为此时无法加载MySQL。语法可能有一些小错误，但主体有效：



我用你的数据创建了一个名为 kals84 的表根据问题，此查询返回您需要的值：

You need to use group by to get the initial data and then you can put all of that into a sub-query. The following example is in T-SQL and was tested against SQL Server as I am unable to load MySQL at this time. The syntax may have some minor errors but the principal works:

I created a table called kals84 with your data as per the question and this query returns the values you require:

select q.cnt, q.mx, k.name, q.city
from kals84 k
inner join (select city, count(*) as cnt, max(id) as mx
from kals84 group by city) q on k.city = q.city and q.mx = k.id

这项工作的关键是子查询

The key to this working is the sub-query

select city, count(*) as cnt, max(id) as mx
from kals84 group by city

返回值

city   cnt      mx
city1	3	4
city2	2	5

根据 city 名称 $ c>和派生的 mx 列

select distinct Count(Id) over (partition by city) as "Total_rows" ,Max(id) over (partition by city) as "max_id", name
from temp_cp

这篇关于单个查询中的Count和max（id）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！