数据库账单做法（SQL SERVER）

一、创建表格

-- 创建表格
CREATE TABLE Expenses (
    ExpenseID INT IDENTITY(1,1) PRIMARY KEY,
    Date DATE,
    Purpose VARCHAR(255),
    Amount DECIMAL(10, 2)
);

二、插入数据

DECLARE @Counter INT = 1;  -- 定义计数
WHILE @Counter <= 20000   --循环20000次--
BEGIN
    DECLARE @RandomDate DATE = DATEADD(DAY, @Counter % 365, '2022-01-01'); --用 @Counter 除以 365 的余数，余数在0-364之间，然后将这个余数加到起始日期 '2023-01-01' 上。
    DECLARE @Purpose VARCHAR(255);
    DECLARE @Amount DECIMAL(10, 2);
    -- 随机选择Purpose
    SET @Purpose = 
        CASE CAST(RAND() * 5 AS INT)
            WHEN 0 THEN 'Salary'                 
            WHEN 1 THEN 'Entertainment'  
            WHEN 2 THEN 'subsidy'  
            WHEN 3 THEN 'Dining'  
            ELSE 'Other'
        END;
    -- 根据Purpose生成Amoun[Salary，subsidy正数 Entertainment，Dining负数 Other随机正负数]
    SET @Amount =
        CASE
            WHEN @Purpose IN ('Salary', 'subsidy') THEN ROUND((RAND() * 1000) + 10, 2)
            WHEN @Purpose IN ('Entertainment', 'Dining') THEN ROUND(((RAND() * -1000) - 10), 2)
            ELSE ROUND((((RAND() * 2) - 1) * 1000) + 10, 2)
        END;
    -- 插入数据
    INSERT INTO Expenses (Date, Purpose, Amount)
    VALUES (@RandomDate, @Purpose, @Amount);
    SET @Counter = @Counter + 1;
    PRINT @Counter-1
END;

三、查询语句

DECLARE @STARTTIME DATE, @ENDTIME DATE;
Set @STARTTIME = '2022-01-15';
Set @ENDTIME = '2022-03-15';

WITH
--期初
FA AS (
SELECT   '' 日期, SUM(Amount) AS 余额 
FROM  Expenses AS  TD 
WHERE TD.Date < @STARTTIME
)
,
--详细每日出入账
DIO AS (
SELECT de2.ExpenseID AS 序号,
		CONVERT(varchar(10),de2.Date,120) AS 日期,
		de2.Purpose AS 项目,
		CASE WHEN de2.Amount >0   THEN de2.Amount ELSE 0 END AS 入账,
		CASE WHEN de2.Amount< 0 THEN -de2.Amount ELSE 0 END AS 出账,
		SUM(CASE WHEN de2.Amount >0   THEN de2.Amount ELSE 0 END - CASE WHEN de2.Amount< 0 THEN -de2.Amount ELSE 0 END )  
		OVER  (ORDER BY  de2.Date,de2.ExpenseID )  AS 余额
FROM Expenses de2
WHERE Date>= @STARTTIME AND Date <= @ENDTIME
)
,
--每日总结
D AS ( 
SELECT TD.日期,
    SUM(TD.入账) AS 入账,
    SUM(TD.出账) AS 出账,
    SUM(TD.入账 - TD.出账) AS 消费差额,
    SUM(SUM(TD.入账 - TD.出账)) OVER (ORDER BY TD.日期)  AS 余额 ,
	'日总结' AS 项目 
FROM
	(SELECT 
		CONVERT(VARCHAR(10), Date, 120) AS 日期,
		Purpose AS 项目,
		CASE WHEN Amount > 0 THEN Amount ELSE 0 END AS 入账,
		CASE WHEN Amount < 0 THEN -Amount ELSE 0 END AS 出账
		FROM Expenses) AS  TD
WHERE TD.日期>= @STARTTIME AND TD.日期 <= @ENDTIME
GROUP BY TD.日期
		
) 
,
-- 月度
M as (
SELECT TD.日期,
       '月收入' AS 项目,
       SUM(TD.入账) AS 入账,
       SUM(TD.出账) AS 出账,
       SUM(TD.入账 - TD.出账) AS 消费差额,
       SUM(SUM(TD.入账 - TD.出账)) OVER (ORDER BY TD.日期)  AS 余额
FROM
        (SELECT
        CONVERT(VARCHAR(7), Date, 120) AS 日期,
        CASE WHEN Amount > 0 THEN Amount ELSE 0 END AS 入账,
        CASE WHEN Amount < 0 THEN -Amount ELSE 0 END AS 出账
    FROM Expenses 
	WHERE Date >= @STARTTIME AND Date <= @ENDTIME) AS TD
GROUP BY TD.日期
) 
,
--年
Y as (
SELECT TD.日期 AS 日期,
         '年收入' AS 项目, 
		SUM(TD.入账) AS 入账, SUM(TD.出账) AS 出账,
		SUM(TD.入账 - TD.出账) AS 消费差额, 
		SUM(SUM(TD.入账 - TD.出账)) OVER (ORDER BY TD.日期) AS 余额
FROM
    (SELECT
        CONVERT(VARCHAR(4), Date, 120) AS 日期,
        Purpose AS 项目,
        CASE WHEN Amount > 0 THEN Amount ELSE 0 END AS 入账,
        CASE WHEN Amount < 0 THEN -Amount ELSE 0 END AS 出账
    FROM Expenses
	WHERE Date >= @STARTTIME AND Date <= @ENDTIME) TD
GROUP BY TD.日期
)

