本文介绍了单个查询中的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, name4Ex。对于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 $ c加入这些结果,你得到名称 $ 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)
发布评论