连接数据库表的问题 [英] Problem with joining db tables

问题描述

我在联接表(左联接)时遇到问题

I have problem when joining tables (left join)

表1:

id1  amt1
1    100
2    200
3    300

表2:

id2  amt2
1    150
2    250
2    350

我的查询:

select id1,amt1,id2,amt2 from table1
left join table2 on table2.id1=table1.id2

我认为的o/p是:

      id1 amt1  id2 amt2
row1: 1   100   1    150
row2: 2   200   2    250
row3: 2   200   2    350

我希望第3行中的o/p为

I want o/p in row3 as

2 null 2 350

即我要避免重复数据(amt1).

ie I want avoid repetition of data(amt1).

推荐答案

这确实是一个格式问题，最好由 client 处理.例如，在SQL * Plus中，我们可以使用BREAK ....

This really is a formatting issue which is best handled by the client. For instance, in SQL*Plus we can use BREAK ....

SQL> select t1.*, t2.* from t1, t2
  2  /

A   B   C   D           C1
--- --- --- --- ----------
aaa bbb ccc ddd        111
aaa bbb ccc ddd        222

SQL> break on a on b on c on d
SQL> select t1.*, t2.* from t1, t2
  2  /

A   B   C   D           C1
--- --- --- --- ----------
aaa bbb ccc ddd        111
                       222

SQL>

注意:在没有更多信息的情况下，我选择了笛卡尔积.

Note: in the absence of any further information I opted for a Cartesian product.

修改

BREAK是SQL Plus命令，可禁止我们行中的重复列.它仅在SQL Plus客户端中有效.不出所料，《 Oracle SQL * Plus用户指南》中对此进行了介绍.

BREAK is a SQLPlus command, which suppresses duplicate columns in our rows. It only works in the SQLPlus client. As might be expected, it is covered in Oracle's SQL*Plus User Guide. Find out more.

我使用BREAK作为正确处理方式的示例，因为它很干净并且可以正确实现关注点分离.如果您使用的是其他客户端，则需要使用其格式设置功能.可以对SQL进行调整(请参见下文)，但这会削弱查询的实用性，因为我们无法在不想抑制重复值的其他地方重用查询.

I used BREAK as an example of the proper way of doing things, because it is clean and correctly implements the separation of concerns. It you are using a different client you would need to use its formatting capabilities. It is possible to tweak the SQL (see below) but that diminishes the utility of the query, because we cannot reuse the query in other places which don't want to suppress the duplicated values.

无论如何，这是在嵌入式视图中使用ROW_NUMBER()解析函数的一种解决方案.

Anyway, here is one solution which uses the ROW_NUMBER() analytic function in an inline view.

SQL> select * from t1
  2  /

A   B   C   D           ID
--- --- --- --- ----------
eee fff ggg hhh          1
aaa bbb ccc ddd          2

SQL> select * from t2
  2  /

        C1         ID
---------- ----------
       333          2
       111          1
       222          2
       444          2

SQL> select t1_id
  2         , case when rn = 1 then a else null end as a
  3         , t2_id
  4         , c1
  5  from (
  6      select t1.id as t1_id
  7             , row_number () over (partition by t1.id order by t2.c1) as rn
  8             , t1.a
  9             , t2.c1
 10             , t2.id as t2_id
 11      from t1, t2
 12      where t1.id = t2.id
 13      )
 14  order by t1_id, rn
 15  /

     T1_ID A        T2_ID         C1
---------- --- ---------- ----------
         1 eee          1        111
         2 aaa          2        222
         2              2        333
         2              2        444

SQL>

我选择不使用LAG()，因为这仅适用于固定偏移量，并且T2中的行数似乎是可变的.

I chose not to use LAG(), because that only works with fixed offsets, and it seemed likely that the number of rows in T2 would be variable.

这篇关于连接数据库表的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！