|
1
by brian
clean slate |
1 |
drop table if exists t1, t2;
|
2 |
create table t1 (grp int, a bigint unsigned, c char(10) not null, d char(10) not null);
|
|
3 |
insert into t1 values (1,1,"a","a");
|
|
4 |
insert into t1 values (2,2,"b","a");
|
|
5 |
insert into t1 values (2,3,"c","b");
|
|
6 |
insert into t1 values (3,4,"E","a");
|
|
7 |
insert into t1 values (3,5,"C","b");
|
|
8 |
insert into t1 values (3,6,"D","b");
|
|
9 |
insert into t1 values (3,7,"d","d");
|
|
10 |
insert into t1 values (3,8,"d","d");
|
|
11 |
insert into t1 values (3,9,"D","c");
|
|
12 |
select grp,group_concat(c) from t1 group by grp;
|
|
13 |
grp group_concat(c)
|
|
14 |
1 a
|
|
15 |
2 b,c
|
|
16 |
3 E,C,D,d,d,D
|
|
17 |
explain extended select grp,group_concat(c) from t1 group by grp;
|
|
18 |
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
19 |
1 SIMPLE t1 ALL NULL NULL NULL NULL 9 100.00 Using filesort
|
|
20 |
Warnings:
|
|
21 |
Note 1003 select "test"."t1"."grp" AS "grp",group_concat("test"."t1"."c" separator ',') AS "group_concat(c)" from "test"."t1" group by "test"."t1"."grp"
|
|
22 |
select grp,group_concat(a,c) from t1 group by grp;
|
|
23 |
grp group_concat(a,c)
|
|
24 |
1 1a
|
|
25 |
2 2b,3c
|
|
26 |
3 4E,5C,6D,7d,8d,9D
|
|
27 |
select grp,group_concat("(",a,":",c,")") from t1 group by grp;
|
|
28 |
grp group_concat("(",a,":",c,")")
|
|
29 |
1 (1:a)
|
|
30 |
2 (2:b),(3:c)
|
|
31 |
3 (4:E),(5:C),(6:D),(7:d),(8:d),(9:D)
|
|
32 |
select grp,group_concat(c separator ",") from t1 group by grp;
|
|
33 |
grp group_concat(c separator ",")
|
|
34 |
1 a
|
|
35 |
2 b,c
|
|
36 |
3 E,C,D,d,d,D
|
|
37 |
select grp,group_concat(c separator "---->") from t1 group by grp;
|
|
38 |
grp group_concat(c separator "---->")
|
|
39 |
1 a
|
|
40 |
2 b---->c
|
|
41 |
3 E---->C---->D---->d---->d---->D
|
|
42 |
select grp,group_concat(c order by c) from t1 group by grp;
|
|
43 |
grp group_concat(c order by c)
|
|
44 |
1 a
|
|
45 |
2 b,c
|
|
46 |
3 C,D,d,d,D,E
|
|
47 |
select grp,group_concat(c order by c desc) from t1 group by grp;
|
|
48 |
grp group_concat(c order by c desc)
|
|
49 |
1 a
|
|
50 |
2 c,b
|
|
51 |
3 E,D,d,d,D,C
|
|
52 |
select grp,group_concat(d order by a) from t1 group by grp;
|
|
53 |
grp group_concat(d order by a)
|
|
54 |
1 a
|
|
55 |
2 a,b
|
|
56 |
3 a,b,b,d,d,c
|
|
57 |
select grp,group_concat(d order by a desc) from t1 group by grp;
|
|
58 |
grp group_concat(d order by a desc)
|
|
59 |
1 a
|
|
60 |
2 b,a
|
|
61 |
3 c,d,d,b,b,a
|
|
62 |
select grp,group_concat(a order by a,d+c-ascii(c)-a) from t1 group by grp;
|
|
63 |
grp group_concat(a order by a,d+c-ascii(c)-a)
|
|
64 |
1 1
|
|
65 |
2 2,3
|
|
66 |
3 4,5,6,7,8,9
|
|
67 |
select grp,group_concat(a order by d+c-ascii(c),a) from t1 group by grp;
|
|
68 |
grp group_concat(a order by d+c-ascii(c),a)
|
|
69 |
1 1
|
|
70 |
2 3,2
|
|
71 |
3 7,8,4,6,9,5
|
|
72 |
select grp,group_concat(c order by 1) from t1 group by grp;
|
|
73 |
grp group_concat(c order by 1)
|
|
74 |
1 a
|
|
75 |
2 b,c
|
|
76 |
3 C,D,d,d,D,E
|
|
77 |
select grp,group_concat(distinct c order by c) from t1 group by grp;
|
|
78 |
grp group_concat(distinct c order by c)
|
|
79 |
1 a
|
|
80 |
2 b,c
|
|
81 |
3 C,D,E
|
|
82 |
select grp,group_concat(distinct c order by c desc) from t1 group by grp;
|
|
83 |
grp group_concat(distinct c order by c desc)
|
|
84 |
1 a
|
|
85 |
2 c,b
|
|
86 |
3 E,D,C
|
|
87 |
explain extended select grp,group_concat(distinct c order by c desc) from t1 group by grp;
|
|
88 |
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
89 |
1 SIMPLE t1 ALL NULL NULL NULL NULL 9 100.00 Using filesort
|
|
90 |
Warnings:
|
|
91 |
Note 1003 select "test"."t1"."grp" AS "grp",group_concat(distinct "test"."t1"."c" order by "test"."t1"."c" DESC separator ',') AS "group_concat(distinct c order by c desc)" from "test"."t1" group by "test"."t1"."grp"
|
|
92 |
select grp,group_concat(c order by c separator ",") from t1 group by grp;
|
|
93 |
grp group_concat(c order by c separator ",")
|
|
94 |
1 a
|
|
95 |
2 b,c
|
|
96 |
3 C,D,d,d,D,E
|
|
97 |
select grp,group_concat(c order by c desc separator ",") from t1 group by grp;
|
|
98 |
grp group_concat(c order by c desc separator ",")
|
|
99 |
1 a
|
|
100 |
2 c,b
|
|
101 |
3 E,D,d,d,D,C
|
|
102 |
select grp,group_concat(distinct c order by c separator ",") from t1 group by grp;
|
|
103 |
grp group_concat(distinct c order by c separator ",")
|
|
104 |
1 a
|
|
105 |
2 b,c
|
|
106 |
3 C,D,E
|
|
107 |
explain extended select grp,group_concat(distinct c order by c separator ",") from t1 group by grp;
|
|
108 |
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
109 |
1 SIMPLE t1 ALL NULL NULL NULL NULL 9 100.00 Using filesort
|
|
110 |
Warnings:
|
|
111 |
Note 1003 select "test"."t1"."grp" AS "grp",group_concat(distinct "test"."t1"."c" order by "test"."t1"."c" ASC separator ',') AS "group_concat(distinct c order by c separator "","")" from "test"."t1" group by "test"."t1"."grp"
|
|
112 |
select grp,group_concat(distinct c order by c desc separator ",") from t1 group by grp;
|
|
113 |
grp group_concat(distinct c order by c desc separator ",")
|
|
114 |
1 a
|
|
115 |
2 c,b
|
|
116 |
3 E,D,C
|
|
117 |
select grp,group_concat(c order by grp desc) from t1 group by grp order by grp;
|
|
118 |
grp group_concat(c order by grp desc)
|
|
119 |
1 a
|
|
120 |
2 c,b
|
|
121 |
3 D,d,d,D,C,E
|
|
122 |
select grp, group_concat(a separator "")+0 from t1 group by grp;
|
|
123 |
grp group_concat(a separator "")+0
|
|
124 |
1 1
|
|
125 |
2 23
|
|
126 |
3 456789
|
|
127 |
select grp, group_concat(a separator "")+0.0 from t1 group by grp;
|
|
128 |
grp group_concat(a separator "")+0.0
|
|
129 |
1 1.0
|
|
130 |
2 23.0
|
|
131 |
3 456789.0
|
|
132 |
select grp, ROUND(group_concat(a separator "")) from t1 group by grp;
|
|
133 |
grp ROUND(group_concat(a separator ""))
|
|
134 |
1 1
|
|
135 |
2 23
|
|
136 |
3 456789
|
|
137 |
drop table t1;
|
|
138 |
create table t1 (grp int, c char(10));
|
|
139 |
insert into t1 values (1,NULL),(2,"b"),(2,NULL),(3,"E"),(3,NULL),(3,"D"),(3,NULL),(3,NULL),(3,"D"),(4,""),(5,NULL);
|
|
140 |
select grp,group_concat(c order by c) from t1 group by grp;
|
|
141 |
grp group_concat(c order by c)
|
|
142 |
1 NULL
|
|
143 |
2 b
|
|
144 |
3 D,D,E
|
|
145 |
4
|
|
146 |
5 NULL
|
|
147 |
set group_concat_max_len = 4;
|
|
148 |
select grp,group_concat(c) from t1 group by grp;
|
|
149 |
grp group_concat(c)
|
|
150 |
1 NULL
|
|
151 |
2 b
|
|
152 |
3 D,D,
|
|
153 |
4
|
|
154 |
5 NULL
|
|
155 |
Warnings:
|
|
156 |
Warning 1260 1 line(s) were cut by GROUP_CONCAT()
|
|
157 |
show warnings;
|
|
158 |
Level Code Message
|
|
159 |
Warning 1260 1 line(s) were cut by GROUP_CONCAT()
|
|
160 |
set group_concat_max_len = 1024;
|
|
161 |
select group_concat(sum(c)) from t1 group by grp;
|
|
162 |
ERROR HY000: Invalid use of group function
|
|
163 |
select grp,group_concat(c order by 2) from t1 group by grp;
|
|
164 |
ERROR 42S22: Unknown column '2' in 'order clause'
|
|
165 |
drop table t1;
|
|
166 |
create table t1 ( URL_ID int(11), URL varchar(80));
|
|
167 |
create table t2 ( REQ_ID int(11), URL_ID int(11));
|
|
168 |
insert into t1 values (4,'www.host.com'), (5,'www.google.com'),(5,'www.help.com');
|
|
169 |
insert into t2 values (1,4), (5,4), (5,5);
|
|
170 |
select REQ_ID, Group_Concat(URL) as URL from t1, t2 where
|
|
171 |
t2.URL_ID = t1.URL_ID group by REQ_ID;
|
|
172 |
REQ_ID URL
|
|
173 |
1 X
|
|
174 |
5 X,X,X
|
|
175 |
select REQ_ID, Group_Concat(URL) as URL, Min(t1.URL_ID) urll,
|
|
176 |
Max(t1.URL_ID) urlg from t1, t2 where t2.URL_ID = t1.URL_ID group by REQ_ID;
|
|
177 |
REQ_ID URL urll urlg
|
|
178 |
1 X 4 4
|
|
179 |
5 X,X,X 4 5
|
|
180 |
drop table t1;
|
|
181 |
drop table t2;
|
|
182 |
create table t1 (id int, name varchar(16));
|
|
183 |
insert into t1 values (1,'longername'),(1,'evenlongername');
|
|
184 |
select ifnull(group_concat(concat(t1.id, ':', t1.name)), 'shortname') as 'without distinct: how it should be' from t1;
|
|
185 |
without distinct: how it should be
|
|
186 |
1:longername,1:evenlongername
|
|
187 |
select distinct ifnull(group_concat(concat(t1.id, ':', t1.name)), 'shortname') as 'with distinct: cutoff at length of shortname' from t1;
|
|
188 |
with distinct: cutoff at length of shortname
|
|
189 |
1:longername,1:evenlongername
|
|
190 |
drop table t1;
|
|
191 |
create table t1(id int);
|
|
192 |
create table t2(id int);
|
|
193 |
insert into t1 values(0),(1);
|
|
194 |
select group_concat(t1.id) FROM t1,t2;
|
|
195 |
group_concat(t1.id)
|
|
196 |
NULL
|
|
197 |
drop table t1;
|
|
198 |
drop table t2;
|
|
199 |
create table t1 (bar varchar(32));
|
|
200 |
insert into t1 values('test1'),('test2');
|
|
201 |
select group_concat(bar order by concat(bar,bar)) from t1;
|
|
202 |
group_concat(bar order by concat(bar,bar))
|
|
203 |
test1,test2
|
|
204 |
select group_concat(bar order by concat(bar,bar) desc) from t1;
|
|
205 |
group_concat(bar order by concat(bar,bar) desc)
|
|
206 |
test2,test1
|
|
207 |
select bar from t1 having group_concat(bar)='';
|
|
208 |
bar
|
|
209 |
select bar from t1 having instr(group_concat(bar), "test") > 0;
|
|
210 |
bar
|
|
211 |
test1
|
|
212 |
select bar from t1 having instr(group_concat(bar order by concat(bar,bar) desc), "test2,test1") > 0;
|
|
213 |
bar
|
|
214 |
test1
|
|
215 |
drop table t1;
|
|
216 |
create table t1 (a int, a1 varchar(10));
|
|
217 |
create table t2 (a0 int);
|
|
218 |
insert into t1 values (0,"a"),(0,"b"),(1,"c");
|
|
219 |
insert into t2 values (1),(2),(3);
|
|
220 |
select group_concat(a1 order by (t1.a IN (select a0 from t2))) from t1;
|
|
221 |
group_concat(a1 order by (t1.a IN (select a0 from t2)))
|
|
222 |
b,a,c
|
|
223 |
select group_concat(a1 order by (t1.a)) from t1;
|
|
224 |
group_concat(a1 order by (t1.a))
|
|
225 |
b,a,c
|
|
226 |
drop table t1, t2;
|
|
227 |
CREATE TABLE t1 (id1 tinyint(4) NOT NULL, id2 tinyint(4) NOT NULL);
|
|
228 |
INSERT INTO t1 VALUES (1, 1),(1, 2),(1, 3),(1, 4),(1, 5),(2, 1),(2, 2),(2, 3);
|
|
229 |
CREATE TABLE t2 (id1 tinyint(4) NOT NULL);
|
|
230 |
INSERT INTO t2 VALUES (1),(2),(3),(4),(5);
|
|
231 |
SELECT t1.id1, GROUP_CONCAT(t1.id2 ORDER BY t1.id2 ASC) AS concat_id FROM t1, t2 WHERE t1.id1 = t2.id1 AND t1.id1=1 GROUP BY t1.id1;
|
|
232 |
id1 concat_id
|
|
233 |
1 1,2,3,4,5
|
|
234 |
SELECT t1.id1, GROUP_CONCAT(t1.id2 ORDER BY t1.id2 ASC) AS concat_id FROM t1, t2 WHERE t1.id1 = t2.id1 GROUP BY t1.id1;
|
|
235 |
id1 concat_id
|
|
236 |
1 1,2,3,4,5
|
|
237 |
2 1,2,3
|
|
238 |
SELECT t1.id1, GROUP_CONCAT(t1.id2 ORDER BY t1.id2 DESC) AS concat_id FROM t1, t2 WHERE t1.id1 = t2.id1 GROUP BY t1.id1;
|
|
239 |
id1 concat_id
|
|
240 |
1 5,4,3,2,1
|
|
241 |
2 3,2,1
|
|
242 |
SELECT t1.id1, GROUP_CONCAT(t1.id2 ORDER BY 6-t1.id2 ASC) AS concat_id FROM t1, t2 WHERE t1.id1 = t2.id1 GROUP BY t1.id1;
|
|
243 |
id1 concat_id
|
|
244 |
1 5,4,3,2,1
|
|
245 |
2 3,2,1
|
|
246 |
SELECT t1.id1, GROUP_CONCAT(t1.id2,6-t1.id2 ORDER BY 6-t1.id2 ASC) AS concat_id FROM t1, t2 WHERE t1.id1 = t2.id1 GROUP BY t1.id1;
|
|
247 |
id1 concat_id
|
|
248 |
1 51,42,33,24,15
|
|
249 |
2 33,24,15
|
|
250 |
SELECT t1.id1, GROUP_CONCAT(t1.id2,6-t1.id2 ORDER BY 6-t1.id2 ASC) AS concat_id FROM t1, t2 WHERE t1.id1 = t2.id1 GROUP BY t1.id1;
|
|
251 |
id1 concat_id
|
|
252 |
1 51,42,33,24,15
|
|
253 |
2 33,24,15
|
|
254 |
SELECT t1.id1, GROUP_CONCAT(t1.id2,"/",6-t1.id2 ORDER BY 1+0,6-t1.id2,t1.id2 ASC) AS concat_id FROM t1, t2 WHERE t1.id1 = t2.id1 GROUP BY t1.id1;
|
|
255 |
id1 concat_id
|
|
256 |
1 5/1,4/2,3/3,2/4,1/5
|
|
257 |
2 3/3,2/4,1/5
|
|
258 |
drop table t1,t2;
|
|
259 |
create table t1 (s1 char(10), s2 int not null);
|
|
260 |
insert into t1 values ('a',2),('b',2),('c',1),('a',3),('b',4),('c',4);
|
|
261 |
select distinct s1 from t1 order by s2,s1;
|
|
262 |
s1
|
|
263 |
c
|
|
264 |
a
|
|
265 |
b
|
|
266 |
select group_concat(distinct s1) from t1;
|
|
267 |
group_concat(distinct s1)
|
|
268 |
a,b,c
|
|
269 |
select group_concat(distinct s1 order by s2) from t1 where s2 < 4;
|
|
270 |
group_concat(distinct s1 order by s2)
|
|
271 |
c,b,a
|
|
272 |
select group_concat(distinct s1 order by s2) from t1;
|
|
273 |
group_concat(distinct s1 order by s2)
|
|
274 |
c,b,a
|
|
275 |
drop table t1;
|
|
276 |
create table t1 (a int, c int);
|
|
277 |
insert into t1 values (1, 2), (2, 3), (2, 4), (3, 5);
|
|
278 |
create table t2 (a int, c int);
|
|
279 |
insert into t2 values (1, 5), (2, 4), (3, 3), (3,3);
|
|
280 |
select group_concat(c) from t1;
|
|
281 |
group_concat(c)
|
|
282 |
2,3,4,5
|
|
283 |
select group_concat(c order by (select c from t2 where t2.a=t1.a limit 1)) as grp from t1;
|
|
284 |
grp
|
|
285 |
5,4,3,2
|
|
286 |
select group_concat(c order by (select mid(group_concat(c order by a),1,5) from t2 where t2.a=t1.a)) as grp from t1;
|
|
287 |
grp
|
|
288 |
5,4,3,2
|
|
289 |
select group_concat(c order by (select mid(group_concat(c order by a),1,5) from t2 where t2.a=t1.a) desc) as grp from t1;
|
|
290 |
grp
|
|
291 |
2,4,3,5
|
|
292 |
select t1.a, group_concat(c order by (select c from t2 where t2.a=t1.a limit 1)) as grp from t1 group by 1;
|
|
293 |
a grp
|
|
294 |
1 2
|
|
295 |
2 4,3
|
|
296 |
3 5
|
|
297 |
select t1.a, group_concat(c order by (select mid(group_concat(c order by a),1,5) from t2 where t2.a=t1.a)) as grp from t1 group by 1;
|
|
298 |
a grp
|
|
299 |
1 2
|
|
300 |
2 4,3
|
|
301 |
3 5
|
|
302 |
select t1.a, group_concat(c order by (select mid(group_concat(c order by a),1,5) from t2 where t2.a=t1.a) desc) as grp from t1 group by 1;
|
|
303 |
a grp
|
|
304 |
1 2
|
|
305 |
2 4,3
|
|
306 |
3 5
|
|
307 |
select group_concat(c order by (select concat(5-t1.c,group_concat(c order by a)) from t2 where t2.a=t1.a)) as grp from t1;
|
|
308 |
grp
|
|
309 |
5,4,3,2
|
|
310 |
select group_concat(c order by (select concat(t1.c,group_concat(c)) from t2 where a=t1.a)) as grp from t1;
|
|
311 |
grp
|
|
312 |
2,3,4,5
|
|
313 |
select a,c,(select group_concat(c order by a) from t2 where a=t1.a) as grp from t1 order by grp;
|
|
314 |
a c grp
|
|
315 |
3 5 3,3
|
|
316 |
2 3 4
|
|
317 |
2 4 4
|
|
318 |
1 2 5
|
|
319 |
drop table t1,t2;
|
|
320 |
CREATE TABLE t1 ( a int );
|
|
321 |
CREATE TABLE t2 ( a int );
|
|
322 |
INSERT INTO t1 VALUES (1), (2);
|
|
323 |
INSERT INTO t2 VALUES (1), (2);
|
|
324 |
SELECT GROUP_CONCAT(t1.a*t2.a ORDER BY t2.a) FROM t1, t2 GROUP BY t1.a;
|
|
325 |
GROUP_CONCAT(t1.a*t2.a ORDER BY t2.a)
|
|
326 |
1,2
|
|
327 |
2,4
|
|
328 |
DROP TABLE t1, t2;
|
|
329 |
CREATE TABLE t1 (a char(4));
|
|
330 |
INSERT INTO t1 VALUES ('John'), ('Anna'), ('Bill');
|
|
331 |
SELECT GROUP_CONCAT(a SEPARATOR '||') AS names FROM t1
|
|
332 |
HAVING names LIKE '%An%';
|
|
333 |
names
|
|
334 |
John||Anna||Bill
|
|
335 |
SELECT GROUP_CONCAT(a SEPARATOR '###') AS names FROM t1
|
|
336 |
HAVING LEFT(names, 1) ='J';
|
|
337 |
names
|
|
338 |
John###Anna###Bill
|
|
339 |
DROP TABLE t1;
|
|
340 |
CREATE TABLE t1 ( a int, b TEXT );
|
|
341 |
INSERT INTO t1 VALUES (1,'First Row'), (2,'Second Row');
|
|
342 |
SELECT GROUP_CONCAT(b ORDER BY b) FROM t1 GROUP BY a;
|
|
343 |
GROUP_CONCAT(b ORDER BY b)
|
|
344 |
First Row
|
|
345 |
Second Row
|
|
346 |
DROP TABLE t1;
|
|
347 |
CREATE TABLE t1 (A_ID INT NOT NULL,A_DESC CHAR(3) NOT NULL,PRIMARY KEY (A_ID));
|
|
348 |
INSERT INTO t1 VALUES (1,'ABC'), (2,'EFG'), (3,'HIJ');
|
|
349 |
CREATE TABLE t2 (A_ID INT NOT NULL,B_DESC CHAR(3) NOT NULL,PRIMARY KEY (A_ID,B_DESC));
|
|
350 |
INSERT INTO t2 VALUES (1,'A'),(1,'B'),(3,'F');
|
|
351 |
SELECT t1.A_ID, GROUP_CONCAT(t2.B_DESC) AS B_DESC FROM t1 LEFT JOIN t2 ON t1.A_ID=t2.A_ID GROUP BY t1.A_ID ORDER BY t1.A_DESC;
|
|
352 |
A_ID B_DESC
|
|
353 |
1 A,B
|
|
354 |
2 NULL
|
|
355 |
3 F
|
|
356 |
DROP TABLE t1;
|
|
357 |
DROP TABLE t2;
|
|
358 |
create table t1 (a int, b text);
|
|
359 |
insert into t1 values (1, 'bb'), (1, 'ccc'), (1, 'a'), (1, 'bb'), (1, 'ccc');
|
|
360 |
insert into t1 values (2, 'BB'), (2, 'CCC'), (2, 'A'), (2, 'BB'), (2, 'CCC');
|
|
361 |
select group_concat(b) from t1 group by a;
|
|
362 |
group_concat(b)
|
|
363 |
bb,ccc,a,bb,ccc
|
|
364 |
BB,CCC,A,BB,CCC
|
|
365 |
select group_concat(distinct b) from t1 group by a;
|
|
366 |
group_concat(distinct b)
|
|
367 |
bb,ccc,a
|
|
368 |
BB,CCC,A
|
|
369 |
select group_concat(b order by b) from t1 group by a;
|
|
370 |
group_concat(b order by b)
|
|
371 |
a,bb,bb,ccc,ccc
|
|
372 |
A,BB,BB,CCC,CCC
|
|
373 |
select group_concat(distinct b order by b) from t1 group by a;
|
|
374 |
group_concat(distinct b order by b)
|
|
375 |
a,bb,ccc
|
|
376 |
A,BB,CCC
|
|
377 |
set local group_concat_max_len=4;
|
|
378 |
select group_concat(b) from t1 group by a;
|
|
379 |
group_concat(b)
|
|
380 |
bb,c
|
|
381 |
BB,C
|
|
382 |
Warnings:
|
|
383 |
Warning 1260 2 line(s) were cut by GROUP_CONCAT()
|
|
384 |
select group_concat(distinct b) from t1 group by a;
|
|
385 |
group_concat(distinct b)
|
|
386 |
bb,c
|
|
387 |
BB,C
|
|
388 |
Warnings:
|
|
389 |
Warning 1260 2 line(s) were cut by GROUP_CONCAT()
|
|
390 |
select group_concat(b order by b) from t1 group by a;
|
|
391 |
group_concat(b order by b)
|
|
392 |
a,bb
|
|
393 |
A,BB
|
|
394 |
Warnings:
|
|
395 |
Warning 1260 2 line(s) were cut by GROUP_CONCAT()
|
|
396 |
select group_concat(distinct b order by b) from t1 group by a;
|
|
397 |
group_concat(distinct b order by b)
|
|
398 |
a,bb
|
|
399 |
A,BB
|
|
400 |
Warnings:
|
|
401 |
Warning 1260 2 line(s) were cut by GROUP_CONCAT()
|
|
402 |
insert into t1 values (1, concat(repeat('1', 300), '2')),
|
|
403 |
(1, concat(repeat('1', 300), '2')), (1, concat(repeat('0', 300), '1')),
|
|
404 |
(2, concat(repeat('1', 300), '2')), (2, concat(repeat('1', 300), '2')),
|
|
405 |
(2, concat(repeat('0', 300), '1'));
|
|
406 |
set local group_concat_max_len=1024;
|
|
407 |
select group_concat(b) from t1 group by a;
|
|
408 |
group_concat(b)
|
|
409 |
bb,ccc,a,bb,ccc,1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111112,1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111112,0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001
|
|
410 |
BB,CCC,A,BB,CCC,1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111112,1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111112,0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001
|
|
411 |
select group_concat(distinct b) from t1 group by a;
|
|
412 |
group_concat(distinct b)
|
|
413 |
bb,ccc,a,1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111112,0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001
|
|
414 |
BB,CCC,A,1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111112,0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001
|
|
415 |
select group_concat(b order by b) from t1 group by a;
|
|
416 |
group_concat(b order by b)
|
|
417 |
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001,1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111112,1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111112,a,bb,bb,ccc,ccc
|
|
418 |
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001,1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111112,1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111112,A,BB,BB,CCC,CCC
|
|
419 |
select group_concat(distinct b order by b) from t1 group by a;
|
|
420 |
group_concat(distinct b order by b)
|
|
421 |
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001,1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111112,a,bb,ccc
|
|
422 |
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001,1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111112,A,BB,CCC
|
|
423 |
set local group_concat_max_len=400;
|
|
424 |
select group_concat(b) from t1 group by a;
|
|
425 |
group_concat(b)
|
|
426 |
bb,ccc,a,bb,ccc,1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111112,1111111111111111111111111111111111111111111111111111111111111111111111111111111111
|
|
427 |
BB,CCC,A,BB,CCC,1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111112,1111111111111111111111111111111111111111111111111111111111111111111111111111111111
|
|
428 |
Warnings:
|
|
429 |
Warning 1260 2 line(s) were cut by GROUP_CONCAT()
|
|
430 |
select group_concat(distinct b) from t1 group by a;
|
|
431 |
group_concat(distinct b)
|
|
432 |
bb,ccc,a,1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111112,00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
|
|
433 |
BB,CCC,A,1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111112,00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
|
|
434 |
Warnings:
|
|
435 |
Warning 1260 2 line(s) were cut by GROUP_CONCAT()
|
|
436 |
select group_concat(b order by b) from t1 group by a;
|
|
437 |
group_concat(b order by b)
|
|
438 |
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001,11111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111
|
|
439 |
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001,11111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111
|
|
440 |
Warnings:
|
|
441 |
Warning 1260 2 line(s) were cut by GROUP_CONCAT()
|
|
442 |
select group_concat(distinct b order by b) from t1 group by a;
|
|
443 |
group_concat(distinct b order by b)
|
|
444 |
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001,11111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111
|
|
445 |
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001,11111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111
|
|
446 |
Warnings:
|
|
447 |
Warning 1260 2 line(s) were cut by GROUP_CONCAT()
|
|
448 |
drop table t1;
|
|
449 |
create table t1 (a varchar(255) character set cp1250 collate cp1250_general_ci,
|
|
450 |
b varchar(255) character set koi8r);
|
|
451 |
insert into t1 values ('xxx','yyy');
|
|
452 |
select collation(a) from t1;
|
|
453 |
collation(a)
|
|
454 |
cp1250_general_ci
|
|
455 |
select collation(group_concat(a)) from t1;
|
|
456 |
collation(group_concat(a))
|
|
457 |
cp1250_general_ci
|
|
458 |
create table t2 select group_concat(a) as a from t1;
|
|
459 |
show create table t2;
|
|
460 |
Table Create Table
|
|
461 |
t2 CREATE TABLE "t2" (
|
|
462 |
"a" varchar(400) CHARACTER SET cp1250
|
|
463 |
) ENGINE=MyISAM DEFAULT CHARSET=latin1
|
|
464 |
select collation(group_concat(a,_koi8r'test')) from t1;
|
|
465 |
collation(group_concat(a,_koi8r'test'))
|
|
466 |
cp1250_general_ci
|
|
467 |
select collation(group_concat(a,_koi8r 0xC1C2)) from t1;
|
|
468 |
ERROR HY000: Illegal mix of collations (cp1250_general_ci,IMPLICIT) and (koi8r_general_ci,COERCIBLE) for operation 'group_concat'
|
|
469 |
select collation(group_concat(a,b)) from t1;
|
|
470 |
ERROR HY000: Illegal mix of collations (cp1250_general_ci,IMPLICIT) and (koi8r_general_ci,IMPLICIT) for operation 'group_concat'
|
|
471 |
drop table t1;
|
|
472 |
drop table t2;
|
|
473 |
CREATE TABLE t1 (a CHAR(10) CHARACTER SET cp850);
|
|
474 |
INSERT INTO t1 VALUES ('À');
|
|
475 |
SELECT a FROM t1;
|
|
476 |
a
|
|
477 |
À
|
|
478 |
SELECT GROUP_CONCAT(a) FROM t1;
|
|
479 |
GROUP_CONCAT(a)
|
|
480 |
À
|
|
481 |
DROP TABLE t1;
|
|
482 |
CREATE TABLE t1 (id int);
|
|
483 |
SELECT GROUP_CONCAT(id) AS gc FROM t1 HAVING gc IS NULL;
|
|
484 |
gc
|
|
485 |
NULL
|
|
486 |
DROP TABLE t1;
|
|
487 |
create table t2 (a int, b int);
|
|
488 |
insert into t2 values (1,1), (2,2);
|
|
489 |
select b x, (select group_concat(x) from t2) from t2;
|
|
490 |
x (select group_concat(x) from t2)
|
|
491 |
1 1,1
|
|
492 |
2 2,2
|
|
493 |
drop table t2;
|
|
494 |
create table t1 (d int not null auto_increment,primary key(d), a int, b int, c int);
|
|
495 |
insert into t1(a,b) values (1,3), (1,4), (1,2), (2,7), (1,1), (1,2), (2,3), (2,3);
|
|
496 |
select d,a,b from t1 order by a;
|
|
497 |
d a b
|
|
498 |
1 1 3
|
|
499 |
2 1 4
|
|
500 |
3 1 2
|
|
501 |
5 1 1
|
|
502 |
6 1 2
|
|
503 |
4 2 7
|
|
504 |
7 2 3
|
|
505 |
8 2 3
|
|
506 |
explain select a, group_concat(b) from t1 group by a with rollup;
|
|
507 |
id select_type table type possible_keys key key_len ref rows Extra
|
|
508 |
1 SIMPLE t1 ALL NULL NULL NULL NULL 8 Using filesort
|
|
509 |
select a, group_concat(b) from t1 group by a with rollup;
|
|
510 |
a group_concat(b)
|
|
511 |
1 3,4,2,1,2
|
|
512 |
2 7,3,3
|
|
513 |
NULL 3,4,2,1,2,7,3,3
|
|
514 |
select a, group_concat(distinct b) from t1 group by a with rollup;
|
|
515 |
a group_concat(distinct b)
|
|
516 |
1 3,4,2,1
|
|
517 |
2 7,3
|
|
518 |
NULL 3,4,2,1,7
|
|
519 |
select a, group_concat(b order by b) from t1 group by a with rollup;
|
|
520 |
a group_concat(b order by b)
|
|
521 |
1 1,2,2,3,4
|
|
522 |
2 3,3,7
|
|
523 |
NULL 1,2,2,3,3,3,4,7
|
|
524 |
select a, group_concat(distinct b order by b) from t1 group by a with rollup;
|
|
525 |
a group_concat(distinct b order by b)
|
|
526 |
1 1,2,3,4
|
|
527 |
2 3,7
|
|
528 |
NULL 1,2,3,4,7
|
|
529 |
drop table t1;
|
|
530 |
create table t1 (a char(3), b char(20), primary key (a, b));
|
|
531 |
insert into t1 values ('ABW', 'Dutch'), ('ABW', 'English');
|
|
532 |
select group_concat(a) from t1 group by b;
|
|
533 |
group_concat(a)
|
|
534 |
ABW
|
|
535 |
ABW
|
|
536 |
drop table t1;
|
|
537 |
CREATE TABLE t1 (
|
|
538 |
aID smallint(5) unsigned NOT NULL auto_increment,
|
|
539 |
sometitle varchar(255) NOT NULL default '',
|
|
540 |
bID smallint(5) unsigned NOT NULL,
|
|
541 |
PRIMARY KEY (aID),
|
|
542 |
UNIQUE KEY sometitle (sometitle)
|
|
543 |
);
|
|
544 |
INSERT INTO t1 SET sometitle = 'title1', bID = 1;
|
|
545 |
INSERT INTO t1 SET sometitle = 'title2', bID = 1;
|
|
546 |
CREATE TABLE t2 (
|
|
547 |
bID smallint(5) unsigned NOT NULL auto_increment,
|
|
548 |
somename varchar(255) NOT NULL default '',
|
|
549 |
PRIMARY KEY (bID),
|
|
550 |
UNIQUE KEY somename (somename)
|
|
551 |
);
|
|
552 |
INSERT INTO t2 SET somename = 'test';
|
|
553 |
SELECT COUNT(*), GROUP_CONCAT(DISTINCT t2.somename SEPARATOR ' |')
|
|
554 |
FROM t1 JOIN t2 ON t1.bID = t2.bID;
|
|
555 |
COUNT(*) GROUP_CONCAT(DISTINCT t2.somename SEPARATOR ' |')
|
|
556 |
2 test
|
|
557 |
INSERT INTO t2 SET somename = 'test2';
|
|
558 |
SELECT COUNT(*), GROUP_CONCAT(DISTINCT t2.somename SEPARATOR ' |')
|
|
559 |
FROM t1 JOIN t2 ON t1.bID = t2.bID;
|
|
560 |
COUNT(*) GROUP_CONCAT(DISTINCT t2.somename SEPARATOR ' |')
|
|
561 |
2 test
|
|
562 |
DELETE FROM t2 WHERE somename = 'test2';
|
|
563 |
SELECT COUNT(*), GROUP_CONCAT(DISTINCT t2.somename SEPARATOR ' |')
|
|
564 |
FROM t1 JOIN t2 ON t1.bID = t2.bID;
|
|
565 |
COUNT(*) GROUP_CONCAT(DISTINCT t2.somename SEPARATOR ' |')
|
|
566 |
2 test
|
|
567 |
DROP TABLE t1,t2;
|
|
568 |
create table t1 ( a int not null default 0);
|
|
569 |
select * from (select group_concat(a) from t1) t2;
|
|
570 |
group_concat(a)
|
|
571 |
NULL
|
|
572 |
select group_concat('x') UNION ALL select 1;
|
|
573 |
group_concat('x')
|
|
574 |
x
|
|
575 |
1
|
|
576 |
drop table t1;
|
|
577 |
CREATE TABLE t1 (id int, a varchar(9));
|
|
578 |
INSERT INTO t1 VALUES
|
|
579 |
(2, ''), (1, ''), (2, 'x'), (1, 'y'), (3, 'z'), (3, '');
|
|
580 |
SELECT GROUP_CONCAT(a) FROM t1;
|
|
581 |
GROUP_CONCAT(a)
|
|
582 |
,,x,y,z,
|
|
583 |
SELECT GROUP_CONCAT(a ORDER BY a) FROM t1;
|
|
584 |
GROUP_CONCAT(a ORDER BY a)
|
|
585 |
,,,x,y,z
|
|
586 |
SELECT GROUP_CONCAT(a) FROM t1 GROUP BY id;
|
|
587 |
GROUP_CONCAT(a)
|
|
588 |
,y
|
|
589 |
,x
|
|
590 |
z,
|
|
591 |
SELECT GROUP_CONCAT(a ORDER BY a) FROM t1 GROUP BY id;
|
|
592 |
GROUP_CONCAT(a ORDER BY a)
|
|
593 |
,y
|
|
594 |
,x
|
|
595 |
,z
|
|
596 |
DROP TABLE t1;
|
|
597 |
create table t1(f1 int);
|
|
598 |
insert into t1 values(1),(2),(3);
|
|
599 |
select f1, group_concat(f1+1) from t1 group by f1 with rollup;
|
|
600 |
f1 group_concat(f1+1)
|
|
601 |
1 2
|
|
602 |
2 3
|
|
603 |
3 4
|
|
604 |
NULL 2,3,4
|
|
605 |
select count(distinct (f1+1)) from t1 group by f1 with rollup;
|
|
606 |
count(distinct (f1+1))
|
|
607 |
1
|
|
608 |
1
|
|
609 |
1
|
|
610 |
3
|
|
611 |
drop table t1;
|
|
612 |
create table t1 (f1 int unsigned, f2 varchar(255));
|
|
613 |
insert into t1 values (1,repeat('a',255)),(2,repeat('b',255));
|
|
614 |
select f2,group_concat(f1) from t1 group by f2;
|
|
615 |
Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
|
|
616 |
def test t1 t1 f2 f2 253 255 255 Y 0 0 8
|
|
617 |
def group_concat(f1) 253 400 1 Y 128 0 63
|
|
618 |
f2 group_concat(f1)
|
|
619 |
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 1
|
|
620 |
bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb 2
|
|
621 |
drop table t1;
|
|
622 |
set names latin1;
|
|
623 |
create table t1 (a char, b char);
|
|
624 |
insert into t1 values ('a', 'a'), ('a', 'b'), ('b', 'a'), ('b', 'b');
|
|
625 |
create table t2 select group_concat(b) as a from t1 where a = 'a';
|
|
626 |
create table t3 (select group_concat(a) as a from t1 where a = 'a') union
|
|
627 |
(select group_concat(b) as a from t1 where a = 'b');
|
|
628 |
select charset(a) from t2;
|
|
629 |
charset(a)
|
|
630 |
latin1
|
|
631 |
select charset(a) from t3;
|
|
632 |
charset(a)
|
|
633 |
latin1
|
|
634 |
latin1
|
|
635 |
drop table t1, t2, t3;
|
|
636 |
set names default;
|
|
637 |
create table t1 (c1 varchar(10), c2 int);
|
|
638 |
select charset(group_concat(c1 order by c2)) from t1;
|
|
639 |
charset(group_concat(c1 order by c2))
|
|
640 |
latin1
|
|
641 |
drop table t1;
|
|
642 |
CREATE TABLE t1 (a INT(10), b LONGTEXT, PRIMARY KEY (a));
|
|
643 |
SET GROUP_CONCAT_MAX_LEN = 20000000;
|
|
644 |
INSERT INTO t1 VALUES (1,REPEAT(CONCAT('A',CAST(CHAR(0) AS BINARY),'B'), 40000));
|
|
645 |
INSERT INTO t1 SELECT a + 1, b FROM t1;
|
|
646 |
SELECT a, CHAR_LENGTH(b) FROM t1;
|
|
647 |
a CHAR_LENGTH(b)
|
|
648 |
1 120000
|
|
649 |
2 120000
|
|
650 |
SELECT CHAR_LENGTH( GROUP_CONCAT(b) ) FROM t1;
|
|
651 |
CHAR_LENGTH( GROUP_CONCAT(b) )
|
|
652 |
240001
|
|
653 |
SET GROUP_CONCAT_MAX_LEN = 1024;
|
|
654 |
DROP TABLE t1;
|
|
655 |
CREATE TABLE t1 (a int, b int);
|
|
656 |
INSERT INTO t1 VALUES (2,1), (1,2), (2,2), (1,3);
|
|
657 |
SELECT GROUP_CONCAT(a), x
|
|
658 |
FROM (SELECT a, GROUP_CONCAT(b) x FROM t1 GROUP BY a) AS s
|
|
659 |
GROUP BY x;
|
|
660 |
GROUP_CONCAT(a) x
|
|
661 |
2 1,2
|
|
662 |
1 2,3
|
|
663 |
DROP TABLE t1;
|
|
664 |
set names utf8;
|
|
665 |
create table t1
|
|
666 |
(
|
|
667 |
x text character set utf8 not null,
|
|
668 |
y integer not null
|
|
669 |
);
|
|
670 |
insert into t1 values (repeat('a', 1022), 0), (repeat(_utf8 0xc3b7, 4), 0);
|
|
671 |
set group_concat_max_len= 1022 + 10;
|
|
672 |
select @x:=group_concat(x) from t1 group by y;
|
|
673 |
select @@group_concat_max_len, length(@x), char_length(@x), right(@x,12), right(HEX(@x),12);
|
|
674 |
@@group_concat_max_len length(@x) char_length(@x) right(@x,12) right(HEX(@x),12)
|
|
675 |
1032 1031 1027 aaaaaaa,÷÷÷÷ C3B7C3B7C3B7
|
|
676 |
set group_concat_max_len= 1022 + 9;
|
|
677 |
select @x:=group_concat(x) from t1 group by y;
|
|
678 |
select @@group_concat_max_len, length(@x), char_length(@x), right(@x,12), right(HEX(@x),12);
|
|
679 |
@@group_concat_max_len length(@x) char_length(@x) right(@x,12) right(HEX(@x),12)
|
|
680 |
1031 1031 1027 aaaaaaa,÷÷÷÷ C3B7C3B7C3B7
|
|
681 |
set group_concat_max_len= 1022 + 8;
|
|
682 |
select @x:=group_concat(x) from t1 group by y;
|
|
683 |
select @@group_concat_max_len, length(@x), char_length(@x), right(@x,12), right(HEX(@x),12);
|
|
684 |
@@group_concat_max_len length(@x) char_length(@x) right(@x,12) right(HEX(@x),12)
|
|
685 |
1030 1029 1026 aaaaaaaa,÷÷÷ C3B7C3B7C3B7
|
|
686 |
set group_concat_max_len= 1022 + 7;
|
|
687 |
select @x:=group_concat(x) from t1 group by y;
|
|
688 |
select @@group_concat_max_len, length(@x), char_length(@x), right(@x,12), right(HEX(@x),12);
|
|
689 |
@@group_concat_max_len length(@x) char_length(@x) right(@x,12) right(HEX(@x),12)
|
|
690 |
1029 1029 1026 aaaaaaaa,÷÷÷ C3B7C3B7C3B7
|
|
691 |
set group_concat_max_len= 1022 + 6;
|
|
692 |
select @x:=group_concat(x) from t1 group by y;
|
|
693 |
select @@group_concat_max_len, length(@x), char_length(@x), right(@x,12), right(HEX(@x),12);
|
|
694 |
@@group_concat_max_len length(@x) char_length(@x) right(@x,12) right(HEX(@x),12)
|
|
695 |
1028 1027 1025 aaaaaaaaa,÷÷ 612CC3B7C3B7
|
|
696 |
set group_concat_max_len= 1022 + 5;
|
|
697 |
select @x:=group_concat(x) from t1 group by y;
|
|
698 |
select @@group_concat_max_len, length(@x), char_length(@x), right(@x,12), right(HEX(@x),12);
|
|
699 |
@@group_concat_max_len length(@x) char_length(@x) right(@x,12) right(HEX(@x),12)
|
|
700 |
1027 1027 1025 aaaaaaaaa,÷÷ 612CC3B7C3B7
|
|
701 |
set group_concat_max_len= 1022 + 4;
|
|
702 |
select @x:=group_concat(x) from t1 group by y;
|
|
703 |
select @@group_concat_max_len, length(@x), char_length(@x), right(@x,12), right(HEX(@x),12);
|
|
704 |
@@group_concat_max_len length(@x) char_length(@x) right(@x,12) right(HEX(@x),12)
|
|
705 |
1026 1025 1024 aaaaaaaaaa,÷ 6161612CC3B7
|
|
706 |
set group_concat_max_len= 1022 + 3;
|
|
707 |
select @x:=group_concat(x) from t1 group by y;
|
|
708 |
select @@group_concat_max_len, length(@x), char_length(@x), right(@x,12), right(HEX(@x),12);
|
|
709 |
@@group_concat_max_len length(@x) char_length(@x) right(@x,12) right(HEX(@x),12)
|
|
710 |
1025 1025 1024 aaaaaaaaaa,÷ 6161612CC3B7
|
|
711 |
set group_concat_max_len= 1022 + 2;
|
|
712 |
select @x:=group_concat(x) from t1 group by y;
|
|
713 |
select @@group_concat_max_len, length(@x), char_length(@x), right(@x,12), right(HEX(@x),12);
|
|
714 |
@@group_concat_max_len length(@x) char_length(@x) right(@x,12) right(HEX(@x),12)
|
|
715 |
1024 1023 1023 aaaaaaaaaaa, 61616161612C
|
|
716 |
set group_concat_max_len= 1022 + 1;
|
|
717 |
select @x:=group_concat(x) from t1 group by y;
|
|
718 |
select @@group_concat_max_len, length(@x), char_length(@x), right(@x,12), right(HEX(@x),12);
|
|
719 |
@@group_concat_max_len length(@x) char_length(@x) right(@x,12) right(HEX(@x),12)
|
|
720 |
1023 1023 1023 aaaaaaaaaaa, 61616161612C
|
|
721 |
drop table t1;
|
|
722 |
set group_concat_max_len=1024;
|
|
723 |
set names latin1;
|
|
724 |
create table t1 (f1 int unsigned, f2 varchar(255));
|
|
725 |
insert into t1 values (1,repeat('a',255)),(2,repeat('b',255));
|
|
726 |
select f2,group_concat(f1) from t1 group by f2;
|
|
727 |
Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
|
|
728 |
def test t1 t1 f2 f2 253 255 255 Y 0 0 8
|
|
729 |
def group_concat(f1) 252 1024 1 Y 128 0 63
|
|
730 |
f2 group_concat(f1)
|
|
731 |
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 1
|
|
732 |
bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb 2
|
|
733 |
drop table t1;
|
|
734 |
CREATE TABLE t1(a TEXT, b CHAR(20));
|
|
735 |
INSERT INTO t1 VALUES ("one.1","one.1"),("two.2","two.2"),("one.3","one.3");
|
|
736 |
SELECT GROUP_CONCAT(DISTINCT UCASE(a)) FROM t1;
|
|
737 |
GROUP_CONCAT(DISTINCT UCASE(a))
|
|
738 |
ONE.1,TWO.2,ONE.3
|
|
739 |
SELECT GROUP_CONCAT(DISTINCT UCASE(b)) FROM t1;
|
|
740 |
GROUP_CONCAT(DISTINCT UCASE(b))
|
|
741 |
ONE.1,TWO.2,ONE.3
|
|
742 |
DROP TABLE t1;
|
|
743 |
CREATE TABLE t1( a VARCHAR( 10 ), b INT );
|
|
744 |
INSERT INTO t1 VALUES ( repeat( 'a', 10 ), 1),
|
|
745 |
( repeat( 'b', 10 ), 2);
|
|
746 |
SET group_concat_max_len = 20;
|
|
747 |
SELECT GROUP_CONCAT( a ) FROM t1;
|
|
748 |
GROUP_CONCAT( a )
|
|
749 |
aaaaaaaaaa,bbbbbbbbb
|
|
750 |
Warnings:
|
|
751 |
Warning 1260 1 line(s) were cut by GROUP_CONCAT()
|
|
752 |
SELECT GROUP_CONCAT( DISTINCT a ) FROM t1;
|
|
753 |
GROUP_CONCAT( DISTINCT a )
|
|
754 |
aaaaaaaaaa,bbbbbbbbb
|
|
755 |
Warnings:
|
|
756 |
Warning 1260 1 line(s) were cut by GROUP_CONCAT()
|
|
757 |
SELECT GROUP_CONCAT( a ORDER BY b ) FROM t1;
|
|
758 |
GROUP_CONCAT( a ORDER BY b )
|
|
759 |
aaaaaaaaaa,bbbbbbbbb
|
|
760 |
Warnings:
|
|
761 |
Warning 1260 1 line(s) were cut by GROUP_CONCAT()
|
|
762 |
SELECT GROUP_CONCAT( DISTINCT a ORDER BY b ) FROM t1;
|
|
763 |
GROUP_CONCAT( DISTINCT a ORDER BY b )
|
|
764 |
aaaaaaaaaa,bbbbbbbbb
|
|
765 |
Warnings:
|
|
766 |
Warning 1260 1 line(s) were cut by GROUP_CONCAT()
|
|
767 |
SET group_concat_max_len = DEFAULT;
|
|
768 |
DROP TABLE t1;
|
|
769 |
SET group_concat_max_len= 65535;
|
|
770 |
CREATE TABLE t1( a TEXT, b INTEGER );
|
|
771 |
INSERT INTO t1 VALUES ( 'a', 0 ), ( 'b', 1 );
|
|
772 |
SELECT GROUP_CONCAT( a ORDER BY b ) FROM t1;
|
|
773 |
GROUP_CONCAT( a ORDER BY b )
|
|
774 |
a,b
|
|
775 |
SELECT GROUP_CONCAT(DISTINCT a ORDER BY b) FROM t1;
|
|
776 |
GROUP_CONCAT(DISTINCT a ORDER BY b)
|
|
777 |
a,b
|
|
778 |
SELECT GROUP_CONCAT(DISTINCT a) FROM t1;
|
|
779 |
GROUP_CONCAT(DISTINCT a)
|
|
780 |
a,b
|
|
781 |
SET group_concat_max_len= 10;
|
|
782 |
SELECT GROUP_CONCAT(a ORDER BY b) FROM t1;
|
|
783 |
GROUP_CONCAT(a ORDER BY b)
|
|
784 |
a,b
|
|
785 |
SELECT GROUP_CONCAT(DISTINCT a ORDER BY b) FROM t1;
|
|
786 |
GROUP_CONCAT(DISTINCT a ORDER BY b)
|
|
787 |
a,b
|
|
788 |
SELECT GROUP_CONCAT(DISTINCT a) FROM t1;
|
|
789 |
GROUP_CONCAT(DISTINCT a)
|
|
790 |
a,b
|
|
791 |
SET group_concat_max_len= 65535;
|
|
792 |
CREATE TABLE t2( a TEXT );
|
|
793 |
INSERT INTO t2 VALUES( REPEAT( 'a', 5000 ) );
|
|
794 |
INSERT INTO t2 VALUES( REPEAT( 'b', 5000 ) );
|
|
795 |
INSERT INTO t2 VALUES( REPEAT( 'a', 5000 ) );
|
|
796 |
SELECT LENGTH( GROUP_CONCAT( DISTINCT a ) ) FROM t2;
|
|
797 |
LENGTH( GROUP_CONCAT( DISTINCT a ) )
|
|
798 |
10001
|
|
799 |
CREATE TABLE t3( a TEXT, b INT );
|
|
800 |
INSERT INTO t3 VALUES( REPEAT( 'a', 65534 ), 1 );
|
|
801 |
INSERT INTO t3 VALUES( REPEAT( 'a', 65535 ), 2 );
|
|
802 |
INSERT INTO t3 VALUES( REPEAT( 'a', 65533 ), 3 );
|
|
803 |
SELECT LENGTH( GROUP_CONCAT( a ) ) FROM t3 WHERE b = 1;
|
|
804 |
LENGTH( GROUP_CONCAT( a ) )
|
|
805 |
65534
|
|
806 |
SELECT LENGTH( GROUP_CONCAT( a ) ) FROM t3 WHERE b = 2;
|
|
807 |
LENGTH( GROUP_CONCAT( a ) )
|
|
808 |
65535
|
|
809 |
SELECT LENGTH( GROUP_CONCAT( a ) ) FROM t3 WHERE b = 3;
|
|
810 |
LENGTH( GROUP_CONCAT( a ) )
|
|
811 |
65533
|
|
812 |
SET group_concat_max_len= DEFAULT;
|
|
813 |
DROP TABLE t1, t2, t3;
|
|
814 |
set names latin1;
|
|
815 |
create table t1 (id int, name varchar(20)) DEFAULT CHARSET=utf8;
|
|
816 |
insert into t1 (id, name) values (1, "óra");
|
|
817 |
insert into t1 (id, name) values (2, "óra");
|
|
818 |
select b.id, group_concat(b.name) from t1 a, t1 b group by b.id;
|
|
819 |
id group_concat(b.name)
|
|
820 |
1 óra,óra
|
|
821 |
2 óra,óra
|
|
822 |
drop table t1;
|
|
823 |
create table t1(a bit not null default 0);
|
|
824 |
insert into t1 values (), (), ();
|
|
825 |
select group_concat(distinct a) from t1;
|
|
826 |
group_concat(distinct a)
|
|
827 |
0
|
|
828 |
select group_concat(distinct a order by a) from t1;
|
|
829 |
group_concat(distinct a order by a)
|
|
830 |
0
|
|
831 |
drop table t1;
|
|
832 |
create table t1(a bit(2) not null);
|
|
833 |
insert into t1 values (1), (0), (0), (3), (1);
|
|
834 |
select group_concat(distinct a) from t1;
|
|
835 |
group_concat(distinct a)
|
|
836 |
1,0,3
|
|
837 |
select group_concat(distinct a order by a) from t1;
|
|
838 |
group_concat(distinct a order by a)
|
|
839 |
0,1,3
|
|
840 |
select group_concat(distinct a order by a desc) from t1;
|
|
841 |
group_concat(distinct a order by a desc)
|
|
842 |
3,1,0
|
|
843 |
drop table t1;
|
|
844 |
create table t1(a bit(2), b varchar(10), c bit);
|
|
845 |
insert into t1 values (1, 'a', 0), (0, 'b', 1), (0, 'c', 0), (3, 'd', 1),
|
|
846 |
(1, 'e', 1), (3, 'f', 1), (0, 'g', 1);
|
|
847 |
select group_concat(distinct a, c) from t1;
|
|
848 |
group_concat(distinct a, c)
|
|
849 |
10,01,00,31,11
|
|
850 |
select group_concat(distinct a, c order by a) from t1;
|
|
851 |
group_concat(distinct a, c order by a)
|
|
852 |
00,01,11,10,31
|
|
853 |
select group_concat(distinct a, c) from t1;
|
|
854 |
group_concat(distinct a, c)
|
|
855 |
10,01,00,31,11
|
|
856 |
select group_concat(distinct a, c order by a, c) from t1;
|
|
857 |
group_concat(distinct a, c order by a, c)
|
|
858 |
00,01,10,11,31
|
|
859 |
select group_concat(distinct a, c order by a desc, c desc) from t1;
|
|
860 |
group_concat(distinct a, c order by a desc, c desc)
|
|
861 |
31,11,10,01,00
|
|
862 |
drop table t1;
|
|
863 |
create table t1 (f1 char(20));
|
|
864 |
insert into t1 values (''),('');
|
|
865 |
select group_concat(distinct f1) from t1;
|
|
866 |
group_concat(distinct f1)
|
|
867 |
||
868 |
select group_concat(f1) from t1; |
|
869 |
group_concat(f1) |
|
870 |
, |
|
871 |
drop table t1; |
|
872 |
CREATE TABLE t1 (a INT, b INT); |
|
873 |
INSERT INTO t1 VALUES (1, 1), (2, 2), (2, 3); |
|
874 |
SELECT GROUP_CONCAT(DISTINCT a ORDER BY b) FROM t1; |
|
875 |
GROUP_CONCAT(DISTINCT a ORDER BY b) |
|
876 |
1,2 |
|
877 |
SELECT GROUP_CONCAT(DISTINCT a ORDER BY b DESC) FROM t1; |
|
878 |
GROUP_CONCAT(DISTINCT a ORDER BY b DESC) |
|
879 |
2,1 |
|
880 |
SELECT GROUP_CONCAT(DISTINCT a) FROM t1; |
|
881 |
GROUP_CONCAT(DISTINCT a) |
|
882 |
1,2 |
|
883 |
SELECT GROUP_CONCAT(DISTINCT a + 1 ORDER BY 3 - b) FROM t1; |
|
884 |
GROUP_CONCAT(DISTINCT a + 1 ORDER BY 3 - b) |
|
885 |
3,2 |
|
886 |
SELECT GROUP_CONCAT(DISTINCT a + 1 ORDER BY b) FROM t1; |
|
887 |
GROUP_CONCAT(DISTINCT a + 1 ORDER BY b) |
|
888 |
2,3 |
|
889 |
SELECT GROUP_CONCAT(a ORDER BY 3 - b) FROM t1; |
|
890 |
GROUP_CONCAT(a ORDER BY 3 - b) |
|
891 |
2,2,1 |
|
892 |
CREATE TABLE t2 (a INT, b INT, c INT, d INT); |
|
893 |
INSERT INTO t2 VALUES (1,1, 1,1), (1,1, 2,2), (1,2, 2,1), (2,1, 1,2); |
|
894 |
SELECT GROUP_CONCAT(DISTINCT a, b ORDER BY c, d) FROM t2; |
|
895 |
GROUP_CONCAT(DISTINCT a, b ORDER BY c, d) |
|
896 |
11,21,12 |
|
897 |
SELECT GROUP_CONCAT(DISTINCT a, b ORDER BY d, c) FROM t2; |
|
898 |
GROUP_CONCAT(DISTINCT a, b ORDER BY d, c) |
|
899 |
11,12,21 |
|
900 |
CREATE TABLE t3 (a INT, b INT, c INT); |
|
901 |
INSERT INTO t3 VALUES (1, 1, 1), (2, 1, 2), (3, 2, 1); |
|
902 |
SELECT GROUP_CONCAT(DISTINCT a, b ORDER BY b, c) FROM t3; |
|
903 |
GROUP_CONCAT(DISTINCT a, b ORDER BY b, c) |
|
904 |
11,21,32 |
|
905 |
SELECT GROUP_CONCAT(DISTINCT a, b ORDER BY c, b) FROM t3; |
|
906 |
GROUP_CONCAT(DISTINCT a, b ORDER BY c, b) |
|
907 |
11,32,21 |
|
908 |
SELECT GROUP_CONCAT(DISTINCT a, b ORDER BY a, b) FROM t1; |
|
909 |
GROUP_CONCAT(DISTINCT a, b ORDER BY a, b) |
|
910 |
11,22,23 |
|
911 |
SELECT GROUP_CONCAT(DISTINCT b, a ORDER BY a, b) FROM t1; |
|
912 |
GROUP_CONCAT(DISTINCT b, a ORDER BY a, b) |
|
913 |
11,22,32 |
|
914 |
SELECT GROUP_CONCAT(DISTINCT a, b ORDER BY b, a) FROM t1; |
|
915 |
GROUP_CONCAT(DISTINCT a, b ORDER BY b, a) |
|
916 |
11,22,23 |
|
917 |
SELECT GROUP_CONCAT(DISTINCT b, a ORDER BY a, b) FROM t1; |
|
918 |
GROUP_CONCAT(DISTINCT b, a ORDER BY a, b) |
|
919 |
11,22,32 |
|
920 |
SELECT GROUP_CONCAT(DISTINCT a ORDER BY a, b) FROM t1; |
|
921 |
GROUP_CONCAT(DISTINCT a ORDER BY a, b) |
|
922 |
1,2 |
|
923 |
SELECT GROUP_CONCAT(DISTINCT b ORDER BY b, a) FROM t1; |
|
924 |
GROUP_CONCAT(DISTINCT b ORDER BY b, a) |
|
925 |
1,2,3 |
|
926 |
SELECT GROUP_CONCAT(DISTINCT a, b ORDER BY a) FROM t1; |
|
927 |
GROUP_CONCAT(DISTINCT a, b ORDER BY a) |
|
928 |
11,23,22 |
|
929 |
SELECT GROUP_CONCAT(DISTINCT b, a ORDER BY b) FROM t1; |
|
930 |
GROUP_CONCAT(DISTINCT b, a ORDER BY b) |
|
931 |
11,22,32 |
|
932 |
DROP TABLE t1, t2, t3; |
|
933 |
CREATE TABLE t1(a INT); |
|
934 |
INSERT INTO t1 VALUES (),(); |
|
935 |
SELECT s1.d1 FROM |
|
936 |
( |
|
937 |
SELECT |
|
938 |
t1.a as d1, |
|
939 |
GROUP_CONCAT(DISTINCT t1.a) AS d2 |
|
940 |
FROM |
|
941 |
t1 AS t1, |
|
942 |
t1 AS t2 |
|
943 |
GROUP BY 1 |
|
944 |
) AS s1; |
|
945 |
d1 |
|
946 |
NULL |
|
947 |
DROP TABLE t1; |
|
948 |
End of 5.0 tests |