MySQL技术问询：查询store表全列且去除store列重复值并按views降序

Got it, let's break down your problem and fix this query properly. Your original statement SELECT * FROM store order by views DESC sorts results by views but doesn't handle duplicate store values. To get all columns while ensuring each store appears only once (and still sort by views descending), here are the most reliable approaches:

Recommended Approach: Using Window Functions (MySQL 8.0+) #

This method is clean, predictable, and lets you explicitly choose which row to keep for each unique store (we'll prioritize the row with the highest views here):

SELECT *
FROM (
    SELECT *,
           -- Assign a unique row number to each row in the same store group, sorted by views descending
           ROW_NUMBER() OVER (PARTITION BY store ORDER BY views DESC) AS row_num
    FROM store
) AS ranked_stores
-- Keep only the top row (highest views) for each store
WHERE row_num = 1
-- Final sort of the unique store rows by views descending
ORDER BY views DESC;

Why this works: #

• PARTITION BY store groups rows with identical store values together.
• ORDER BY views DESC within the window ensures the row with the highest views gets row_num = 1.
• Filtering for row_num = 1 guarantees one row per store, then we sort the final results as needed.

Alternative for Older MySQL Versions (Pre-8.0) #

If you're using a MySQL version that doesn't support window functions, use a subquery to find the maximum views per store, then join back to the original table to get full row data:

SELECT s.*
FROM store s
INNER JOIN (
    -- Get the highest views value for each unique store
    SELECT store, MAX(views) AS max_views
    FROM store
    GROUP BY store
) AS store_max ON s.store = store_max.store AND s.views = store_max.views
ORDER BY s.views DESC;

Note: #

If multiple rows for the same store share the maximum views, this query will return all of them. To get strictly one row per store, add an extra condition (like picking the latest id or another unique column) to narrow it down.

What to Avoid (Unpredictable Results) #

You might see suggestions to use GROUP BY store directly with SELECT *, like this:

-- NOT RECOMMENDED
SELECT *
FROM store
GROUP BY store
ORDER BY views DESC;

This only works if ONLY_FULL_GROUP_BY is disabled (not a best practice), and non-grouped columns (like views, id) will return arbitrary values from the group. The result is unpredictable and can lead to bugs, so stick to the first two methods.

内容的提问来源于stack exchange，提问作者Bensalem