SQLite 中的 GROUP_CONCAT

2021-11-27 00:00:00 sql sqlite group-concat android-sqlite

我有这样的数据

1 A
1 B
1 C
1 D
2 E
2 F
3 G
3 H
3 I
3 J
3 K

通过使用这个查询

select ABSTRACTS_ITEM._id,Name 
from ABSTRACTS_ITEM , ABSTRACT_AUTHOR , AUTHORS_ABSTRACT
where
ABSTRACTS_ITEM._id = AUTHORS_ABSTRACT.ABSTRACTSITEM_ID
and
ABSTRACT_AUTHOR._id = AUTHORS_ABSTRACT.ABSTRACTAUTHOR_ID

现在，我想显示这样的数据

Now, I want to show data like this

1 A,B,C,D
2 EF

等等..我也知道它可以通过GROUP_CONCAT函数来实现.所以，我试过这个

and so on..I also know it can achieve by GROUP_CONCAT function. So, I tried with this

SELECT ABSTRACTS_ITEM._id,
GROUP_CONCAT(ABSTRACT_AUTHOR.NAME) FROM
(select ABSTRACTS_ITEM._id,
Name
from
ABSTRACTS_ITEM , ABSTRACT_AUTHOR , AUTHORS_ABSTRACT
where
ABSTRACTS_ITEM._id = AUTHORS_ABSTRACT.ABSTRACTSITEM_ID
and
ABSTRACT_AUTHOR._id = AUTHORS_ABSTRACT.ABSTRACTAUTHOR_ID)

但是，它显示我错误.所以，我在这里做错了什么.实现这一目标的正确程序是什么?

But, It shows me error. So, what I am doing wrong here. What are the right procedure to achieve that?

推荐答案

使用聚合函数时需要添加GROUP BY子句.也可以使用 JOIN 来连接表.

You need to add GROUP BY clause when you are using aggregate function. Also use JOIN to join tables.

试试这个:

SELECT AI._id, GROUP_CONCAT(Name) AS GroupedName
  FROM ABSTRACTS_ITEM AI 
  JOIN AUTHORS_ABSTRACT AAB ON AI.ID = AAB.ABSTRACTSITEM_ID
  JOIN ABSTRACT_AUTHOR AAU ON AAU._id = AAB.ABSTRACTAUTHOR_ID
 GROUP BY tbl._id;

参见这个示例 SQLFiddle

<小时>

您尝试的几乎是正确的.您只需要在最后添加 GROUP BY 子句.但第一个更好.

See this sample SQLFiddle

What you were trying was almost correct. You just needed to add GROUP BY clause at the end. But the first one is better.

SELECT ID,
GROUP_CONCAT(NAME) 
FROM
    (select ABSTRACTS_ITEM._id AS ID,
     Name
     from
    ABSTRACTS_ITEM , ABSTRACT_AUTHOR , AUTHORS_ABSTRACT
    where
    ABSTRACTS_ITEM._id = AUTHORS_ABSTRACT.ABSTRACTSITEM_ID
    and
    ABSTRACT_AUTHOR._id = AUTHORS_ABSTRACT.ABSTRACTAUTHOR_ID)
GROUP BY ID;

相关文章