单个查询中的Count和max（id）

本文介绍了单个查询中的Count和max（id）的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个如下所示的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 qt, 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 和派生的 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