SQLite:group_concat() 多列 [英] SQLite: group_concat() multiple columns

问题描述

我有问题:

在我的 SQLite(android 上的 sqlite3)数据库中，我有一个这样的表

In my SQLite (sqlite3 on android) database I have a table like so

company |  name    | job
--------------------------
      1 |  'Peter' | 'Manager'
      1 |  'Jim'   | (null)
      2 |  'John'  | 'CEO'
      2 |  'Alex'  | 'Developer'
      3 |  'Lisa'  | (null)

我想去

company | formated
--------------------------------------
      1 | 'Peter (Manager), Jim'
      2 | 'John (CEO), Alex (Developer)'
      3 | 'Lisa'

到目前为止我得到的是

SELECT group_concat(concat) FROM (
    SELECT
        CASE 
            WHEN job IS NULL THEN name
            ELSE name || ' (' || job || ')'
        END AS concat
    FROM jobs
)

它给了我一个字符串

'Peter (Manager), Jim, John (CEO), Alex (Developer), Lisa'

虽然已经很不错了，但仍然不是我想要的.那时我无法理解我必须如何组合才能得到我想要的东西.

Although that is quite good already, it's still not what I want. And at that point I fail to understand how I have to combine things to get what I want.

旁注:是否有关于复杂查询的好教程?到目前为止，我只找到了一些片段，但没有真正解释如何构建这样的东西

On a sidenote: Is there any good tutorial on complexer queries? So far I've only found some snippets but nothing that really explains how such a thing can be built

推荐答案

你已经接近了！

SELECT 
    company,
    group_concat(concat) AS formated
FROM (
    SELECT
        company,
        CASE 
            WHEN job IS NULL THEN name
            ELSE name || ' (' || job || ')'
        END AS concat
    FROM jobs
)
GROUP BY company

这篇关于SQLite:group_concat() 多列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！