帮助从多个表构建SQL查询 [英] Help building a SQL query from multiple tables

问题描述

给出以下表格，我如何构建一个SQL查询，该查询包括项目"表中所有项目的列表，以及颜色"表中每种颜色的列，该列针对列出的每个项目指示项目与什么颜色有关.

Given the following tables, how might I build a SQL query that includes a list of all the items from the "items" table, and a column for each color from the "colors" table that, for each item listed, indicates what colors the item has a relationship with.

如果还是不清楚的话，请告诉我还有哪些其他信息可以帮助您澄清.表信息和所需的SQL结果如下:

If that is unclear at all, please let me know what additional information will help clarify. The table information and desired SQL result are below:

项目表:

id | item_name
1  | 'item 1'
2  | 'item 2'
3  | 'item 3'

颜色表:

id | color_name
1  | 'red'
2  | 'blue'
3  | 'green'

item_color表:

item_id | color_id
1       | 1
1       | 3
2       | 2
2       | 3
3       | 2

所需的SQL查询结果:

item_name | red | blue | green
'item 1'  |  1  | null |   1
'item 2'  | null|   1  |   1
'item 3'  | null|   1  | null

谢谢， 科林

推荐答案

使用:

SELECT item_name,
       MAX(red) 'red',
       MAX(blue) 'blue',
       MAX(green) 'green'
  FROM (SELECT t.item_name,
         CASE
           WHEN c.color_name = 'red' THEN
             1
           ELSE
             NULL
         END 'red',
         CASE
           WHEN c.color_name = 'blue' THEN
             1
           ELSE
             NULL
         END 'blue',
         CASE
           WHEN c.color_name = 'green' THEN
             1
           ELSE
             NULL
         END 'green'       
    FROM ITEMS t
    JOIN ITEM_COLOR ic ON ic.item_id = t.item_id
    JOIN COLORS c ON c.id = ic.color_id)
GROUP BY item_name 

如果要与某项关联的红色/蓝色/绿色总数，请将MAX更改为COUNT.

Change MAX to COUNT if you want the total # of red/blue/green associated to an item.

使用子查询分解的替代方法:

Alternate using Subquery Factoring:

WITH icolors AS (
   SELECT t.item_name,
          CASE
           WHEN c.color_name = 'red' THEN
             1
           ELSE
             NULL
         END 'red',
     CASE
       WHEN c.color_name = 'blue' THEN
         1
       ELSE
         NULL
     END 'blue',
     CASE
       WHEN c.color_name = 'green' THEN
         1
       ELSE
             NULL
     END 'green'       
    FROM ITEMS t
    JOIN ITEM_COLOR ic ON ic.item_id = t.item_id
    JOIN COLORS c ON c.id = ic.color_id)
  SELECT t.item_name,
         MAX(t.red) 'red',
         MAX(t.blue) 'blue',
         MAX(t.green) 'green'
    FROM icolors t
GROUP BY t.item_name

这篇关于帮助从多个表构建SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！