GROUP_CONCAT() ,CAST() 和CONVERT()和一个mysql面试题

解答：

建立一个日历表 calendar 放上一段期间内的日期 SQL code

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    | 2006-11-03 | 2006-11-04 |
|  5 | C    | 2006-11-01 | 2006-11-02 |
|  6 | B    | 2006-11-02 | 2006-11-05 |
|  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
    -> ('2006-11-01'),
    -> ('2006-11-02'),
    -> ('2006-11-03'),
    -> ('2006-11-04'),
    -> ('2006-11-05'),
    -> ('2006-11-06'),
    -> ('2006-11-07'),
    -> ('2006-11-08'),
    -> ('2006-11-09'),
    -> ('2006-11-10'),
    -> ('2006-11-11'),
    -> ('2006-11-12'),
    -> ('2006-11-13'),
    -> ('2006-11-14'),
    -> ('2006-11-15'),
    -> ('2006-11-16'),
    -> ('2006-11-17'),
    -> ('2006-11-18'),
    -> ('2006-11-19'),
    -> ('2006-11-20');
Query OK, 20 rows affected (0.06 sec)
Records: 20  Duplicates: 0  Warnings: 0

mysql>
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    | 2006-11-03 | 9,10,2              |
| C    | 2006-11-04 | 9,10                |
+------+------------+---------------------+
14 rows in set (0.00 sec)

mysql>

注：以上资源来源于网络