SQLite 从多行中选择数据作为一行返回 [英] SQLite Select data from multiple rows returned as one row

问题描述

我想知道是否可以在 SQLite 中使用 SELECT 语句将两行的数据合并为一行，类似于下面 SQL Server 论坛中的建议.

I would like to know whether it is possible to use a SELECT statement in SQLite to merge the data from two rows into one, similar to how is suggested in the SQL Server forum below.

考虑以下基于 SQL Server 的场景(取自 http://forums.aspfree.com/microsoft-sql-server-14/merge-the-two-rows-in-one-row-245550.html)

Consider the scenario below which is based on SQL Server (taken from http://forums.aspfree.com/microsoft-sql-server-14/merge-the-two-rows-in-one-row-245550.html)

假设有一张桌子

身份证 |姓名 |
1 |x |
1 ||
2 |是 |
2 |问 |
3 |W |

ID | Name |
1 | x |
1 | P |
2 | y |
2 | Q |
3 | W |

我们希望 select 语句的结果数据输出:

We want the resulting data from the select statement to output:

身份证 |姓名-1 |Name-2 |
1 |× ||
2 |是 |问 |
3 || ||

Id | Name-1 | Name-2 |
1 | x | P |
2 | y | Q |
3 | w | |

帖子中的答案建议使用以下 SQL 作为可能的解决方案:

The answer in the post suggests the following SQL as a possible solution:

SELECT 
   a.ID, 
   [Name-1] = ISNULL(( 
   SELECT TOP 1 Name 
   FROM emp 
   WHERE ID = a.ID),''), 
   [Name-2] = ISNULL(( 
   SELECT TOP 1 b.Name 
   FROM emp b 
   WHERE b.ID = a.ID 
       AND Name NOT IN( 
           SELECT TOP 1 Name 
           FROM emp 
           WHERE ID = b.ID 
       )),'') 
FROM emp a 
GROUP BY a.ID   

使用 SQLite 是否可以生成列 [Name-1] &[Name-2] 使用嵌套的 SELECT 语句，就像我们在 SQL Server 中可以做的那样?

Using SQLite is it possible to generate the columns [Name-1] & [Name-2] using nested SELECT statements like we can do above in SQL Server?

推荐答案

SELECT 
   a.ID, 
   COALESCE(a.Name,'') as "Name-1", 
   COALESCE((SELECT b.Name FROM Emp b 
             WHERE b.ID = a.ID
             AND b.rowid != a.rowid LIMIT 1),'') as "Name-2"
FROM emp a 
GROUP BY a.ID

这篇关于SQLite 从多行中选择数据作为一行返回的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！