Group_concat (), cast (), convert (), and a MySQL interview

Group_concat () is a function provided by the MySQL database. It is usually used together with group by. For details, refer to the MySQL official document: commit.

Syntax:

 
Group_concat ([distinct] expr [, expr...] [order by {unsigned_integer | col_name | expr} [ASC | DESC] [, col_name...] [separator str_val])

1. For example:
Select student_id, group_concat (courses_id) as courses from student_courses where student_id = 2 group by student_id;
+ ------------ + --------- +
| Student_id | courses |
+ ------------ + --------- +
| 2 | 3, 4, 5 |
+ ------------ + --------- +
This requires no PHP loop.
$ ROW = $ PdO-> query ("select student_id, group_concat (courses_id) as courses from student_courses where student_id = 2 group by student_id ");
$ Result = explode (',', $ row ['Course']);

 

2. Of course, the separator can also be customized. The default Delimiter is ",". To change it to "|", use separator. For example:

Select student_id, group_concat (courses_id separator '|') as courses from student_courses where student_id = 2 group by student_id;
+ ------------ + --------- +
| Student_id | courses |
+ ------------ + --------- +
| 2 | 3 | 4 | 5 |
+ ------------ + --------- +

3. In addition, you can sort the values of this group and connect them to strings. For example, sort the values by courses_id in descending order:
Select student_id, group_concat (courses_id order by courses_id DESC) as courses from student_courses where student_id = 2 group by student_id;
+ ------------ + --------- +
| Student_id | courses |
+ ------------ + --------- +
| 2 | 5, 4, 3 |
+ ------------ + --------- +

4. Notes:
 

A.int field connection trap
When using group_concat, note that if the connected field is of the int type, it must be converted to Char and then combined,
Otherwise, after you execute the statement (executescalar or any other method that executes the SQL return result), the returned result will not be a comma-separated string,
Instead, it is byte [].
This problem cannot be found in sqlyog or other tools.
Select group_concat (IPaddress) from t_ip returns comma-separated strings
Select group_concat (ID) from t_ip return byte []
Select group_concat (cast (ID as char) from t_dep returns comma-separated strings
Select group_concat (convert (ID, char) from t_dep returns comma-separated strings
Attached cast and convert usage:
Cast (expr as type), convert (expr, type), convert (expr using transcoding_name)
Cast () and convert () functions can be used to obtain values of one type and generate values of another type.
This type can be one of the following values:
Binary [(n)]
Char [(n)]
Date
Datetime
Decimal
Signed [integer]
Time
Unsigned [integer]
B. Length trap
After group_concat is used, it does not work if limit is used in select.
When using group_concat to connect a field, there is a limit on the length, not the number of connections. But you can set it.
Using group_concat_max_len system variables, you can set the maximum allowed length.
Program The syntax for this operation is as follows, where Val is an unsigned integer:
Set [session | Global] group_concat_max_len = val;
If the maximum length has been set, the result is ended with the maximum length.
Run set global group_concat_max_len = 10 in sqlyog and re-open sqlyog. The setting will take effect.

Group_concat () is used in the following interview question ()

the advertising spaces on the company's websites are carousel. the maximum number of advertisements that can be carousel for a certain advertising space is limited every day. For example, a can only play three advertisements every day, however, the salesperson does not consider this restriction when selling advertising spaces. Therefore, the salesperson must query the contract Table for contracts that exceed the number of advertising spaces for carousel.
mysql> select * From S1;
+ ---- + ------------ + ---------- +
| T1 | T2 | T3 | T4 |
+ ---- + ------------ +
| 1 | A | 2006-11-01 | 2006-11-03 |
| 2 | c | 2006-11-02 | 2006-11-03 |
| 3 | B | 2006-11-01 | 2006-11-04 |
| 4 | A | |
| 5 | c |
| 6 | B |
| 7 | A | 2006- 11-03 |
| 8 | A | 2006-11-04 | 2006-11-05 |
| 9 | c | 2006-11-03 | 2006-11-04 |
| 10 | c | 2006-11-02 | 2006-11-04 |
+ ---- + ------------ +
mysql> select * from S2;
+ ---- +
| T6 | T5 |
+ ---- +
| A | 2 |
| B | 1 |
| c | 3 |
+ ---- +
8 rows in S
ad space a can play a maximum of 2 ads a day, however, in the contract table, there are three advertisements (1, 4, and 7) on the day. For ad spaces A, 1, 4, and 7, they are the final results. If necessary, you can use temporary tables and stored procedures.
the final result is
the T5 value is exceeded
T2 adate value
A 2006-11-01 1
A 2006-11-02

 

Answer:

Create a calendar table calendar and place the date within a period.

SQL code

Mysql> select * From S1; + ---- + ------ + ------------ + | T1 | T2 | T3 | T4 | + ---- + ------ + ------------ + | 1 | A | 2006-11-01 | 2006-11-03 | 2 | C | 3 | B | 4 | A | 5 | c | 6 | B | | 7 | A | 2006-11-02 | 2006-11-03 | 8 | A | 2006-11-04 | 2006-11-05 | 9 | c | 2006-11-03 | 2006-11-04 | 10 | c | 2006-11-02 | 2006-11-04 | + ---- + ------ + ------------ + 10 rows in SET (0.00 Sec) mysql> Create Table calendar (d date primary key); query OK, 0 rows affected (0.08 Sec) mysql> insert into Calendar values-> ('2017-11-01 '), -> ('2017-11-02 '),-> ('2017-11-03'),-> ('2017-11-04 '),-> ('2017-11-05 '), -> ('2017-11-06 '),-> ('2017-11-07'),-> ('2017-11-08 '),-> ('2017-11-09 '), -> ('2017-11-10 '),-> ('2017-11-11'),-> ('2017-11-12 '),-> ('2017-11-13 '), -> ('2017-11-14 '),-> ('2017-11-15'),-> ('2017-11-16 '),-> ('2017-11-17 '), -> ('2017-11-18 '),-> ('2017-11-19'),-> ('2017-11-20 '); query OK, 20 rows affected (2006 Sec) records: 20 duplicates: 0 Warnings: 0 mysql> select s1.t2, C. d, group_concat (s1.t1)-> from calendar C, S1-> where c. d between s1.t3 and s1.t4-> group by s1.t2, C. d; + ------ + ------------ + --------------------- + | T2 | d | group_concat (s1.t1) | + ------ + ------------ + ------------------- + | A | 2006-11-01 | 1 | A | 2006-11-02 | 7,1 | A | 2006-11-03 | 7,1, 4 | A | 2006-11-04 | 8, 4 | A | 2006-11-05 | 8 | B | 2006-11-01 | 3 | B | 2006-11-02 | 6, 3 | B | 2006-11-03 | 6, 3 | B | 2006-11-04 | 6, 3 | B | 2006-11-05 | 6 | c | 2006-11-01 | 5 | c | 2006-11-02 | 5, 10, 2 | c | 9, 10, 2 | c | 9, 10 | + ------ + ------------ + --------------------------- + 14 rows in SET (0.00 Sec) mysql>

Note: The above resources are from the network.