“排队不长，只是很粗”——浅谈化宽为长

roych

记得初中时住校吃饭堂，那时候每周伙食费是20元。早上1元，午餐晚餐各1.5元（周末则回家）。

早餐一般是一碗很稀的粥，上面漂浮着一块或者两块油汪汪的肥肉，外加一个包子。星期三则是猪骨头汤米粉，——当然是没有包子了，——所以，早操一结束，大家就集体逃亡一般奔向饭堂。最开始冲过去的在每个窗口前排成两列，排到十几个之后，后面的大都是女生。其它男同学则会绕到侧面，伺机斜插进去。

曾经有个走读的同学问我，住校时排队很长吗？我认真地想了想，“排队不长，只是很粗”。她笑了，“你很幽默。”然后我也笑了，虽然我并不是一个幽默的人。直到后来学生会组织了一些学生干部专门去维持秩序，才开始“化宽为长”。

正如太宽的队伍常常有不遵守秩序的嫌疑，太宽的数据表也常常有不遵守第二范式的嫌疑。而违反它，在数据库中往往会引发很多问题，表维护不方便，难以统计分析，查询不好处理等等……所以“化宽为长”就变得很重要了。

化宽为长的前提是要求“宽”的那部分拥有共同的属性，就像宽的那部分队伍一样（都是男同学，都在队伍侧面），例如，都是数值型，都在最右边的字段等等。如果没有这些，当然谈不上化宽为长了。先看看效果吧：

代码如下，具体见示例文件。喜欢的话，就回个帖吧。

1. Function getSQL(ByVal strTableName As String, ByVal strEndFieldName As String) As String
   
2.     Dim rst As New ADODB.Recordset
   
3.     Dim i As Long
   
4.     Dim lngPosition As Long
   
5.     Dim strSQL As String, strSQL2 As String
   
6.     Dim dic As New Dictionary
   
7.     Dim lngEnd As Long
   
8.    
   
9.     rst.Open strTableName, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
   
10. '查找字段分割点的位置
    
11.     For i = 0 To rst.Fields.Count - 1
    
12.         If rst.Fields(i).Name = strEndFieldName Then
    
13.             lngEnd = i
    
14.             Exit For
    
15.         End If
    
16.     Next
    
17. '切割字段
    
18.     For i = 0 To rst.Fields.Count - 1
    
19. '在分割点之前，直接连接字符串
    
20.         If i <= lngEnd Then
    
21.             strSQL = strSQL & rst.Fields(i).Name & ","
    
22.         Else
    
23. '在分割点之后，写入字典，用于确定变量名和变量值。
    
24.             dic.Add i, rst.Fields(i).Name
    
25.             
    
26.         End If
    
27.     Next
    
28. '关闭记录集
    
29.     rst.Close
    
30.    
    
31.     '准备语句
    
32.     For i = 0 To dic.Count - 1
    
33.         strSQL2 = strSQL2 & "select " & strSQL & """" & _
    
34.                 dic.Items(i) & """ as 变量名称,[" & _
    
35.                 dic.Items(i) & "] as 变量值 from " & strTableName & " union all "
    
36.     Next
    
37.    
    
38.     getSQL = Left(strSQL2, Len(strSQL2) - 11)
    
39.                
    
40. End Function

复制代码

tmtony

你总是离不开吃

zpy2

这是传说中的二维转一维吗，赞一个

zpy2

1. Option Compare Database
   
2. Option Explicit
   
3. 
   
4. Function getSQL2(ByVal strTableName As String, ByVal strEndFieldName As String) As String
   
5.   On Error Resume Next
   
6.   Dim rst As DAO.Recordset, rst2
   
7.   Dim i As Long
   
8.   Dim j As Long
   
9.   Dim strSQL As String, strSQL2 As String
   
10.   Dim lngEnd As Long
    
11.   
    
12.   Set rst = CurrentDb.OpenRecordset("select * from " & strTableName)
    
13.   '查找字段分割点的位置
    
14.   For i = 0 To rst.Fields.Count - 1
    
15.     If rst.Fields(i).Name = strEndFieldName Then
    
16.       lngEnd = i
    
17.       Exit For
    
18.     End If
    
19.   Next
    
20.   
    
21.   '这里省去了 判断 表否存在
    
22.   strSQL = "DROP TABLE tempTable"
    
23.   CurrentDb.Execute strSQL
    
24.   strSQL = "CREATE TABLE tempTable ("
    
25.   For i = 0 To rst.Fields.Count - 1
    
26.     '在分割点之前，直接连接字符串
    
27.     If i <= lngEnd Then
    
28.       strSQL = strSQL & rst.Fields(i).Name & " TEXT,"
    
29.     Else
    
30.       Exit For
    
31.     End If
    
32.   Next
    
33.   strSQL = strSQL & "变量值 DOUBLE)"
    
34.   CurrentDb.Execute strSQL
    
35.   Rem 这段比union方法看这不同
    
36.   strSQL = "SELECT * FROM tempTable"
    
37.   Set rst2 = CurrentDb.OpenRecordset(strSQL)
    
38.   Do While Not rst.EOF
    
39.    
    
40.     For i = lngEnd + 1 To rst.Fields.Count - 1
    
41.       '在分割点之前，直接连接字符串
    
42.       rst2.AddNew
    
43.       For j = 0 To lngEnd
    
44.         rst2.Fields(j) = rst.Fields(j)
    
45.       Next
    
46.       rst2.Fields(lngEnd + 1) = rst.Fields(i)
    
47.       rst2.Update
    
48.       
    
49.     Next i
    
50.     rst.MoveNext
    
51.   Loop
    
52.   rst2.Close
    
53.   rst.Close
    
54. End Function
    
55. 
    
56. Sub runOnce()
    
57.   Dim x
    
58.   x = getSQL2("提成等级表", "百分点")
    
59. End Sub
    
60. 
    
61. 
    
62. 
    

复制代码

用临时表作了下，好像代码多了点。

红尘如烟

肉一，你这是降维打击么？

roych

zpy2 发表于 2017-3-12 10:02
用临时表作了下，好像代码多了点。

创建临时表有2个问题，
1、处理字段类型。
这里是double，换了别的，可能会是single或者long等等。考虑到通用性，所以我没有使用这个方式。
2、插入数据的效率问题。
创建临时表一般需要嵌套循环插入，当数据量较大时可能会有些许延时。至于修改已存在的查询语句，会否存在这个问题，暂时未测试。不过，窃以为执行选择查询会比追加查询快一些。

roych

红尘如烟 发表于 2017-3-12 10:47
肉一，你这是降维打击么？

当时看到那个附件，想死的心都有了：
http://www.office-cn.net/thread-122659-1-1.html

yanwei82123300

我以为是腿粗呢，roych 老师为什么早餐粥上漂肥肉呀？啥饭？

Henry D. Sy

生活真美好，一天20元，早餐还有肉吃。
我上学时一个月才15元的生活费、

Henry D. Sy

excel里有个逆透视