在SQLite的GROUP_CONCAT函数内使用ORDER BY子句 [英] Using ORDER BY clause inside GROUP_CONCAT function in SQLite

问题描述

我认为我不能在GROUP_CONCAT函数内使用ORDER BY子句.

I don't think that I can use ORDER BY clause inside the GROUP_CONCAT function.

有人知道在SQLite中完成此行为的棘手方法吗?

Does anyone know a tricky way to accomplish this behavior in SQLite?

我之前看到了这个问题.但是我有一个复杂的查询.

I saw this question before. But I have a complex query .

我的陈述如下:

SELECT
    c.col1, c.col3, m.col3, m.col4,
    count(m.col1), count(re.col2) AS cnt,
    GROUP_CONCAT(p.col1 ORDER BY p.col1) AS "Group1",
    GROUP_CONCAT(p.col2 ORDER BY p.col1) AS "Group2", 
    GROUP_CONCAT(CASE WHEN con.col3 is null THEN p.col1 ELSE con.col3 END),
    con.col4, con.col5, p.col3
FROM t1 re
    INNER JOIN t2  c  ON (re.col1  = c.col1)
    INNER JOIN t3  p  ON (re.col2  = p.col1)
    LEFT JOIN  t4 con ON (con.col1 = p.col2)
    INNER JOIN  t5 m  ON (m.col1   = c.col5) 
GROUP BY re.col1 

Group1和Group2来自同一表但不同的列:我想用Group2保留Group1的顺序:

Group1 and Group2 is coming from the same table but different columns: I want to preserve the order of the Group1 with Group2:

table t3 
+------+------+
| col1 | col2 |
+------+------+
|    1 | A    |
|    2 | B    |
|    3 | C    |
|    4 | D    |
|    5 | E    |
+------+------+

因此，如果Group1看起来像这样2,1,3 Group2应该看起来像这样B,A,C

so if Group1 appears like this 2,1,3 Group2 should appear like this B,A,C

推荐答案

我已经尝试过了，并且可以解决问题

I have tried this and it make the work

SELECT
    c.col1, c.col3, m.col3, m.col4,
    count(m.col1), count(re.col2) AS cnt,
    GROUP_CONCAT(p.col1 ORDER BY p.col1) AS "Group1",
    GROUP_CONCAT(p.col2 ORDER BY p.col1) AS "Group2", 
    GROUP_CONCAT(CASE WHEN con.col3 is null THEN p.col1 ELSE con.col3 END),
    con.col4, con.col5, p.col3
FROM t1 re
    INNER JOIN t2 c   ON (re.col1  = c.col1)
    INNER JOIN (
        SELECT col1, col2, col3, col4, col5 FROM t3 ORDER BY col1
    ) AS          p   ON (re.col2  = p.col1)
    LEFT JOIN  t4 con ON (con.col1 = p.col2)
    INNER JOIN t5 m   ON (m.col1   = c.col5) 
GROUP BY re.col1 

这篇关于在SQLite的GROUP_CONCAT函数内使用ORDER BY子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！