SQL Server查询为不同ref分配唯一颜色值的实现方法咨询

Absolutely feasible! This is a common requirement for data visualization, and SQL gives you a few straightforward ways to pull it off—perfect for your ~30 unique ref values. Let’s break down the options:

100% yes. Whether you want curated, visually distinct colors or auto-generated values, SQL has the tools to map each unique ref to a one-of-a-kind color. With only ~30 values, you won’t run into scalability issues with any of the approaches below.

Predefined Color Palette (Recommended for Control) #

If you want consistent, aesthetically pleasing colors, define a palette of ~30 RGB values and map each unique ref to a color using a row number. This ensures no two refs get the same color (and you can reuse the palette if ref counts grow beyond 30 by looping colors).

Example (PostgreSQL/MySQL compatible with minor tweaks): #

WITH distinct_refs AS (
    -- Get unique refs and assign a sequential number
    SELECT DISTINCT ref,
           ROW_NUMBER() OVER (ORDER BY ref) AS ref_rank
    FROM your_table
),
color_palette AS (
    -- Define your ~30 RGB colors here
    SELECT rgb_color,
           ROW_NUMBER() OVER (ORDER BY rgb_color) AS color_rank
    FROM (VALUES
        ('rgb(255,99,71)'), ('rgb(255,159,64)'), ('rgb(255,205,86)'),
        ('rgb(75,192,192)'), ('rgb(54,162,235)'), ('rgb(153,102,255)'),
        ('rgb(255,159,243)'), ('rgb(205,92,92)'), ('rgb(107,142,35)'),
        ('rgb(143,188,143)'), ('rgb(176,224,230)'), ('rgb(106,90,205)'),
        -- Add 18 more colors to reach ~30 total
        ('rgb(210,105,30)'), ('rgb(240,230,140)'), ('rgb(100,149,237)'),
        ('rgb(123,104,238)'), ('rgb(255,192,203)'), ('rgb(188,143,143)')
    ) AS colors(rgb_color)
)
-- Map each ref to a color
SELECT dr.ref, cp.rgb_color AS color
FROM distinct_refs dr
JOIN color_palette cp 
  -- For exact 1:1 mapping (if ref count <= 30)
  ON dr.ref_rank = cp.color_rank
  -- Uncomment below to loop colors if ref count exceeds 30
  -- ON (dr.ref_rank - 1) % (SELECT COUNT(*) FROM color_palette) = cp.color_rank - 1;

Hash-Generated Colors (No Maintenance Needed) #

If you don’t care about curating colors, use a hash function to generate RGB values directly from the ref string. This auto-generates unique colors (with near-zero collision chance for 30 values) without needing a predefined palette.

Example (MySQL): #

SELECT DISTINCT ref,
       CONCAT('rgb(', 
              FLOOR(CRC32(ref) % 256), ',',
              FLOOR((CRC32(ref) >> 8) % 256), ',',
              FLOOR((CRC32(ref) >> 16) % 256), ')') AS color
FROM your_table;

Example (PostgreSQL): #

SELECT DISTINCT ref,
       CONCAT('rgb(',
              ('x' || SUBSTRING(MD5(ref) FROM 1 FOR 2))::int, ',',
              ('x' || SUBSTRING(MD5(ref) FROM 3 FOR 2))::int, ',',
              ('x' || SUBSTRING(MD5(ref) FROM 5 FOR 2))::int, ')') AS color
FROM your_table;

Decimal Color Format Option #

If you need a single decimal value instead of RGB strings, you can convert RGB to a decimal integer (calculated as R * 65536 + G * 256 + B) or use a hash’s hex value directly converted to decimal.

Predefined Decimal Palette Example: #

WITH distinct_refs AS (
    SELECT DISTINCT ref,
           ROW_NUMBER() OVER (ORDER BY ref) AS ref_rank
    FROM your_table
),
color_palette AS (
    SELECT decimal_color,
           ROW_NUMBER() OVER (ORDER BY decimal_color) AS color_rank
    FROM (VALUES
        (16711680), -- Red: rgb(255,0,0)
        (16753920), -- Orange: rgb(255,165,0)
        (16776960), -- Yellow: rgb(255,255,0)
        (65280), -- Green: rgb(0,255,0)
        -- Add ~26 more decimal color values
        (15658734) -- Pink: rgb(255,192,203)
    ) AS colors(decimal_color)
)
SELECT dr.ref, cp.decimal_color AS color
FROM distinct_refs dr
JOIN color_palette cp ON dr.ref_rank = cp.color_rank;

Hash-Generated Decimal Example: #

SELECT DISTINCT ref,
       -- Convert first 6 hex chars of MD5 hash to decimal
       ('x' || SUBSTRING(MD5(ref) FROM 1 FOR 6))::bigint AS color
FROM your_table;

• Use the predefined palette if you need consistent, visually distinct colors for reporting/visualization.
• Use hash generation if you want a set-it-and-forget-it solution with minimal code.
• Both approaches work seamlessly for 30 unique ref values—no performance or uniqueness issues to worry about.

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