SELECT TOTAL.日期,TOTAL.项目,TOTAL.入账,TOTAL.出账,TOTAL.余额 FROM (
	--期初
	SELECT
	    '' AS 年,
		0 AS 年排序,
		'' AS 月,
		0 AS 月排序,
		AT.日期,
		0 AS 序号,
		1 AS 排序,
		'期初' AS 项目,
		0 AS 入账,
		0 AS 出账,
		0 AS 消费差额,
		AT.余额
	FROM FA  AS AT
	UNION ALL

	--每天消费明细
	SELECT
	     Convert(varchar(4), DIO.日期, 120) AS 年 ,
		 0 AS 年排序,
		 Convert(varchar(7), DIO.日期, 120) AS 月,
		 0 AS 月排序,
		  DIO.日期 ,
		  DIO.序号,
		  2  AS 排序,	
		  DIO.项目,
		  DIO.入账,
		  DIO.出账, 
		  0 AS 消费差额,
		  DIO.余额+FA.余额
	FROM DIO
	CROSS JOIN FA

	UNION ALL
	--每天的消费总结
	SELECT  
	   	 Convert(varchar(4), D.日期, 120) AS 年 ,
		 0 AS 年排序,
		 Convert(varchar(7), D.日期, 120) AS 月,
		 0 AS 月排序,
		 D.日期,
		 0 AS 序号,
		 3 AS 排序,
		 D.项目,
		 D.入账,
		 D.出账,
		 D.消费差额,
		 D.余额+FA.余额
	FROM D
	CROSS JOIN FA

	UNION ALL
	--每个月的消费总结
	SELECT  
	     Convert(varchar(4), M.日期, 120) AS 年 ,
		 0 AS 年排序,
		 Convert(varchar(7), M.日期, 120) AS 月,
		 1 AS 月排序,
		 M.日期,
		 0 AS 序号,
		 4 AS 排序,
		 M.项目,
		 M.入账,
		 M.出账,
		 M.消费差额,
		 M.余额+FA.余额
	FROM M
	CROSS JOIN FA

	UNION ALL
	--年消费总结
	SELECT  
	    Convert(varchar(4), Y.日期, 120) AS 年,
		1 AS 年排序,
		'' AS 月,
		0 AS 月排序,
		 Y.日期,
		 0 AS 序号,
		5 AS 排序,
		 Y.项目,
		 Y.入账,
		 Y.出账,
		 Y.消费差额,
		 Y.余额+FA.余额
	FROM Y
	CROSS JOIN FA
) AS TOTAL    
ORDER BY TOTAL.年,TOTAL.年排序,TOTAL.月,TOTAL.月排序,TOTAL.日期,TOTAL.排序,TOTAL.序号  

总结

备注:
期初:就是查询的天数之前的累计金额
查询的表格要包括:期初，每日出入账详情，每日消费总结，月度总结，年度总结
思路是将每个部分进行拆分，详细出入账详情，每日消费总结，月度总结，年度总结，最后使用union all将其连接起来进行排序