每天努力一点之SQL
时间:2022-03-10 16:59
今天工作当中遇到一个问题:统计信息并导出EXcel 报表。
刚开始只做了统计信息:
如下图
请看最后一列的数据。
我当时想都从数据库里取出来,但是由于我能力有限没有做出来。先贴下后来写的SQL 语句。
?
alter PROCEDURE spread_GetAuthoInfoByStatistics(
@ Count
int
)
AS
SET
NOCOUNT ON ;
BEGIN
DECLARE @hitsHistory INT
if(@ Count =0)
begin
SELECT @hitsHistory= COUNT (1) FROM
dbo.AuthinfoAct
; WITH
TempTable
AS (
SELECT
YEAR (AddTime) [ Year ], COUNT (1) AS
Total --, @hitsHistory,@hitsHistory+COUNT(1)
FROM dbo.AuthinfoAct
GROUP BY YEAR (AddTime)
)
SELECT
A. Year , A.Total, SUM (B.Total) AcumulateTotal
FROM TempTable A, TempTable B
WHERE A. Year >=B. Year
GROUP BY A. Year ,A.Total order
by A. Year
end
else
begin
SELECT
@hitsHistory= COUNT (1) FROM
dbo.AuthinfoAct WHERE
YEAR (AddTime) != YEAR (GETDATE());
WITH TempTable
AS (
SELECT
MONTH (AddTime) [ Month ], COUNT (1) AS
Total --, @hitsHistory,@hitsHistory+COUNT(1)
FROM dbo.AuthinfoAct
WHERE YEAR (AddTime) = YEAR (GETDATE())
GROUP BY MONTH (AddTime)
)
SELECT
A.[ Month ], A.Total, SUM (B.Total) +@hitsHistory AcumulateTotal
FROM TempTable A, TempTable B
WHERE A.[ Month ]>=B.[ Month ]
GROUP BY A.[ Month ],A.Total
end
END
GO |
看着也挺简单的是吧,但是我当初想的比较麻烦还想到了递归。
下面就说说我第一次怎么做的吧。、
第一次从数据库统计好第一列,第二列的数据。
最后一列的数据我利用代码的方便性写的。
如下:
var Num = 0; for (int i = 0; i < models.Count; i++) { if (i == 0) { <tr> <td class="edit">@models[i].Item2</td> <td>@models[i].Item1</td> <td>@models[i].Item3</td> <td>@(models[i].Item1+visitHis.Value) @{ Num = models[i].Item1 + visitHis.Value;} </td> </tr> } else { <tr> <td class="edit">@models[i].Item2</td> <td>@models[i].Item1</td> <td>@(models[i].Item1 + Num) @{ Num = models[i].Item1 + Num;} </td> </tr> } }
还是先处理数据好啊。
每天努力一点之SQL,gxlsystem