mysql 状态选择_MYSQL选择状态按分组顺序排序(MYSQL Select statment Order By with Group By)...

MYSQL选择状态按分组顺序排序(MYSQL Select statment Order By with Group By)

我有以下简单的SQL语句

SELECT id, name, value_name, value_id

FROM table

GROUP BY id

ORDER BY value_id DESC

分组时我想得到元组的value_name和value_id，其中value_id是最大的。 我的方式是获得最小的价值。 例如

1, name1, valuename, 3 (where i know that there is a value_id of 5)

你能帮忙吗？

I have the following simple SQL statment

SELECT id, name, value_name, value_id

FROM table

GROUP BY id

ORDER BY value_id DESC

when grouping I would like to get the value_name and value_id of the tuple where the value_id is the biggest. The way it is i am getting the smallest value. For example

1, name1, valuename, 3 (where i know that there is a value_id of 5)

Can you please help?

原文：https://stackoverflow.com/questions/2884688

更新时间：2019-11-11 05:23

最满意答案

更改, value_id到, MAX(value_id) AS value_id在字段列表中...

实际上，现在我看着它，我想你想要的是这样的：

SELECT a.id, a.name, MAX(b.value_id) AS value_id, (SELECT b.value_name FROM table AS b WHERE b.id = a.id AND b.value_id = MAX(a.value_id)) AS value_name

FROM table AS a

GROUP BY a.id, a.name

ORDER BY value_id DESC

问题是该表未完全标准化。 因此，您有多个id，名称相同的行，但每个行都有唯一的value_id和value_name。 因此，获取与value_id关联的value_name的唯一方法是通过子查询(我在那里做的)或连接...

change , value_id to , MAX(value_id) AS value_id in the field list...

Actually, now that I look at it, I think what you want is something like this:

SELECT a.id, a.name, MAX(b.value_id) AS value_id, (SELECT b.value_name FROM table AS b WHERE b.id = a.id AND b.value_id = MAX(a.value_id)) AS value_name

FROM table AS a

GROUP BY a.id, a.name

ORDER BY value_id DESC

The problem is that the table isn't fully normalized. So you have multiple rows with id and name being the same, but each has a unique value_id and value_name. So the only ways to get the value_name associated with the value_id is either through a subquery (what I did there), or a join...

2010-05-21

相关问答

更改, value_id到, MAX(value_id) AS value_id在字段列表中... 实际上，现在我看着它，我想你想要的是这样的： SELECT a.id, a.name, MAX(b.value_id) AS value_id, (SELECT b.value_name FROM table AS b WHERE b.id = a.id AND b.value_id = MAX(a.value_id)) AS value_name

FROM table AS a

GROUP BY

...

select wp_posts.* from wp_posts where wp_posts.post_status='publish'and wp_posts.post_type='post' group by wp_posts.post_author having wp_posts.post_date = MAX(wp_posts.post_date) /* ONLY THE LAST POST FOR EACH AUTHOR */ order by wp_posts.post_date de

...

一个简单的解决方案是首先将查询包含在具有ORDER语句的子选择中，然后再应用GROUP BY： SELECT * FROM (

SELECT `timestamp`, `fromEmail`, `subject`

FROM `incomingEmails`

ORDER BY `timestamp` DESC

) AS tmp_table GROUP BY LOWER(`fromEmail`)

这与使用连接类似，但看起来更好。 在SELECT中使用GROUP BY子句的

...

在子查询中使用ORDER BY不是解决此问题的最佳方法。 获取作者max(post_date)的最佳解决方案是使用子查询返回最大日期，然后在post_author和最大日期post_author其加入到表中。 解决办法应该是： SELECT p1.*

FROM wp_posts p1

INNER JOIN

(

SELECT max(post_date) MaxPostDate, post_author

FROM wp_posts

WHERE post_status='pu

...

你得到1行而不是6行的原因是你是通过语法滥用mysql独特的轻松组(我不会详细介绍，但你不是所有非聚合列的分组 - 通常是语法错误)。 基本上你想要最早的两行连接，你可以根据自己的意愿弯曲这个独特的功能： select a.*, b.cost AS future_cost, b.start_date AS future_date

from (select * from (select * from testdata order by start_date) x group by pattern)

...

从手册 ： 如果使用GROUP BY，则输出行将根据GROUP BY列进行排序，就好像您具有相同列的ORDER BY一样。 为了避免GROUP BY生成的排序开销，添加ORDER BY NULL： SELECT a, COUNT(b) FROM test_table GROUP BY a ORDER BY NULL;

不推荐依赖于隐式GROUP BY排序(即，在没有ASC或DESC指示符的情况下进行排序)。 要生成给定的排序顺序，请对GROUP BY列使用显式ASC或DESC指示符，或者提供OR

...

这可能工作(未测试)： SELECT CASE

WHEN name = 'ciao' THEN 'ciao'

ELSE 'not ciao'

END as NAME,

COUNT(*)

FROM table

GROUP BY CASE

WHEN name = 'ciao' THEN 'ciao'

ELSE 'not ciao'

END

OR在MySQL中 S

...

您不能在group by子句中使用参数，您可以使用参数构建查询字符串。 像这样的东西。 DECLARE @group nvarchar(50) = N'Age_Band'; --set col name

DECLARE @SQLString nvarchar(500);

SET @SQLString =

N'SELECT AVG(Loan_Amount) as Avg_Loan_Amount

,count(Loan_Amount)

,SUM(Loan_Amount) as Su

...

你可以使用MAX()聚合函数和GROUP BY一样 select `user`, max(permission_a) as permission_a,

max(permission_b) as permission_b

from permission

group by `user`;

You can probably use MAX() aggregate function with GROUP BY like select `user`, max(permission_a) as permis

...

试试： select sales_people.person_id, last_name, first_name, sales_region.Region_id, trim(sales_region.name) AS 'Region Name'

from sales_region

inner join sales_people_region on sales_people_region.region_id = sales_region.region_id

inner jo

...