在 SQL 中合并两行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2941116/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Merge two rows in SQL
提问by Jason
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
Thanks
谢谢
Edit: Fix field2 name for clarity
编辑：为了清晰起见，修复 field2 名称
回答by Cheran Shunmugavel
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):
聚合函数可以在这里帮助你。聚合函数会忽略NULLs（至少在 SQL Server、Oracle 和 Jet/Access 上是这样），因此您可以使用这样的查询（在 SQL Server Express 2008 R2 上测试）：
SELECT
FK,
MAX(Field1) AS Field1,
MAX(Field2) AS Field2
FROM
table1
GROUP BY
FK;
I used MAX, but any aggregate which picks one value from among the GROUP BYrows should work.
我使用了MAX，但是从GROUP BY行中选择一个值的任何聚合都应该起作用。
Test data:
测试数据：
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');
Results:
结果：
FK Field1 Field2
-- ------ ------
3 ABC DEF
4 JKL PQR
回答by Dustin Laine
There are a few ways depending on some data rules that you have not included, but here is one way using what you gave.
根据您未包含的某些数据规则，有几种方法，但这是使用您提供的方法的一种方法。
SELECT
t1.Field1,
t2.Field2
FROM Table1 t1
LEFT JOIN Table1 t2 ON t1.FK = t2.FK AND t2.Field1 IS NULL
Another way:
其它的办法：
SELECT
t1.Field1,
(SELECT Field2 FROM Table2 t2 WHERE t2.FK = t1.FK AND Field1 IS NULL) AS Field2
FROM Table1 t1
回答by AmaDaden
I had a similar problem. The difference was that I needed far more control over what I was returning so I ended up with an simple clear but rather long query. Here is a simplified version of it based on your example.
我有一个类似的问题。不同之处在于我需要对返回的内容进行更多的控制，所以我最终得到了一个简单明了但相当长的查询。这是基于您的示例的简化版本。
select main.id, Field1_Q.Field1, Field2_Q.Field2
from
(
select distinct id
from Table1
)as main
left outer join (
select id, max(Field1)
from Table1
where Field1 is not null
group by id
) as Field1_Q on main.id = Field1_Q.id
left outer join (
select id, max(Field2)
from Table1
where Field2 is not null
group by id
) as Field2_Q on main.id = Field2_Q.id
;
The trick here is that the first select 'main' selects the rows to display. Then you have one select per field. What is being joined on should be all of the same values returned by the 'main' query.
这里的技巧是第一个 select 'main' 选择要显示的行。然后每个字段都有一个选择。加入的内容应该是“主”查询返回的所有相同值。
Be warned, those other queries need to return only one row per id or you will be ignoring data
请注意，其他查询每个 ID 只需要返回一行，否则您将忽略数据
回答by Daniel Vassallo
There might be neater methods, but the following could be one approach:
可能有更简洁的方法，但以下可能是一种方法：
SELECT t.fk,
(
SELECT t1.Field1
FROM `table` t1
WHERE t1.fk = t.fk AND t1.Field1 IS NOT NULL
LIMIT 1
) Field1,
(
SELECT t2.Field2
FROM `table` t2
WHERE t2.fk = t.fk AND t2.Field2 IS NOT NULL
LIMIT 1
) Field2
FROM `table` t
WHERE t.fk = 3
GROUP BY t.fk;
Test Case:
测试用例：
CREATE TABLE `table` (fk int, Field1 varchar(10), Field2 varchar(10));
INSERT INTO `table` VALUES (3, 'ABC', NULL);
INSERT INTO `table` VALUES (3, NULL, 'DEF');
INSERT INTO `table` VALUES (4, 'GHI', NULL);
INSERT INTO `table` VALUES (4, NULL, 'JKL');
INSERT INTO `table` VALUES (5, NULL, 'MNO');
Result:
结果：
+------+--------+--------+
| fk | Field1 | Field2 |
+------+--------+--------+
| 3 | ABC | DEF |
+------+--------+--------+
1 row in set (0.01 sec)
Running the same query without the WHERE t.fk = 3clause, it would return the following result-set:
在没有WHERE t.fk = 3子句的情况下运行相同的查询，它将返回以下结果集：
+------+--------+--------+
| fk | Field1 | Field2 |
+------+--------+--------+
| 3 | ABC | DEF |
| 4 | GHI | JKL |
| 5 | NULL | MNO |
+------+--------+--------+
3 rows in set (0.01 sec)
回答by Kamrul Hasan
if one row has value in field1 column and other rows have null value then this Query might work.
如果一行在 field1 列中具有值而其他行具有空值，则此查询可能有效。
SELECT
FK,
MAX(Field1) as Field1,
MAX(Field2) as Field2
FROM
(
select FK,ISNULL(Field1,'') as Field1,ISNULL(Field2,'') as Field2 from table1
)
tbl
GROUP BY FK
回答by Yu Zhao
My case is I have a table like this
我的情况是我有一张这样的桌子
---------------------------------------------
|company_name|company_ID|CA | WA |
---------------------------------------------
|Costco | 1 |NULL | 2 |
---------------------------------------------
|Costco | 1 |3 |Null |
---------------------------------------------
And I want it to be like below:
我希望它如下所示：
---------------------------------------------
|company_name|company_ID|CA | WA |
---------------------------------------------
|Costco | 1 |3 | 2 |
---------------------------------------------
Most code is almost the same:
大多数代码几乎相同：
SELECT
FK,
MAX(CA) AS CA,
MAX(WA) AS WA
FROM
table1
GROUP BY company_name,company_ID
The only difference is the group by, if you put two column names into it, you can group them in pairs.
唯一的区别是group by，如果将两个列名放入其中，则可以将它们成对分组。
