统计今日、昨日、本周、本月、上月、今年、去年、全部的内容条目数量的SQL语句

SELECT 
    '今日' AS time_range,
    COUNT(*) AS count
FROM 
    content_table
WHERE 
    DATE(date_column) = CURDATE()

UNION ALL

SELECT 
    '昨日' AS time_range,
    COUNT(*) AS count
FROM 
    content_table
WHERE 
    DATE(date_column) = DATE_SUB(CURDATE(), INTERVAL 1 DAY)

UNION ALL

SELECT 
    '本周' AS time_range,
    COUNT(*) AS count
FROM 
    content_table
WHERE 
    YEARWEEK(date_column) = YEARWEEK(CURDATE())

UNION ALL

SELECT 
    '本月' AS time_range,
    COUNT(*) AS count
FROM 
    content_table
WHERE 
    YEAR(date_column) = YEAR(CURDATE()) AND MONTH(date_column) = MONTH(CURDATE())

UNION ALL

SELECT 
    '上月' AS time_range,
    COUNT(*) AS count
FROM 
    content_table
WHERE 
    YEAR(date_column) = YEAR(CURDATE()) AND MONTH(date_column) = MONTH(CURDATE()) - 1

UNION ALL

SELECT 
    '今年' AS time_range,
    COUNT(*) AS count
FROM 
    content_table
WHERE 
    YEAR(date_column) = YEAR(CURDATE())

UNION ALL

SELECT 
    '去年' AS time_range,
    COUNT(*) AS count
FROM 
    content_table
WHERE 
    YEAR(date_column) = YEAR(CURDATE()) - 1

UNION ALL

SELECT 
    '全部' AS time_range,
    COUNT(*) AS count
FROM 
    content_table;

解释：

• 每个子查询使用 COUNT(*) 统计对应时间范围内的内容条目数量。
• CURDATE() 函数用于获取当前日期。
• DATE(date_column) 用于提取日期部分。
• YEARWEEK(date_column) 用于获取年份和周数的组合。
• YEAR(date_column) 和 MONTH(date_column) 用于提取年份
• content_table 是存储内容条目的表名，date_column 是存储条目日期的列名。