asp.net – 计算SQL中值的总和,以显示每月名称

发布时间：2020-12-16 03:34:02 所属栏目：asp.Net 来源：网络整理

我有一个具有以下布局的表格.

Email Blast Table

EmailBlastId |  FrequencyId | UserId
---------------------------------
1            |   5          |   1
2            |   2          |   1
3            |   4          |   1


Frequency Table

Id | Frequency 
------------
 1 |  Daily
 2 |  Weekly
 3 |  Monthly
 4 |  Quarterly
 5 |  Bi-weekly

我需要在我的asp.net页面上提供一个网格显示,如下所示.

Email blasts per month.

UserId | Jan | Feb | Mar | Apr |..... Dec | Cumulative
-----------------------------------------------------
1        7      6     6     7          6     #xx

我能想到这样做的唯一方法如下,每个月都有一个案例陈述.

select SUM(
        CASE WHEN FrequencyId = 1 THEN 31 
        WHEN FrequencyId = 2 THEN 4
        WHEN FrequencyId = 3 THEN 1
        WHEN FrequencyId = 4 THEN 1
        WHEN FrequencyId = 5 THEN 2 END) AS Jan,SUM(
        CASE WHEN FrequencyId = 1 THEN 28 (29 - leap year)
        WHEN FrequencyId = 2 THEN 4
        WHEN FrequencyId = 3 THEN 1
        WHEN FrequencyId = 4 THEN 0
        WHEN FrequencyId = 5 THEN 2 END) AS Feb,etc etc
FROM EmailBlast 
Group BY UserId

还有其他更好的方法吗？

解决方法

这是在任何一年吗？我假设你想要当年的时间表.如果您想要未来的一年,您可以随时更改DECLARE @now以指定任何将来的日期.

“一周两周”(通常称为“每两周一次”)并不适合每月一桶(2月份非闰年除外).这可能会改为“一个月两次”吗？

另外,为什么不将系数存储在频率表中,添加一个名为“PerMonth”的列？那么你只需要处理每日和每季度的案例(这是一个随意的选择,这种情况只会发生在1月,4月等等吗？).

假设其中一些是灵活的,这就是我建议的,假设对表模式进行了这么小的改动：

USE tempdb;
GO

CREATE TABLE dbo.Frequency 
(
    Id INT PRIMARY KEY,Frequency VARCHAR(32),PerMonth TINYINT
);

CREATE TABLE dbo.EmailBlast 
(
    Id INT,FrequencyId INT,UserId INT
);

而这个样本数据：

INSERT dbo.Frequency(Id,Frequency,PerMonth)
  SELECT 1,'Daily',NULL
  UNION ALL SELECT 2,'Weekly',4
  UNION ALL SELECT 3,'Monthly',1
  UNION ALL SELECT 4,'Quarterly',NULL
  UNION ALL SELECT 5,'Twice a month',2;

INSERT dbo.EmailBlast(Id,FrequencyId,UserId)
  SELECT 1,5,1
  UNION ALL SELECT 2,2,1
  UNION ALL SELECT 3,4,1;

我们可以使用非常复杂的查询来完成此任务(但我们不必对这些月份数进行硬编码)：

DECLARE @now DATE = CURRENT_TIMESTAMP;
DECLARE @Jan1 DATE = DATEADD(MONTH,1-MONTH(@now),DATEADD(DAY,1-DAY(@now),@now));

WITH n(m) AS 
(
    SELECT TOP 12 m = number
        FROM master.dbo.spt_values
        WHERE number > 0 GROUP BY number
),months(MNum,MName,StartDate,NumDays) AS
(    SELECT m,mn = CONVERT(CHAR(3),DATENAME(MONTH,DATEADD(MONTH,m-1,@Jan1))),@Jan1),DATEDIFF(DAY,m,@Jan1))
    FROM n
),grp AS
(
    SELECT UserId,c = SUM (
        CASE x.Id WHEN 1 THEN NumDays
            WHEN 4 THEN CASE WHEN MNum % 3 = 1 THEN 1 ELSE 0 END
            ELSE x.PerMonth END )
    FROM months CROSS JOIN (SELECT e.UserId,f.* 
        FROM EmailBlast AS e 
        INNER JOIN Frequency AS f
        ON e.FrequencyId = f.Id) AS x
    GROUP BY UserId,MName
),cumulative(UserId,total) AS
(
    SELECT UserId,SUM(c)
      FROM grp GROUP BY UserID
),pivoted AS
(
    SELECT * FROM (SELECT UserId,c,MName FROM grp) AS grp 
    PIVOT(MAX(c) FOR MName IN (
        [Jan],[Feb],[Mar],[Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec])
    ) AS pvt
)
SELECT p.*,c.total 
    FROM pivoted AS p
    LEFT OUTER JOIN cumulative AS c
    ON p.UserId = c.UserId;

结果：

UserId  Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec total
1       7   6   6   7   6   6   7   6   6   7   6   6   76

清理：

DROP TABLE dbo.EmailBlast,dbo.Frequency;
GO

事实上,我建议的架构更改并不会给你带来太多的好处,它只会在grp CTE中为你节省两个额外的CASE分支.总的来说,花生.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!