从长胖 了的“日”字谈起——关于化长为宽

roych

      话说，古时候有个人，给书呆子儿子请了个私塾先生。一日，先生教了个“日”字。过几天，父亲问起他学了哪个字，他说，“学了个日字。”      “怎么写啊？”

      “不记得了。”
      “那你去书房里找出来，指给我看吧。”父亲无奈。
      一番翻箱倒柜之后，他拿着一本书，指着里面的“曰”，高兴地说，“我找到了！”
      “这是日字吗？你再仔细看看？”父亲大怒。
     “嗯……几天没见，咋就长胖了呢？”书呆子嗫嚅着说。

      长胖了的“日”字，自然就不读“ri”了。同样地，“曰”字如果减肥成功，也不读“yue”了。也就是说，从本质上，它们已经是不同的汉字了。对于Access数据表，同样是这样，不管化长为宽还是化宽为长，信息看起来没变，但结构已经是两码事了。

      不少新手在刚刚开始接触数据库时，建表不符合数据库设计范式，所以常常到了设计查询的时候才发现，似乎从技术上实现某个功能很困难。——这时候一般有三种人。第一种是试图通过ADO写代码来实现，第二种是根据功能需求重新设置表字段，至于第三种……则是“玛德，劳资不学了还不行吗？”。第一种会在错误的路上越走越远，此后将麻烦不断。第二种虽然有些弯路，但推翻重来之后，应该会好过一些。去年发的“化宽为长”示例就是为了方便第二种新手推翻重来而写的模块。
      而化长为宽，并不值得推崇，所以一直没考虑写。毕竟，数据库的第二范式就是禁止使用二维表（姑且这么称呼吧）的。非要按二维表呈现数据，倒也不是不可以，例如，交叉表查询就是一个很好的选择。但如果需要在窗体或者报表上呈现时，则应该尽可能保证列字段固定。——列字段不固定时需要写一些代码了（详细请点击这里）。而更麻烦的是，当计算字段相对较多时，交叉表查询就力所不逮了。如下图：

      因为交叉表查询只能有一个值。非要呈现三个值，除了用IIf来创建自定义字段，基本上是无法用查询实现的了。IIf的写法大概像这样：IIf([承揽方]="博罗博一","检验单数", Null)，只是创建二三十个这样的字段似乎得花它十几分钟吧（如果对语句比较熟，可以用文本编辑器打开，复制粘贴，查找替换等等，相对快一些）。所以说，这时候，优先考虑的应该是数据透视表。——事实上，当你看完这个例子之后，将会对此深有体会。

      这是否就意味着Access在技术上无法实现了呢？当然不是了。只要我们能创建一个这样的二维表，用报表的分组统计，未必不能实现上面的效果。我们先来对比下结果图：

     看起来还是比较像吧？只是这切切实实花了不少工夫。本身来讲，对我这样的手残党，控件对齐，分布都是一件极其痛苦的事情，更何况还要各种复制计算字段呢。本以为可以偷下懒，结果报表里的加载事件是不能给控件来源进行赋值的。所以……
我是分割线----------------------------------------------------------------我是分割线------------------------------------------------------------------我是分割线
     报表的设计就不多说了。大家可以下载附件来查看（兴许做得比我的好呢）。这里讲讲化长为宽的实现思路。这是一个分成三步走的做法。
     首先，创建一个不含计算列的表。考虑到固定列可能有各种不同类型的字段（这里是日期，但也有可能是文本等等），所以先用生成表的方式创建一个表，以保证目标表的第一列和源表固定列的字段类型一致。接着新增一个字符串类型的字段，用于填充计算字段的名称。
      其次，根据源表，增加列字段。这里我用了distinct获取不重复值，通过记录集光标的不断移动来增加字段名。当然也可以用group by来获取。主要看大家的习惯。
     最后，填充数据。填充数据时需要考虑充分。第一层循环，计算字段有三种类型（检测单数、不合格数、不合格率）；第二层循环，源表记录集的循环；第三层循环，目标表的列字段循环（即源表的承揽方）。当清楚这些关系之后，就可以写代码了：

