C# Winform 使用SUM CASE WHEN实现动态列交叉数据报表|C/S框架网

C# Winform 使用SUM CASE WHEN实现动态列交叉数据报表

作者:作者不详发布日期:2021/06/28 19:15:54

C# Winform 使用SUM CASE WHEN实现动态列交叉数据报表

使用SUM CASE WHEN实现动态列交叉报表

a) 根据查询条件返回不同字段的结果数据。

b) 根据查询条件自动创建表格的列。

1. 按年度生成动态列的SQL脚本

SQL Code:

SELECT Account,OperationType,
SUM(CASE WHEN YEAR(OperationTime)=2015 THEN 1 ELSE 0 END) AS Y2015,
SUM(CASE WHEN YEAR(OperationTime)= 2016 THEN 1 ELSE 0 END) AS Y2016,
SUM(CASE WHEN YEAR(OperationTime)= 2017 THEN 1 ELSE 0 END) AS Y2017,
SUM(CASE WHEN YEAR(OperationTime)= 2018 THEN 1 ELSE 0 END) AS Y2018,
SUM(CASE WHEN YEAR(OperationTime)= 2019 THEN 1 ELSE 0 END) AS Y2019,
SUM(CASE WHEN YEAR(OperationTime)= 2020 THEN 1 ELSE 0 END) AS Y2020,
SUM(CASE WHEN YEAR(OperationTime)= 2021 THEN 1 ELSE 0 END) AS Y2021
FROM sys_LogOperation
GROUP BY Account,OperationType;

//来源:C/S框架网 | www.csframework.com | QQ:23404761

2. 按固定项目生成动态列的SQL脚本

SQL Code:

SELECT YEAR(OperationTime) AS YearItem,
SUM(CASE WHEN OperationType = 'OpenForm' THEN 1 ELSE 0 END) AS OpenForm,
SUM(CASE WHEN OperationType = 'Exception' THEN 1 ELSE 0 END) AS Exception,
SUM(CASE WHEN OperationType = 'CustomError' THEN 1 ELSE 0 END) AS CustomError
FROM sys_LogOperation
GROUP BY YEAR(OperationTime);

//来源:C/S框架网 | www.csframework.com | QQ:23404761

3. 界面设计参考

4. 查询结果，动态创建列

C# Code:

/// <summary>
/// 动态窗体GridColumn实例
/// </summary>
/// <param name="years"></param>
private void CreateDynamicColumns(string[] years)
{
//清除所有列
gridView1.Columns.Clear();

//添加默认列OperationType,Account,左侧固定
gridView1.Columns.Add(colAccount);
gridView1.Columns.Add(colType);

colAccount.VisibleIndex = 0;
colType.VisibleIndex = 1;

int v = 2;
foreach (var year in years)
{
GridColumn col = new GridColumn();
col.Name = "col" + year;
col.Caption = year;
col.FieldName = "Y" + year;//字段名
col.Width = 100;
col.SummaryItem.SummaryType = DevExpress.Data.SummaryItemType.Sum;//数字合计
col.VisibleIndex = v;
col.Visible = true;
gridView1.Columns.Add(col);
v++;
}

}

//来源:C/S框架网 | www.csframework.com | QQ:23404761

5. 演示界面源码位置

CSFrameworkV5-ClientFoundation

\CSFrameworkV5.Report.frmDynamicReport

6. DAL层源码

C# Code:

public DataSet GetDynamicReport(string[] years)
{
//组合SQL1
StringBuilder sb = new StringBuilder();
sb.AppendLine($"SELECT Account,OperationType, ");
for (int i = 0; i <= years.Length - 1; i++)
{
string year = years[i].Trim();//必须去空格!
string item = $" SUM(CASE WHEN YEAR(OperationTime)={year} THEN 1 ELSE 0 END) AS Y{year} ";
if (i < years.Length - 1) item += ", ";
sb.AppendLine(item);
}

sb.AppendLine("FROM sys_LogOperation ");
sb.AppendLine("GROUP BY Account,OperationType; ");
string sql1 = sb.ToString();

//组合SQL2
string sql2 = "SELECT YEAR(OperationTime) AS YearItem, " +
" SUM(CASE WHEN OperationType = 'OpenForm' THEN 1 ELSE 0 END) AS OpenForm, " +
" SUM(CASE WHEN OperationType = 'Exception' THEN 1 ELSE 0 END) AS Exception, " +
" SUM(CASE WHEN OperationType = 'CustomError' THEN 1 ELSE 0 END) AS CustomError " +
"FROM sys_LogOperation " +
"GROUP BY YEAR(OperationTime); ";

DataSet ds = DatabaseProvider.SystemDatabase.GetDataSet(sql1 + sql2);
return ds;
}

//来源:C/S框架网 | www.csframework.com | QQ:23404761

扫一扫加作者微信

参考文档：

C#多线程处理多个队列的数据(交叉线程访问及Invoke方法使用)
C# Winform动态设置控件的值及反射技术应用
C# Winform系统登录(登入/登出)架构设计
C# Winform项目多语言实现(支持简/繁/英三种语言)
C#使用using语法自动关闭SQLConnection数据库连接
C# Winform 按回车键查找下一个可设置焦点的组件
C# Winform 开发平台新功能 - C/S系统开发框架旗舰版V5.0
C# Winform 拖放图片文件到界面
C# Winform中如何获取父窗体中控件的信息
推荐C# Winform+DevExpress皮肤框架|C/S框架网
【原创】C# Winform进销存系统快速开发框架
【原创】C# Winform Dev自动下拉弹框筛选数据PopupContainerEdit组件
C#使用Multipart form-data方式上传文件及提交其他数据
基于DevExpress C# Winform 财务会计凭证表格金额录入组件（支持元角分）
C# Winform软件快速开发框架|软件开发平台 - 界面多语言实现原理

旗舰版V5.1
(作者推荐)

轻量框架V2.1
(2021 release)