How can I select rows from MySQL based on max value of a one column and grouping two other columns? The query needs to group by code, group by element_id with where clause (element_id=1.B.1.b) and max(data_value).
如何根据一列的最大值从MySQL中选择行并分组其他两列?查询需要按代码进行分组,使用where子句(element_id= 1.b .1 b)和max(data_value)进行分组。
code element_id data_value
11-1011.00 1.B.1.a 1.33
11-1011.00 1.B.1.b 2.00
11-1012.00 1.B.1.a 2.67
11-1012.00 1.B.1.b 3.67
11-1012.00 1.B.1.C 7.00
I have tried this:
我试过这个:
SELECT * FROM (select
max(`data_value`) as maxID, `code`, `element_id`
from
table_name
GROUP BY `code`, `element_id`
ORDER BY maxID desc) abc
GROUP BY `code`
in that table i have lots of data. its a sample.
在那张桌子上我有很多资料。它的一个示例。
Here you can see more clear:
在这里你可以看到更清晰的:
I need result :
我需要结果:
11-1011.00 1.B.1.b 2.00
11 - 1011.00 - 1.责任。b 2.00
11-1012.00 1.B.1.c 7.00
11 - 1012.00 - 1.责任。c 7.00
this result for without where clasuse.
这个结果在哪里都没有。
if using where clause i want only one result that match with element_id(1.B.1.b) that i have:
如果使用where子句,我只想要一个与element_id(1.B.1.b)匹配的结果:
11-1011.00 1.B.1.b 2.00
11 - 1011.00 - 1.责任。b 2.00
2 个解决方案
#1
2
You can achieve this by
你可以做到这一点。
SELECT a.code, max( a.maxID ) , a.element_id
FROM (
SELECT *
FROM (
SELECT max(data_value) AS maxID, code , element_id
FROM table_name
GROUP BY code , element_id
ORDER BY maxID DESC
) AS abc
GROUP BY code
) AS a
WHERE a.element_id = '1.B.1.b'
GROUP BY a.code
ORDER BY a.code
or try this
或者试试这个
SELECT a.code, a.element_id, a.data_value
FROM interests a
INNER JOIN (
SELECT code, max( data_value ) data_value
FROM table_name
GROUP BY code
)b ON a.code = b.code
AND a.data_value = b.data_value
WHERE a.element_id = '1.B.1.b'
ORDER BY a.code
#2
1
So, you want to group by code
and element_id
, and select the max data_value
. This can be achieved by.
因此,您希望按代码和element_id进行分组,并选择最大data_value。这可以通过。
SELECT
code,
element_id,
max(data_value) AS data_value
FROM table_name
GROUP BY
code,
element_id
Now, if there are a lot more columns within table_name
, you'll want to make the previous a query a sub-query, join to table_name
, and then select your desired columns.
现在,如果table_name中有更多的列,那么您将希望将前面的查询变成子查询,连接到table_name,然后选择所需的列。
SELECT table_name.*
FROM (
SELECT
code,
element_id,
max(data_value) AS data_value
FROM table_name
GROUP BY
code,
element_id
) AS max_record
INNER JOIN table_name
ON table_name.code = max_record.code
AND table_name.element_id = max_record.element_id
AND table_name.data_value = max_record.data_value
#1
2
You can achieve this by
你可以做到这一点。
SELECT a.code, max( a.maxID ) , a.element_id
FROM (
SELECT *
FROM (
SELECT max(data_value) AS maxID, code , element_id
FROM table_name
GROUP BY code , element_id
ORDER BY maxID DESC
) AS abc
GROUP BY code
) AS a
WHERE a.element_id = '1.B.1.b'
GROUP BY a.code
ORDER BY a.code
or try this
或者试试这个
SELECT a.code, a.element_id, a.data_value
FROM interests a
INNER JOIN (
SELECT code, max( data_value ) data_value
FROM table_name
GROUP BY code
)b ON a.code = b.code
AND a.data_value = b.data_value
WHERE a.element_id = '1.B.1.b'
ORDER BY a.code
#2
1
So, you want to group by code
and element_id
, and select the max data_value
. This can be achieved by.
因此,您希望按代码和element_id进行分组,并选择最大data_value。这可以通过。
SELECT
code,
element_id,
max(data_value) AS data_value
FROM table_name
GROUP BY
code,
element_id
Now, if there are a lot more columns within table_name
, you'll want to make the previous a query a sub-query, join to table_name
, and then select your desired columns.
现在,如果table_name中有更多的列,那么您将希望将前面的查询变成子查询,连接到table_name,然后选择所需的列。
SELECT table_name.*
FROM (
SELECT
code,
element_id,
max(data_value) AS data_value
FROM table_name
GROUP BY
code,
element_id
) AS max_record
INNER JOIN table_name
ON table_name.code = max_record.code
AND table_name.element_id = max_record.element_id
AND table_name.data_value = max_record.data_value