1. Sub getWidth(ByVal strRowField As String, ByVal strColField As String, ByVal strTableName As String)
   
2.     Dim rst1 As New ADODB.Recordset
   
3.     Dim rst2 As New ADODB.Recordset
   
4.     Dim strOtherFields() As String
   
5.     Dim lngFieldsCount As Long
   
6.     Dim i As Long, j As Long
   
7.     DoCmd.SetWarnings False
   
8.     '创建含有行标题的初始表
   
9.     DoCmd.RunSQL "select " & strRowField & " into tblWidth from " & strTableName & " where 1=0"
   
10.     '增加统计类别字段（文本）
    
11.     DoCmd.RunSQL "alter table tblWidth add sumType varchar(50)"
    
12.     '根据列标题增加字段,列字段仅用于计算
    
13.     rst1.Open "select distinct " & strColField & " from " & strTableName, _
    
14.     CurrentProject.Connection, adOpenKeyset, adLockOptimistic
    
15.    
    
16.     Do Until rst1.EOF
    
17.         DoCmd.RunSQL "alter table tblWidth add " & rst1(0) & " numeric"
    
18.         rst1.MoveNext
    
19.     Loop
    
20.     rst1.Close
    
21.    
    
22.     '确定计算字段
    
23.     rst1.Open "select * from " & strTableName, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
    
24.    '由于前面有1个日期字段和1个计算类型字段，因此可以根据减去2。得到的3个字段，即为填充计算类型的记录。
    
25.     ReDim strOtherFields(1 To rst1.Fields.Count - 2)
    
26.     j = 1
    
27.     For i = 0 To rst1.Fields.Count - 1
    
28.         If rst1.Fields(i).Name <> strRowField And rst1.Fields(i).Name <> strColField Then
    
29.             strOtherFields(j) = rst1.Fields(i).Name
    
30.             j = j + 1
    
31.             
    
32.         End If
    
33.     Next
    
34.     rst1.Close
    
35.    
    
36.     '增加数据
    
37.     For i = 1 To UBound(strOtherFields)
    
38.         rst1.Open "select * from tblWidth", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
    
39.         rst2.Open "select " & strRowField & "," & strColField & "," & strOtherFields(i) & " from " & strTableName, _
    
40.         CurrentProject.Connection, adOpenKeyset, adLockOptimistic
    
41.         Do Until rst2.EOF
    
42.             rst1.AddNew
    
43.             For j = 0 To rst1.Fields.Count - 1
    
44.                 rst1(strRowField) = rst2(strRowField)
    
45.                 rst1("sumType") = strOtherFields(i)
    
46.                 If rst1(j).Name = rst2(strColField) Then
    
47.                     rst1(j) = rst2(strOtherFields(i))
    
48.                 End If
    
49.             Next
    
50.             rst1.Update
    
51.             rst2.MoveNext
    
52.         Loop
    
53.         rst2.Close
    
54.         rst1.Close
    
55.     Next
    
56. End Sub

复制代码

      创建表和填充数据是可以拆开的。只是考虑到参数传递时比较麻烦，所以决定写在一起了。剩下的就是调用这个函数生成一个二维表，然后再通过报表进行数据呈现了。

zpy2

roych 发表于 2018-11-3 03:56
话说，古时候有个人，给书呆子儿子请了个私塾先生。一日，先生教了个“日”字。过几天，父亲问起他学 ...

不错，赞一个。
来自: 微社区

tmtony

这个示例Roych花了很多心血。这个要做的网友有福了。

roych

tmtony 发表于 2018-11-3 23:39
这个示例Roych花了很多心血。这个要做的网友有福了。

改成Excel数据透视表形式，都是体力活啊。何况我这样的手残党。

冚友

感谢大侠为我做这个示例。感激！感激！感激！