Retail Stores数据库：Hub列仅显示单次Hub1的实现方案问询

Got it, let's work through this problem. The goal is to keep all your existing column results exactly as they are, while making sure the Hub column only displays "hub1" once. Here's how to adjust your query:

Approach #

Your original query groups results by store ID and account ID, so each row is tied to a specific account entry. To make "hub1" appear only once, we'll:

1. Wrap your existing query in a subquery to preserve all core calculations for the required columns.
2. Add a window function to assign a unique row number to each result row, following your original sort order.
3. Use a CASE statement to show "hub1" only on the first row (tweak this if you need it on a specific row), and leave the Hub column empty/NULL for all other rows.

Modified SQL Query #

SELECT 
    `# of stores`,
    name,
    -- Show "hub1" only on the first row; use '' instead of NULL if you prefer a blank
    CASE WHEN rn = 1 THEN 'hub1' ELSE NULL END AS hub,
    `# of orders`,
    `Total Order Size`,
    `# of boxes`,
    latitude,
    longitude
FROM (
    -- Your original query logic remains fully intact, plus a row number for filtering
    SELECT 
        s.id AS `# of stores`, 
        s.name, 
        COUNT(o.id) AS `# of orders`, 
        SUM(total_product_cost) AS `Total Order Size`, 
        SUM(o.no_of_boxes) AS `# of boxes`, 
        s.point_y AS latitude, 
        s.point_x AS longitude,
        -- Assign row numbers using your original sort order
        ROW_NUMBER() OVER (ORDER BY a1.id ASC) AS rn
    FROM `order` AS o 
    JOIN store_warehouse_shipper AS sws ON sws.id = o.associate_id 
    JOIN store AS s ON s.id = sws.store_id 
    JOIN store_salesperson AS ss ON ss.store_id = s.id 
    JOIN account AS a1 ON a1.id = ss.salesperson_account_id 
    LEFT JOIN store_salesperson AS ss1 ON ss1.store_id = s.id AND ss1.id > ss.id 
    WHERE DATE(delivered_by) = CURDATE() AND a1.display_name LIKE '%hub%' 
    GROUP BY s.id, a1.id 
) AS subquery
ORDER BY rn ASC -- Match your original sort order

Key Details #

• All your critical columns (# of stores, name, # of orders, etc.) will return identical results to your original query—no changes to their calculations.
• If you want "hub1" to appear on a different row (not the first), adjust the ORDER BY inside the ROW_NUMBER() function (e.g., ORDER BY s.id DESC to pick the last store row).
• Swap NULL with '' in the CASE statement if you want a blank value instead of NULL for non-hub1 rows.

内容的提问来源于stack exchange，提问作者Larry Mark Alcantara