Question

鉴于以下表格,我如何能够建立一个包含“项目”表所有项目清单的坐标表,以及从“栏目”表中每个颜色的列栏,表明每个项目与该项目有何种关系。

如果这根本不明确,请让我知道哪些补充资料将有助于澄清。表格信息和预期结果如下:

www.un.org/Depts/DGACM/index_french.htm

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

<<>strong>colors table:

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

<>strong>item_color table:

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

Desiredkou 查询结果:

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

Thanks, Colin

Answer 1

使用:

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

如果希望与某个项目相关的红 red/蓝./绿化物总数的话,将“彩虹”改为COUNT。

候补成员:

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

Answer 2

你们是不是11g or?

这对新网站(http://www.oracle.com/technology/pub/articles/oracle-database-11g-top-features/11g-pivot.html)来说似乎是一种理想用途。

Answer 3

If you know all the possible colours in advance, you can do it messily but effectively. If you don t know all the possible colours in advance, it is very much harder - you have to run some queries to find out which columns will appear in the result table, and then craft the SQL to create those columns (dynamic SQL).

因此,请您了解下表各栏:

SELECT i.item_name, r.red, b.blue, g.green
  FROM items i
       LEFT JOIN
       (SELECT item_name, COUNT(*) AS red
          FROM item_color
         WHERE color_id = 1
         GROUP BY item_name) AS r
       ON i.item_name = r.item_name
       LEFT JOIN
       (SELECT item_name, COUNT(*) AS green
          FROM item_color
         WHERE color_id = 3
         GROUP BY item_name) AS g
       ON i.item_name = g.item_name
       LEFT JOIN
       (SELECT item_name, COUNT(*) AS blue
          FROM item_color
         WHERE color_id = 2
         GROUP BY item_name) AS b
       ON i.item_name = b.item_name

请注意,在这一表述中,我使用了colours table in building the query。替代形式将建立分层,因为(内部)使用彩色名称而不是《家庭环境和健康调查》条款中的代码,加入肤色表。

Answer 4

create table item (id number not null, item_name varchar2(200) not null);
create table color (id number not null, color_name varchar2(200) not null);
create table item_color (item_id number not null, color_id number not null);

insert into item values (1,  item 1 );
insert into item values (2,  item 2 );
insert into item values (3,  item 3 );


insert into color values (1,  red );
insert into color values (2,  blue );
insert into color values (3,  green );

insert into item_color values (1, 1);
insert into item_color values (1, 3);
insert into item_color values (2, 2);
insert into item_color values (2, 3);
insert into item_color values (3, 2);

commit;

然后选择:

select * from
(
select
  i.item_name
  , c.color_name
from
  item i
  , color c
  , item_color ic
where
  ic.item_id = i.id
  and ic.color_id = c.id
) pivot (
  count(color_name) cnt
  for color_name in ( red ,  blue ,  green )
);

说明:

item 1    1    0    1
item 2    0    1    1
item 3    0    1    0

如果你事先不知道肤色清单,你可以先选择彩色表,然后以动态方式(如<条码>为颜色——名称(从颜色中选取),否则,你可以使用<条码>pivot xml,并处理结果:

select * from
(
select
  i.item_name
  , c.color_name
from
  item i
  , color c
  , item_color ic
where
  ic.item_id = i.id
  and ic.color_id = c.id
) pivot xml (
  count(color_name) cnt
  for color_name in (any)
)

说明:

item 1  <PivotSet><item><column name = "COLOR_NAME">green</column><column name = "CNT">1</column></item><item><column name = "COLOR_NAME">red</column><column name = "CNT">1</column></item></PivotSet>
item 2  <PivotSet><item><column name = "COLOR_NAME">blue</column><column name = "CNT">1</column></item><item><column name = "COLOR_NAME">green</column><column name = "CNT">1</column></item></PivotSet>
item 3  <PivotSet><item><column name = "COLOR_NAME">blue</column><column name = "CNT">1</column></item></PivotSet>

友情链接