如何加入重复列 [英] How do I join duplicate columns

问题描述

Hello,

I have two tables named description and price with same duplicate ids and model.

Description table:
ID  DESCR MODEL
  1  tyre   A
  2  tyre   A
  1  tube   A
  1  bumper A
  2  pipe   A

Price table:
ID Price MODEL
  1   54    A
  1   23    A
  1   35    A
  2   55    A
  2   35    A

I want to join like this:

ID  DESCR Price MODEL
  1   tyre  54   A
  1   tube  23   A
  1   bumper 35  A
  2   tyre   55  A
  2   pipe   35  A

I tried with inner join but several combinations of duplicates comes as a result. Is there any way to handle this?

Thanks in advance.

推荐答案

试试这个：

Try this:

select d.id, d.descr, p.price, d.model from
(
select id,  descr, model,
ROW_NUMBER() OVER(PARTITION BY id ORDER BY descr DESC) row
from description
) d
inner join
(select id,  price,
ROW_NUMBER() OVER(PARTITION BY id ORDER BY id) row
from price
 ) p
 on d.row=p.row where d.id=p.id

Hello



我看到主键定义有问题。



我建议你看看这个：

http://en.wikipedia.org/wiki/Unique_key [ ^ ]



我建议您定义一个具有自动增量的字段主键（如果您决定使用数字字段），如果您想约束某种字段组合，我建议您使用唯一键约束（除主键之外） 。



定义主键或唯一键时，您可以在另一个表中引用该表。



我想看看你的Da ta字典，了解你如何定义和使用这些字段以及如何约束它们。



如果你还有其他任何问题，请随意提问。



最诚挚的问候。

Aner

Hello

I see an issue with the definition of the primary key.

I recommend you to take a look at this:
http://en.wikipedia.org/wiki/Unique_key[^]

I recommend you to define a single field Primary key with autoincrement (if you decide to use a numeric field), and if you want to constraint some kind of field combination, I recommend you to use Unique Key constraint (additional to Primary Key).

When you define the primary or unique key, you will be able to reference the table on another table.

I'd like to see your Data Dictionary to see how you define and use this fields and how you constraint them.

If you still have any other question, please fell free to ask.

Best Regards.
Aner

这篇关于如何加入重复列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！