将具有空值的几行合并为一个 [英] Combine several rows with null values into one

问题描述

我的选择语句有点问题.我需要从表中为每种电话选择最新记录，所以我尝试以下代码:

I have a little problem with my select statement. I need to select the latest record for each type of phones from my table, so I try this code:

CREATE TABLE contacts(
tel_number VARCHAR2(14),
tel_type NUMBER,
row_id_con NUMBER,
record_id NUMBER
);

CREATE TABLE orders(
order_id VARCHAR2(9),
row_id_ord NUMBER
);

INSERT INTO contacts VALUES('444-444-444', 1, 1, 1);
INSERT INTO contacts VALUES('22-22-22', 2, 1, 2);
INSERT INTO contacts VALUES('555-555-555', 1, 1, 3);

INSERT INTO orders VALUES('111111111', 1);

SELECT ord.order_id, 
       DECODE(con.tel_type, 1, con.tel_number) AS number1,
       DECODE(con.tel_type, 2, con.tel_number) AS number2
FROM  (SELECT MAX(c.record_id) as max_row_id
       FROM contacts c, orders o
       WHERE c.row_id_con = o.row_id_ord
       and o.order_id = '111111111'
       GROUP BY c.tel_type) c, contacts con, orders ord
WHERE  con.row_id_con = ord.row_id_ord 
       and con.record_id = c.max_row_id
GROUP BY ord.order_id, con.tel_type, con.tel_number;

现在的输出是这样的:

order_id   number1      number2
111111111  null         22-22-22
111111111  555-555-555  null

但是我需要这样的东西:

But I need something like that:

order_id   number1      number2
111111111  555-555-555  22-22-22

我该怎么办?

推荐答案

更多解决方案:

SELECT o.order_id,
       max( case c.tel_type when 1 then tel_number end ) 
           KEEP ( DENSE_RANK LAST ORDER BY row_id_con NULLS FIRST ) as number1,
       max( case c.tel_type when 2 then tel_number end ) 
           KEEP ( DENSE_RANK LAST ORDER BY row_id_con NULLS FIRST ) as number2
FROM orders o
LEFT JOIN contacts c ON c.row_id_con = o.row_id_ord
GROUP BY o.order_id
;

这篇关于将具有空值的几行合并为一个的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！