本文介绍了在SQLite的GROUP_CONCAT函数内使用ORDER BY子句的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我认为我不能在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子句的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!