在SQL中合并两行 [英] Merge two rows in SQL

问题描述

假设我有一个包含以下信息的表:

Assuming I have a table containing the following information:

FK | Field1 | Field2
=====================
3  | ABC    | *NULL*
3  | *NULL* | DEF

有没有一种方法可以在表上执行选择以获取以下内容

is there a way I can perform a select on the table to get the following

FK | Field1 | Field2
=====================
3  | ABC    | DEF

谢谢

为清楚起见，修正了field2名称

Fix field2 name for clarity

推荐答案

集合函数可以在这里为您提供帮助.聚合函数将忽略NULLs(至少在SQL Server，Oracle和Jet/Access中是如此)，因此您可以使用类似的查询(在SQL Server Express 2008 R2上测试):

Aggregate functions may help you out here. Aggregate functions ignore NULLs (at least that's true on SQL Server, Oracle, and Jet/Access), so you could use a query like this (tested on SQL Server Express 2008 R2):

SELECT
    FK,
    MAX(Field1) AS Field1,
    MAX(Field2) AS Field2
FROM
    table1
GROUP BY
    FK;

我使用了MAX，但是任何从GROUP BY行中选择一个值的聚合都应该起作用.

I used MAX, but any aggregate which picks one value from among the GROUP BY rows should work.

测试数据:

CREATE TABLE table1 (FK int, Field1 varchar(10), Field2 varchar(10));

INSERT INTO table1 VALUES (3, 'ABC', NULL);
INSERT INTO table1 VALUES (3, NULL, 'DEF');
INSERT INTO table1 VALUES (4, 'GHI', NULL);
INSERT INTO table1 VALUES (4, 'JKL', 'MNO');
INSERT INTO table1 VALUES (4, NULL, 'PQR');

结果:

FK  Field1  Field2
--  ------  ------
3   ABC     DEF
4   JKL     PQR

这篇关于在SQL中合并两行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！