SQL 按月分组求和，没有数据补0效果图主数据表核心语句筛选结果：思路分析：完整代码:后续

181 阅读 0 评论 120 点赞

我是靠谱客的博主柔弱小懒猪，这篇文章主要介绍SQL 按月分组求和，没有数据补0效果图主数据表核心语句筛选结果：思路分析：完整代码:后续，现在分享给大家，希望可以做个参考。

效果图

先附上一张效果图，
前端选择
起止日期:DateStart，
截止日期:DateEnd月份
备件属性:SparePartAttributeIDSrch，
返回每月的求和数据，月份数据没有的补充0
表格：EasyUI datagrid,折线图：Echarts
在这里插入图片描述

主数据表

表名：UPMEquipConsume
备件属性：SparePartAttributeID
总价：Dmbtr
在这里插入图片描述

核心语句

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
select ROW_NUMBER() OVER (ORDER BY a.UseDate ASC) AS id,a.* 
from(
SELECT top 100 percent
 m AS 'UseDate',
 SUM ( CASE WHEN CONVERT ( VARCHAR ( 7 ), c.UseDate, 120 ) = m THEN c.Dmbtr ELSE 0 END ) AS 'bpbpxh' 
FROM
 ( SELECT '2020-12' m UNION ALL SELECT '2021-01' UNION ALL SELECT '2021-02' UNION ALL SELECT '2021-03' ) aa
 LEFT JOIN ( SELECT * FROM UPMEquipConsume WHERE SparePartAttributeID = 1 ) c ON CONVERT ( VARCHAR ( 7 ), c.UseDate, 120 ) = aa.m 
GROUP BY
 m 
ORDER BY m) a

筛选结果：

在这里插入图片描述

思路分析：

根据选择的起止月份，动态创建表m，然后左连接的主数据表UPMEquipConsume

复制代码

1
2
SELECT '2020-12' m UNION ALL SELECT '2021-01' UNION ALL SELECT '2021-02' UNION ALL SELECT '2021-03'

在这里插入图片描述

完整代码:

sqlZong：核心语句。
sqlZongNew：在核心语句的基础上加入分页功能。

复制代码

int page = PublicMethod.GetInt("page") == 0 ? 1 : PublicMethod.GetInt("page");
            int rows = PublicMethod.GetInt("rows") == 0 ? 10 : PublicMethod.GetInt("rows");
            int totalRead = (page - 1) * rows;

string SparePartAttributeID = PublicMethod.GetString("SparePartAttributeIDSrch");
            DateTime DateStart = PublicMethod.GetDateTime("DateStart");
            DateTime DateEnd = PublicMethod.GetDateTime("DateEnd");

string DateStartNew = DateStart.ToString("yyyy-MM");//2020-11
            //string DateStartNew = DateEnd.ToString("yyyy-MM");//2021-02

string StartYear = DateStart.ToString("yyyy");//2020
            string StartMonth = DateStart.ToString("MM"); //11

string EndYear = DateEnd.ToString("yyyy"); //2021
            string EndMonth = DateEnd.ToString("MM");  //02

int sm = int.Parse(StartMonth);//11
            int em = int.Parse(EndMonth);//2

string tempHead = "select ROW_NUMBER() OVER (ORDER BY a.UseDate ASC) AS id,a.* from( select top 100 percent m as 'UseDate', "
             + "SUM ( CASE WHEN CONVERT ( VARCHAR ( 7 ), c.UseDate, 120 ) = m THEN c.Dmbtr ELSE 0 END ) AS 'bpbpxh'  "
             + " from ( ";

string tempsq = " select '" + DateStartNew + "' m";
            if (StartYear != EndYear) //跨年
            {
                int startyear = int.Parse(StartYear);
                int endyear = int.Parse(EndYear);

//首年
                for (int i = sm + 1; i <= 12; i++)
                {

if (i < 10)
                        tempsq += " union all select '" + StartYear + "-0" + i + "'";
                    else
                        tempsq += " union all select '" + StartYear + "-" + i + "'";

}
                //中间
                if (endyear - startyear > 1)
                {

for (int i = startyear + 1; i <= endyear - 1; i++)
                    {
                        for (int j = 1; j <= 12; j++)
                        {

if (j < 10)
                                tempsq += " union all select '" + i + "-0" + j + "'";
                            else
                                tempsq += " union all select '" + i + "-" + j + "'";

}
                    }

}
                //尾年
                for (int i = 1; i <= em; i++)
                {
                    if (i < 10)
                        tempsq += " union all select '" + EndYear + "-0" + i + "'";
                    else
                        tempsq += " union all select '" + EndYear + "-" + i + "'";
                }
            }
            else //在同一年
            {

for (int i = sm + 1; i <= em; i++)
                {
                    if (i < 10)
                        tempsq += " union all select '" + EndYear + "-0" + i + "'";
                    else
                        tempsq += " union all select '" + EndYear + "-" + i + "'";
                }

}

tempsq += ") aa ";

string sqlZong = tempHead + tempsq
                  + " left join (SELECT * FROM UPMEquipConsume where SparePartAttributeID =" + SparePartAttributeID + ") c on CONVERT ( VARCHAR ( 7 ), c.UseDate, 120 ) = aa.m "
                  + " group by m ORDER BY m) a";

