SQLite中的GROUP_CONCAT

问题描述

我有这样的数据

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;

这篇关于SQLite中的GROUP_CONCAT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！