MySQL GROUP by name group to the maximum value of one, do not understand AH seek doubts. Solution Ideas
Source: Internet
Author: User
MySQL GROUP by name Group to get the most value one, do not understand Ah, ask for doubts.
Select A.* from Test a where 1 > (select COUNT (*) from test where name = A.name and val > A.val)
SQL statement, the field that displays the largest Val value by name group
Issue: SELECT COUNT (*) from test where name = A.name and val > a.val subquery is not a numeric value detected? The SQL statement doesn't have to be.
Select A.* from Test a where 1 >1? But this logic is obviously wrong, what is the right logic? How are the outer and inner layers compared? It's not enough to understand the IQ.
------to solve the idea----------------------
When the Val maximum is obtained, COUNT (*) is 0, 1>0 is not exactly satisfied
------to solve the idea----------------------
When the a.val is the maximum value
Val > A.val not set, (SELECT COUNT (*) from test where name = A.name and val > A.val) return 0 (no qualifying record)
1 > (select COUNT (*) from test where name = A.name and val > A.val)
Established, the record was elected
You already know, but you didn't get around bend.
Select A.* from Test a where 1 > Count
Count is the number of records that are greater than a.val, and only conut equals 0 when it does not exist. Expression 1 > 0 established
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.