string sqlZongNew = "SELECT TOP " + rows + " UseDate, bpbpxh from ("
                      + sqlZong + ")AA where ID not in ( select top " + totalRead + "id from("
                      + sqlZong + ")AA  ORDER BY UseDate ASC )   ORDER BY UseDate ASC";

DataTable dt = CommAppKfzx.BLL.CommonToolBLL.SqlSelect(sqlZongNew);
            DataTable totalDT = CommAppKfzx.BLL.CommonToolBLL.SqlSelect(sqlZong);
            int totalInt = totalDT.Rows.Count;
            string jsonStr = JsonHelper.GetEasyUICombogridJsonByTable(dt, totalInt);
            context.Response.Write(jsonStr);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
            int page = PublicMethod.GetInt("page") == 0 ? 1 : PublicMethod.GetInt("page");
            int rows = PublicMethod.GetInt("rows") == 0 ? 10 : PublicMethod.GetInt("rows");
            int totalRead = (page - 1) * rows;



            string SparePartAttributeID = PublicMethod.GetString("SparePartAttributeIDSrch");
            DateTime DateStart = PublicMethod.GetDateTime("DateStart");
            DateTime DateEnd = PublicMethod.GetDateTime("DateEnd");


            string DateStartNew = DateStart.ToString("yyyy-MM");//2020-11
            //string DateStartNew = DateEnd.ToString("yyyy-MM");//2021-02

            string StartYear = DateStart.ToString("yyyy");//2020
            string StartMonth = DateStart.ToString("MM"); //11

            string EndYear = DateEnd.ToString("yyyy"); //2021
            string EndMonth = DateEnd.ToString("MM");  //02


            int sm = int.Parse(StartMonth);//11
            int em = int.Parse(EndMonth);//2


            string tempHead = "select ROW_NUMBER() OVER (ORDER BY a.UseDate ASC) AS id,a.* from( select top 100 percent m as 'UseDate', "
             + "SUM ( CASE WHEN CONVERT ( VARCHAR ( 7 ), c.UseDate, 120 ) = m THEN c.Dmbtr ELSE 0 END ) AS 'bpbpxh'  "
             + " from ( ";

            string tempsq = " select '" + DateStartNew + "' m";
            if (StartYear != EndYear) //跨年
            {
                int startyear = int.Parse(StartYear);
                int endyear = int.Parse(EndYear);

                //首年
                for (int i = sm + 1; i <= 12; i++)
                {

                    if (i < 10)
                        tempsq += " union all select '" + StartYear + "-0" + i + "'";
                    else
                        tempsq += " union all select '" + StartYear + "-" + i + "'";

                }
                //中间
                if (endyear - startyear > 1)
                {

                    for (int i = startyear + 1; i <= endyear - 1; i++)
                    {
                        for (int j = 1; j <= 12; j++)
                        {

                            if (j < 10)
                                tempsq += " union all select '" + i + "-0" + j + "'";
                            else
                                tempsq += " union all select '" + i + "-" + j + "'";

                        }
                    }

                }
                //尾年
                for (int i = 1; i <= em; i++)
                {
                    if (i < 10)
                        tempsq += " union all select '" + EndYear + "-0" + i + "'";
                    else
                        tempsq += " union all select '" + EndYear + "-" + i + "'";
                }
            }
            else //在同一年
            {

                for (int i = sm + 1; i <= em; i++)
                {
                    if (i < 10)
                        tempsq += " union all select '" + EndYear + "-0" + i + "'";
                    else
                        tempsq += " union all select '" + EndYear + "-" + i + "'";
                }

            }

            tempsq += ") aa ";

            string sqlZong = tempHead + tempsq
                  + " left join (SELECT * FROM UPMEquipConsume where SparePartAttributeID =" + SparePartAttributeID + ") c on CONVERT ( VARCHAR ( 7 ), c.UseDate, 120 ) = aa.m "
                  + " group by m ORDER BY m) a";

            string sqlZongNew = "SELECT TOP " + rows + " UseDate, bpbpxh from ("
                      + sqlZong + ")AA where ID not in ( select top " + totalRead + "id from("
                      + sqlZong + ")AA  ORDER BY UseDate ASC )   ORDER BY UseDate ASC";


            DataTable dt = CommAppKfzx.BLL.CommonToolBLL.SqlSelect(sqlZongNew);
            DataTable totalDT = CommAppKfzx.BLL.CommonToolBLL.SqlSelect(sqlZong);
            int totalInt = totalDT.Rows.Count;
            string jsonStr = JsonHelper.GetEasyUICombogridJsonByTable(dt, totalInt);
            context.Response.Write(jsonStr);

后续

前期也是查询了大量资料，相关文章都在这里，感谢以下博客大大提供的灵感，感谢，感谢，感谢！
SQL统计1-12月的数据，没有数据的月份显示为0
mysql 按月统计数据没有数据按0补全
SQL统计1-12月的数据，没有数据的月份显示为0
mysql按日期分组（group by）查询统计的时候，没有数据补0的解决办法
MySQL之统计查询，按月查询每天数据，无数据自动填充0