如何获取记账数据库中的数据

每日统计
SELECT 
       SUM(CASE WHEN type = '1' THEN money ELSE 0 END) AS total_in,
       SUM(CASE WHEN type = '2' THEN money ELSE 0 END) AS total_out,
       DATE_FORMAT(FROM_UNIXTIME(date),'%Y-%m-%d') as day
FROM jjjshop_bookkeeper_record
WHERE DATE_FORMAT(FROM_UNIXTIME(date),'%Y-%m-%d')='2023-08-28'
GROUP BY DATE_FORMAT(FROM_UNIXTIME(date),'%Y-%m-%d');

每月统计
SELECT 
       SUM(CASE WHEN type = '1' THEN money ELSE 0 END) AS total_in,
       SUM(CASE WHEN type = '2' THEN money ELSE 0 END) AS total_out,
       DATE_FORMAT(FROM_UNIXTIME(date),'%Y-%m') as day
FROM jjjshop_bookkeeper_record
WHERE DATE_FORMAT(FROM_UNIXTIME(date),'%Y-%m')='2023-08'
GROUP BY DATE_FORMAT(FROM_UNIXTIME(date),'%Y-%m');

查询当月所有记录
SELECT *, DATE_FORMAT(FROM_UNIXTIME(date),'%Y-%m') as day FROM jjjshop_bookkeeper_record WHERE DATE_FORMAT(FROM_UNIXTIME(date),'%Y-%m')='2023-08';


每年统计
SELECT 
       SUM(CASE WHEN type = '1' THEN money ELSE 0 END) AS total_in,
       SUM(CASE WHEN type = '2' THEN money ELSE 0 END) AS total_out,
       DATE_FORMAT(FROM_UNIXTIME(date),'%Y') as day
FROM jjjshop_bookkeeper_record
WHERE DATE_FORMAT(FROM_UNIXTIME(date),'%Y')='2023'
GROUP BY DATE_FORMAT(FROM_UNIXTIME(date),'%Y');

通过上边的语句查询月度汇总

然后查询，当月每天的数据汇总

SELECT SUM(CASE WHEN type = '1' THEN money ELSE 0 END) AS total_in, SUM(CASE WHEN type = '2' THEN money ELSE 0 END) AS total_out, DATE_FORMAT(FROM_UNIXTIME(date),'%Y-%m-%d') as day FROM jjjshop_bookkeeper_record WHERE DATE_FORMAT(FROM_UNIXTIME(date),'%Y-%m')='2023-08' GROUP BY DATE_FORMAT(FROM_UNIXTIME(date),'%Y-%m-%d');

最后查询出每条数据

SELECT *, DATE_FORMAT(FROM_UNIXTIME(date),'%Y-%m-%d') as day FROM jjjshop_bookkeeper_record WHERE DATE_FORMAT(FROM_UNIXTIME(date),'%Y-%m')='2023-08';

获取数据排行

SELECT remarks,sum(money) as money,label_id, DATE_FORMAT(FROM_UNIXTIME(date),'%Y-%m-%d') as day FROM jjjshop_bookkeeper_record WHERE DATE_FORMAT(FROM_UNIXTIME(date),'%Y-%m')='2023-08' GROUP BY label_id ORDER BY money desc;