代码之家 › 专栏 › 技术社区 › espresso_coffee

如何基于状态连接表记录?

oracle-sqldeveloper left-join oracle11g oracle

espresso_coffee · 技术社区 · 5 年前

我有一个表,它必须根据状态连接到另一个表。以下是表1数据的示例:

ROWID  STATE 
 34     TX
 56     NY
 67     WA
 89     TX

ROWID   STATE       NAME  
 19      TX     Chuck, Brown
 20      TX     Nick, Johnes

如表2所示,德克萨斯州有两项记录。如果我使用 Left Outer Join 试着这样做:

LEFT OUTER JOIN Table 2 TB2
   ON TB1.STATE = TB2.SATE

34 TX Chuck, Brwon
34 TX Nick, Johnes
89 TX Chuck, Brwon
89 TX Nick, Johnes

每一行都是重复的,因为表1中有两条德克萨斯州的记录。表2中有两条德克萨斯州的记录。我想看到的是:

ROWID   STATE     NAME 1         NAME 2
 34      TX    Chuck, Brwon    Nick, Johnes

基本上我想把两行合并成一行,然后加入表1。我最近开始做这个项目,我们使用Oracle数据库。如果有人知道实现这一目标的好方法,请让我知道。非常感谢。

2 回复 | 直到 5 年前

Sentinel 5 年前

基于示例所需的输出,我将输出限制为表1中的第一个state实例,并按ID对数据透视名称进行排序,尽管这恰好与给定示例数据的name字段的alpha顺序相匹配。

看到这个了吗 SQL Fiddle

Oracle 11g R2架构设置 :

CREATE TABLE Table1
    ("ID" number, "STATE" varchar2(2))
;

INSERT ALL 
    INTO Table1 ("ID", "STATE")
         VALUES (34, 'TX')
    INTO Table1 ("ID", "STATE")
         VALUES (56, 'NY')
    INTO Table1 ("ID", "STATE")
         VALUES (67, 'WA')
    INTO Table1 ("ID", "STATE")
         VALUES (89, 'TX')
SELECT * FROM dual
;

CREATE TABLE Table2
    ("ID" number, "STATE" varchar2(2), "NAME" varchar2(12))
;

INSERT ALL 
    INTO Table2 ("ID", "STATE", "NAME")
         VALUES (19, 'TX', 'Chuck, Brown')
    INTO Table2 ("ID", "STATE", "NAME")
         VALUES (20, 'TX', 'Nick, Johnes')
SELECT * FROM dual
;

问题1

select t1.id
     , t2.state
     , t2.name
  from (select min(id) id, state from table1 group by state) t1
  join table2 t2
    on t1.state = t2.state

Results :

| ID | STATE |         NAME |
|----|-------|--------------|
| 34 |    TX | Nick, Johnes |
| 34 |    TX | Chuck, Brown |

问题2

with t1(id, state) as (
  select min(id) id, state from table1 group by state
), t2 as (   
  select state
       , row_number() over (partition by state order by name) rn
       , name
    from table2
)
select t1.id
     , pvt.*
  from t1
  join t2 pivot (max(name) for rn in (1 name_1,2 name_2)) pvt
    on t1.state = pvt.state

Results :

| ID | STATE |       NAME_1 |       NAME_2 |
|----|-------|--------------|--------------|
| 34 |    TX | Chuck, Brown | Nick, Johnes |

Littlefoot 5 年前

正如你在问题下的一条评论中所说,你会接受一个, 串联名称列,这里有一个选项;我使用CTE创建表,所以需要从第13行开始。

SQL> with
  2  t1 (rid, state) as
  3    (select 34, 'TX' from dual union all
  4     select 56, 'NY' from dual union all
  5     select 67, 'WA' from dual union all
  6     select 89, 'TX' from dual
  7    ),
  8  t2 (rid, state, name) as
  9    (select 19, 'TX', 'Chuck, Brown' from dual union all
 10     select 20, 'TX', 'Nick, Johnes' from dual
 11    ),
 12  -- start here
 13  it2 as
 14    (select state, listagg(name, ' & ') within group (order by null) name
 15     from t2
 16     group by state
 17    )
 18  select min(t1.rid) rid,
 19    t1.state,
 20    it2.name
 21  from t1 join it2 on t1.state = it2.state
 22  and t1.state = 'TX'
 23  group by t1.state, it2.name;

       RID ST NAME
---------- -- ------------------------------
        34 TX Chuck, Brown & Nick, Johnes

SQL>