SQL转置练习，转置九式。【开贴】

makeriches

下来学习！

7786910

学习一下，楼主辛苦

qiexy

SQL学习ing。。。。。。

胖莉

MARK~学习一哈。

AVEL

看了吴姐跟群子两位女侠的回帖，都很强大。

香川群子

wcymiss 发表于 2012-2-3 19:03
神奇的吴版，神奇的转置九式
以下sql语句均需把连接文本里的"HDR=YES"改为"HDR ...

经确认，转置6代码写法有问题，少了双引号。
应该为：

TRANSFORM FIRST(结果) SELECT 列字段2 FROM
    (SELECT 列字段1,结果,列字段1 AS 列字段2,行字段 FROM
        (SELECT a.f1 AS 列字段1,a.f2 AS 结果,COUNT(b.f1) AS 行字段 FROM [数据$]a,[数据$]b
        WHERE a.f1=b.f1 AND a.f2>=b.f2 GROUP BY a.f1,a.f2) UNION
    SELECT f1&f1,NULL,""总共(""&COUNT(f2)&""人)"",1 FROM [数据$]
    GROUP BY f1)
GROUP BY 列字段1,列字段2 PIVOT 行字段

香川群子

全部代码为：

1. Sub sqltest()
   
2.    
   
3.     Set Cnn = CreateObject("ADODB.Connection")
   
4.     Cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;Hdr=No;';Data Source=" & ThisWorkbook.FullName
   
5.    
   
6.     sql_1 = "TRANSFORM FIRST(结果) SELECT NULL FROM (SELECT f1 AS 结果,f1&f2 AS 字段 FROM [数据$] UNION SELECT f2,f1&f2&f1 FROM [数据$]) GROUP BY 1  PIVOT 字段"
   
7.     Sheet2.Range("A8").CopyFromRecordset Cnn.Execute(sql_1)
   
8.    
   
9.     sql_2 = "TRANSFORM FIRST(结果) SELECT NULL FROM (SELECT 结果,INT((COUNT(辅助2)-1)/6) AS 列字段,(COUNT(辅助2)-1) MOD 6 AS 行字段 FROM (SELECT f1 AS 结果,f1&f2 AS 辅助1 FROM [数据$] UNION SELECT f2,f1&f2&f1 FROM [数据$])a,(SELECT f1&f2 AS 辅助2 FROM [数据$] UNION SELECT f1&f2&f1 FROM [数据$])b WHERE 辅助1>=辅助2 GROUP BY 结果,辅助1) GROUP BY 列字段 PIVOT 行字段"
   
10.     Sheet3.Range("L1").CopyFromRecordset Cnn.Execute(sql_2)
    
11.    
    
12.     sql_3 = "TRANSFORM FIRST(结果) SELECT NULL FROM (SELECT 结果,列字段1,COUNT(辅助2) AS 行字段 FROM (SELECT f1 AS 结果,f1&f2 AS 辅助1,f1 AS 列字段1 FROM [数据$] UNION SELECT f2,f1&f2&f1,f1 FROM [数据$])a,(SELECT f1&f2 AS 辅助2,f1 AS 列字段2 FROM [数据$] UNION SELECT f1&f2&f1,f1 FROM [数据$])b WHERE 辅助1>=辅助2 AND 列字段1=列字段2 GROUP BY 结果,列字段1,辅助1) GROUP BY 列字段1 PIVOT 行字段"
    
13.     Sheet4.Range("A8").CopyFromRecordset Cnn.Execute(sql_3)
    
14.    
    
15.     sql_4 = "TRANSFORM FIRST(结果) SELECT 列字段 FROM (SELECT a.f1 AS 列字段,a.f2 AS 结果,COUNT(b.f1) AS 行字段 FROM [数据$]a,[数据$]b WHERE a.f1=b.f1 AND a.f2>=b.f2 GROUP BY a.f1,a.f2) GROUP BY 列字段 PIVOT 行字段"
    
16.     Sheet5.Range("L1").CopyFromRecordset Cnn.Execute(sql_4)
    
17.    
    
