温故知新-MYSQL-一些查询技巧（二）

通过出生年月查询平均年龄

SELECT
    avg(
        YEAR (
            from_days(
            datediff( now(), birthday )))) 
FROM
    table_name 
WHERE
    ...

case when 语法查询分类信息

SELECT
    name,
CASE
    area 
    WHEN 1 THEN
    '吴江开发区' 
    WHEN 2 THEN
    '汾湖高新区' 
    WHEN 3 THEN
    '东太湖度假区' 
ELSE '未知' 
END as area_name,
sum( number )
FROM
    table_name
WHERE
...

根据时间（日期）分组查询

存储的为时分秒，要按照天分组

SELECT
    count(*) AS num,
    date_format( created_at, '%Y-%m-%d' )  as day_str
FROM
    table_name 
WHERE
    ...
GROUP BY
    date_format( created_at, '%Y-%m-%d' )