sql-ex平台SQL练习问题咨询：Painting数据库表结构相关疑问

Hey there! Let's walk through how to tackle SQL queries for this Painting database. First, let's get crystal clear on the table structures since that's the backbone of every query we'll write:

核心表结构说明 #

• utQ: 存储初始为黑色（未上色状态）的方块信息
  • Q_ID: 方块唯一标识（int类型）
  • Q_NAME: 方块名称（varchar(35)）

  注：黑色不属于有效上色颜色，仅红(R)、绿(G)、蓝(B)视为已上色的有效颜色

• utV: 存储喷漆罐信息
  • V_ID: 喷漆罐唯一标识（int类型）
  • V_NAME: 喷漆罐名称（varchar(35)）
  • V_COLOR: 喷漆颜色（char(1)，取值为R/G/B等）
• utB: 存储喷漆操作记录
  • B_Q_ID: 被喷漆的方块ID（关联utQ.Q_ID）
  • B_V_ID: 使用的喷漆罐ID（关联utV.V_ID）
  • B_VOL: 喷漆体积（tinyint类型）
  • B_DATETIME: 喷漆操作的时间（datetime类型）

示例1：查询每个方块的最终上色状态 #

这是个高频需求：要知道每个方块当前的颜色，没有喷漆记录的话就是初始的未上色（黑色）。我们需要先找到每个方块最新的喷漆记录，再关联喷漆罐表拿到颜色：

SELECT 
    q.Q_ID,
    q.Q_NAME,
    COALESCE(v.V_COLOR, '黑色（未上色）') AS FINAL_COLOR
FROM utQ q
LEFT JOIN (
    -- 子查询获取每个方块的最新喷漆记录
    SELECT B_Q_ID, MAX(B_DATETIME) AS LATEST_TIME
    FROM utB
    GROUP BY B_Q_ID
) latest_b ON q.Q_ID = latest_b.B_Q_ID
LEFT JOIN utB b ON latest_b.B_Q_ID = b.B_Q_ID AND latest_b.LATEST_TIME = b.B_DATETIME
LEFT JOIN utV v ON b.B_V_ID = v.V_ID
ORDER BY q.Q_ID;

示例2：统计每种有效颜色的总喷漆量 #

如果需要统计红、绿、蓝三种颜色分别被用了多少体积，可以通过关联喷漆记录和喷漆罐表，过滤有效颜色后分组求和：

SELECT 
    v.V_COLOR,
    v.V_NAME,
    SUM(b.B_VOL) AS TOTAL_VOLUME_USED
FROM utB b
JOIN utV v ON b.B_V_ID = v.V_ID
WHERE v.V_COLOR IN ('R', 'G', 'B') -- 仅统计有效颜色
GROUP BY v.V_COLOR, v.V_NAME
ORDER BY TOTAL_VOLUME_USED DESC;

示例3：找出从未被喷漆的方块 #

要筛选出没有任何喷漆记录的方块，用左连接后判断喷漆记录是否为空即可：

SELECT 
    q.Q_ID,
    q.Q_NAME
FROM utQ q
LEFT JOIN utB b ON q.Q_ID = b.B_Q_ID
WHERE b.B_Q_ID IS NULL
ORDER BY q.Q_ID;

If you're stuck on a specific query requirement (like tracking color changes over time, or finding the most used spray can), just share the exact problem statement and I can help tailor a solution for you!

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