18.     sql_5 = "TRANSFORM FIRST(结果) SELECT 列字段2 FROM (SELECT 列字段1,结果,列字段1 AS 列字段2,行字段 FROM (SELECT a.f1 AS 列字段1,a.f2 AS 结果,COUNT(b.f1) AS 行字段 FROM [数据$]a,[数据$]b WHERE a.f1=b.f1 AND a.f2>=b.f2 GROUP BY a.f1,a.f2) UNION SELECT f1&f1,NULL,NULL,1 FROM [数据$]) GROUP BY 列字段1,列字段2 PIVOT 行字段"
    
19.     Sheet6.Range("L1").CopyFromRecordset Cnn.Execute(sql_5)
    
20.    
    
21.     sql_6 = "TRANSFORM FIRST(结果) SELECT 列字段2 FROM (SELECT 列字段1,结果,列字段1 AS 列字段2,行字段 FROM (SELECT a.f1 AS 列字段1,a.f2 AS 结果,COUNT(b.f1) AS 行字段 FROM [数据$]a,[数据$]b WHERE a.f1=b.f1 AND a.f2>=b.f2 GROUP BY a.f1,a.f2) UNION SELECT f1&f1,NULL,""总共("" & Count(f2) & ""人)"",1 FROM [数据$] GROUP BY f1) GROUP BY 列字段1,列字段2 PIVOT 行字段"
    
22.     Sheet7.Range("L1").CopyFromRecordset Cnn.Execute(sql_6)
    
23.    
    
24.     sql_7 = "TRANSFORM FIRST(结果) SELECT NULL FROM (SELECT a.f1 AS 列字段1,a.f2 AS 结果,(COUNT(b.f1)-1) MOD 3 +2 AS 行字段,INT((COUNT(b.f1)+2)/3) AS 列字段2 FROM [数据$]a,[数据$]b WHERE a.f1&a.f2>=b.f1&b.f2 AND a.f1=b.f1 GROUP BY a.f1,a.f2 UNION SELECT f1,f1,1,1 FROM [数据$]) GROUP BY 列字段1,列字段2  PIVOT 行字段"
    
25.     Sheet8.Range("F1").CopyFromRecordset Cnn.Execute(sql_7)
    
26.    
    
27.     sql_8 = "TRANSFORM FIRST(结果) SELECT NULL FROM (SELECT a.f1 AS 列字段1,a.f2 AS 结果,(COUNT(b.f1)-1) MOD 3 +2 AS 行字段,INT((COUNT(b.f1)-1)/3) AS 列字段2 FROM [数据$]a,[数据$]b WHERE a.f1&a.f2>=b.f1&b.f2 and a.f1=b.f1 GROUP BY a.f1,a.f2 UNION SELECT a.f1,a.f1,1,INT((COUNT(b.f1)-1)/3) FROM [数据$]a,[数据$]b WHERE a.f1&a.f2>=b.f1&b.f2 AND a.f1=b.f1 GROUP BY a.f1,a.f2) GROUP BY 列字段1,列字段2 PIVOT 行字段"
    
28.     Sheet9.Range("F1").CopyFromRecordset Cnn.Execute(sql_8)
    
29.    
    
30.     sql_9 = "TRANSFORM FIRST(结果) SELECT NULL FROM (SELECT a.f1 AS 列字段1,a.f2 AS 结果,(COUNT(b.f1)-1) MOD 3 +2 AS 行字段,INT((COUNT(b.f1)+2)/3) AS 列字段2 FROM [数据$]a,[数据$]b WHERE a.f1&a.f2>=b.f1&b.f2 AND a.f1=b.f1 GROUP BY a.f1,a.f2 UNION SELECT f1,f1,1,1 FROM [数据$] UNION SELECT f1,'总共('&COUNT(f1)&'人)',1,2 FROM [数据$] GROUP BY f1) GROUP BY 列字段1,列字段2  PIVOT 行字段"
    
31.     Sheet10.Range("F1").CopyFromRecordset Cnn.Execute(sql_9)
    
32.    
    
33.    
    
34.     Cnn.Close
    
35.     Set Cnn = Nothing
    
36. End Sub
    
37. 
    

复制代码

香川群子

sql语句太长了。

sintering

看不懂，学习中

manchen

也过来看看，感觉很难的，留位置等有时间再研究