多价格商品价格展示开发需求：单价或最低价-最高价显示

Hey there! I know you've spent 2 days digging into this, so let's break down a straightforward solution using SQL—since you're working with database tables, this is the most direct way to get the output you need.

Core Logic #

The key is to group prices by each product, check how many unique prices exist, then either show the single price or a range of the lowest and highest prices.

Example SQL Query #

Let's assume your tables are structured like this (adjust based on your actual schema):

• products: Stores product details (e.g., product_id, product_name)
• prices: Links to products and holds individual prices (e.g., price_id, product_id, price)
• Your third table: Could be something like skus or categories—we'll include a placeholder join for it, but the core logic stays the same.

SELECT
    p.product_id,
    p.product_name,
    -- Switch between single price and range display
    CASE
        WHEN COUNT(DISTINCT pr.price) = 1 THEN CONCAT('$', FORMAT(pr.price, 2))
        ELSE CONCAT('$', FORMAT(MIN(pr.price), 2), ' — $', FORMAT(MAX(pr.price), 2))
    END AS display_price
FROM products p
-- Join to get all prices linked to each product
JOIN prices pr ON p.product_id = pr.product_id
-- Add your third table join here if needed (example below)
-- JOIN skus s ON p.product_id = s.product_id
GROUP BY p.product_id, p.product_name;

Breakdown of the Query #

• COUNT(DISTINCT pr.price): Counts unique prices per product (use COUNT(pr.price) instead if you want to count all price entries, even duplicates)
• CASE statement: Toggles between single price and range format based on the price count
• MIN(pr.price) & MAX(pr.price): Grabs the lowest and highest prices for the range display
• FORMAT(): Ensures prices show with 2 decimal places (adjust for your database):
  • For PostgreSQL: Replace FORMAT(pr.price, 2) with TO_CHAR(pr.price, 'FM999999.00')
  • For SQL Server: Use FORMAT(pr.price, 'N2') to get two decimal places, then add the $ symbol manually

Example Output #

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