Stay hungry,Stay foolish!

0%

SQL拼接了两天,最终选择了LINQ,真香…

工作已经满一年了,一直在从事C#开发和写写SQL语句,以前秉承着能用SQL解决就用SQL解决的原则。直到最近搞得焦头烂额,才尝试着使用C#的LINQ来解决,结果发现真香!!!

一、前言

某一个风和日丽的下午,接到用户的一个需求:开发一个界面,可以跨数据库查询,拉出两列数据,分别是“产品型号”和它对应的“规格“,然后计算每一种产品的每一种规格的数量,再在产品型号之间来比较每种规格的最大、最小和平均数量值。

数据是这样呈现的(举个例子):

202007191244492020-7-19-12-44-50

最后要统计成这样子,然后输出到Excel中。

202007191245022020-7-19-12-45-2

本来这个用SQL也很容易解决,但是到了行转列的时候,因为在PIVOT函数那里需要用到动态列,还加上求最大、最小和平均数时也需要用到动态列,所以打算使用字符串拼接的方式实现这一功能,结果在传参的时候,需要在一个存储过程里将上面查询得出的结果作为参数放到下面的查询中去,老是成功不了,搞几个存储过程又不好维护,需求又很急,于是改用LINQ试了一试,下面是实现的代码,可直接运行。

二、测试数据

为了方便测试,我直接在代码里面New了一个DataTable,往里面填充数据,以下为代码。

1
DataTable dt = new DataTable();
2
dt.Columns.Add("Type");
3
dt.Columns.Add("Size");
4
dt.Rows.Add(new string[] { "SKI-1", "8mm" });
5
dt.Rows.Add(new string[] { "SKI-1", "8mm" });
6
dt.Rows.Add(new string[] { "SKI-1", "8mm" });
7
dt.Rows.Add(new string[] { "SKI-2", "9mm" });
8
dt.Rows.Add(new string[] { "SKI-3", "10mm" });
9
dt.Rows.Add(new string[] { "SKI-1", "8mm" });
10
dt.Rows.Add(new string[] { "SKI-6", "21mm" });
11
dt.Rows.Add(new string[] { "SKI-1", "8mm" });
12
dt.Rows.Add(new string[] { "SKI-2", "9mm" });
13
dt.Rows.Add(new string[] { "SKI-3", "10mm" });
14
dt.Rows.Add(new string[] { "SKI-1", "10mm" });
15
dt.Rows.Add(new string[] { "SKI-1", "8mm" });
16
dt.Rows.Add(new string[] { "SKI-1", "8mm" });
17
dt.Rows.Add(new string[] { "SKI-2", "9mm" });
18
dt.Rows.Add(new string[] { "SKI-3", "10mm" });
19
dt.Rows.Add(new string[] { "SKI-4", "12mm" });
20
dt.Rows.Add(new string[] { "SKI-5", "12mm" });
21
dt.Rows.Add(new string[] { "SKI-1", "12mm" });
22
dt.Rows.Add(new string[] { "SKI-5", "12mm" });
23
dt.Rows.Add(new string[] { "SKI-1", "12mm" });
24
dt.Rows.Add(new string[] { "SKI-1", "12mm" });

三、LINQ处理

1
// 根据Type和Size分组
2
var countResult = (from p in dt.AsEnumerable()
3
                group p by new { Type = p.Field<string>("Type"), Size = p.Field<string>("Size") } into g
4
                let Count = g.Count()
5
                select new
6
                {
7
                    g.Key.Type,
8
                    g.Key.Size,
9
                    Count
10
                }).ToList();
11
// 计算每个规格Size分组求最大最小和平均值
12
var calculateResult = (from p in countResult
13
                        group p by p.Size into g
14
                        select new
15
                        {
16
                            g.Key,    // Size
17
                            Max = g.Max(x => x.Count),
18
                            Min = g.Min(x => x.Count),
19
                            Avg = g.Average(x => x.Count)
20
                        }).ToList();
21
// 计算有多少种Size,用于行转列
22
var sizeList = (from p in dt.AsEnumerable()
23
                group p by p.Field<string>("Size") into g
24
                orderby g.Key ascending
25
                select g.Key).ToList();
26
DataTable resultDt = new DataTable("统计结果");
27
resultDt.Columns.Add("序号 No.");
28
resultDt.Columns.Add("产品类型");
29
// 有多少种size就添加多少列,用于展示
30
foreach (var feeder in sizeList)
31
{
32
    resultDt.Columns.Add(feeder);
33
}
34
// 根据产品类型进行分组
35
var typeData = (from p in countResult
36
                group p by p.Type into g
37
                select new
38
                {
39
                    Type = g.Key,
40
                    Items = g    // 将原数据存入Items
41
                }).ToList();
42
int num = 1; // 用来计数,也可以在Linq里面处理
43
// 行转列
44
typeData.ForEach(x =>
45
{
46
    string[] array = new string[resultDt.Columns.Count];
47
    array[0] = num.ToString();
48
    array[1] = x.Type;
49
    for(int i = 2; i < resultDt.Columns.Count; i++)   // 从第二列开始填数据
50
    {
51
        array[i] = (from p in x.Items
52
                    where p.Size == resultDt.Columns[i].ToString()
53
                    select p.Count.ToString()).SingleOrDefault();
54
    }
55
    resultDt.Rows.Add(array);
56
    num++;
57
});
58
// 填充统计项
59
string[] maxArray = new string[resultDt.Columns.Count];
60
maxArray[0] = "统计项";
61
maxArray[1] = "Max";
62
for (int i = 2; i < resultDt.Columns.Count; i++)
63
{
64
    maxArray[i] = (from p in calculateResult
65
                    where p.Key == resultDt.Columns[i].ToString()
66
                    select p.Max.ToString()).SingleOrDefault();
67
}
68
resultDt.Rows.Add(maxArray);
69
string[] minArray = new string[resultDt.Columns.Count];
70
minArray[1] = "Min";
71
for (int i = 2; i < resultDt.Columns.Count; i++)
72
{
73
    minArray[i] = (from p in calculateResult
74
                    where p.Key.ToString().ToLower() == resultDt.Columns[i].ToString()
75
                    select p.Min.ToString()).SingleOrDefault();
76
}
77
resultDt.Rows.Add(minArray);
78
string[] avgArray = new string[resultDt.Columns.Count];
79
avgArray[1] = "Avg";
80
for (int i = 2; i < resultDt.Columns.Count; i++)
81
{
82
    avgArray[i] = (from p in calculateResult
83
                    where p.Key.ToString().ToLower() == resultDt.Columns[i].ToString()
84
                    select p.Avg.ToString()).SingleOrDefault();
85
}
86
resultDt.Rows.Add(avgArray);

四、导出到Excel

最后可以借鉴我上一篇文章来实现导出:https://hjxlog.com/posts/20200717a1.html