环比月、年

最新推荐文章于 2024-07-16 09:55:20 发布

民义

最新推荐文章于 2024-07-16 09:55:20 发布

阅读量271

点赞数

CC 4.0 BY-SA版权

分类专栏： C#

本文链接：https://blog.youkuaiyun.com/qq_43016856/article/details/98945287

C# 专栏收录该内容

1 篇文章

订阅专栏

本文深入解析了使用SQL构造复杂查询的过程，重点介绍了如何根据不同的时间条件动态生成SQL语句，实现对请求数据按月或按年的统计分析。通过具体代码示例，详细展示了参数化查询的构建方法，包括字段描述获取、时间部分处理、左连接及分组策略等关键步骤。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

string fieldName = ReportDimension.GetFieldDesc4Equipment(fieldID);
string monthOrYear = (endYear==0)?“MONTH”:“YEAR”;
string monthOrYearLeftJoinStr = (endYear == 0) ? " AND DATEPART(YEAR,r1.RequestDate) = DATEPART(YEAR ,r2.RequestDate) " : “”;
string monthOrYearWhereStr = (endYear == 0) ? " AND DATEPART(YEAR,r1.RequestDate)= @StartYear " : " AND DATEPART(YEAR ,r1.RequestDate)>=@StartYear AND DATEPART(YEAR,r1.RequestDate)<=@EndYear ";

        sqlStr = string.Format("SELECT {0}, DATEPART({1} ,r1.RequestDate) AS monthOrYear,COUNT(DISTINCT r1.ID) AS now,COUNT(DISTINCT r2.ID) AS pre "+
                              " FROM tblRequest r1 "+

                              " LEFT JOIN jctRequestEqpt re ON re.RequestID =r1.ID  "+
                              " LEFT JOIN tblEquipment e ON e.ID =re.EquipmentID "+
                              " LEFT JOIN ( "+

                              " SELECT sr.ID ID,sr.RequestDate,sr.RequestType,{0} " +
                              " FROM tblEquipment e  "+
                              " LEFT JOIN jctRequestEqpt sre ON sre.EquipmentID = e.ID "+
                              " LEFT JOIN tblRequest sr ON sr.ID =sre.RequestID "+

                              " ) r2 ON r1.RequestType =r2.RequestType  "+
                              " AND DATEPART({1},r1.RequestDate) = DATEPART({1} ,r2.RequestDate)+1  {2}" +
                              " AND r2{4} = {0} " +

                              " WHERE r1.RequestType=@RequestType {3} AND re.RequestID IS NOT NULL AND re.EquipmentID IS NOT NULL "+
                              " GROUP BY DATEPART({1} ,r1.RequestDate) ,{0}",
                              fieldName,
                              monthOrYear,
                              monthOrYearLeftJoinStr,
                              monthOrYearWhereStr,
                              fieldName.Substring(fieldName.IndexOf(".")));
        using (SqlCommand command = ConnectionUtil.GetCommand(sqlStr))
        {
            command.Parameters.Add("@RequestType", SqlDbType.Int).Value = (type == RequestInfo.RequestTypes.Recall) ? RequestInfo.RequestTypes.Inspection : type;
            command.Parameters.Add("@StartYear", SqlDbType.Int).Value = startYear;
            if (endYear != 0)
                command.Parameters.Add("@EndrtYear", SqlDbType.Int).Value = endYear;

            return GetDataTable(command);
        }

环比 月、年

环比月、年