|
SQL语句： select a.规格,a.总重量,b.AA重量,c.非AA重量 from (select 规格,sum(库存重量)/1000 as 总重量 from sheet1 group by 规格 ) as a left outer join (select 规格,sum(库存重量)/1000 as AA重量 from sheet1 where 等级 like '%AA%' group by 规格 ) as b on a.规格= b.规格 left outer join (select 规格,sum(库存重量)/1000 as 非AA重量 from sheet1 where 等级 not like '%AA%'group by 规格) as c on a.规格=c.规格 order by a.总重量 desc; 在SQL server2000中可以通过，并得到预期的结果，在EXCEL中提示操作符丢失，那位高人指点一下？ 目的是想从A：E列中按规格汇总出 总重量，AA重量及 非AA重量 在Zhaxinbo的帮助下，将SQL2000中成功转移到EXCEL中，以下分五步，由普通查询逐步过渡到多表外联接查询 Sub DTY()
Dim CNN As New ADODB.Connection
Dim RST As New ADODB.Recordset
Dim sql As String, DBpath As String, maxrow%, t
t = Timer
DBpath = "provider=microsoft.jet.oledb.4.0;extended properties='Excel 8.0;HDR=yes';data source=" & ThisWorkbook.FullName
CNN.Open DBpath
Range("g2:w" & [g65536].End(xlUp).Row).ClearContents
'第一步：按规格汇总库存总重量
sql = "select 规格,sum(库存重量)/1000 from [sheet1$A:E] group by 规格 order by sum(库存重量)/1000 desc"
RST.Open sql, CNN, adOpenKeyset, adLockPessimistic
Cells(2, "g").CopyFromRecordset RST
RST.Close '第二步：按规格汇总库存AA级重量（等级中含‘AA’）
sql = "select 规格,sum(库存重量)/1000 from [sheet1$A:E] where 等级 like '%AA%' group by 规格 order by sum(库存重量)/1000 desc"
RST.Open sql, CNN, adOpenKeyset, adLockPessimistic
Cells(2, "j").CopyFromRecordset RST
RST.Close '第三步：按规格汇总库存非AA级重量（等级中不含‘AA’）
sql = "select 规格,sum(库存重量)/1000 from [sheet1$A:E] where 等级 not like '%AA%' group by 规格 order by sum(库存重量)/1000 desc"
RST.Open sql, CNN, adOpenKeyset, adLockPessimistic
Cells(2, "m").CopyFromRecordset RST
RST.Close '第四步：按规格汇总库存总重量及AA重量（两表之间的外联接）
sql = "select a.规格,a.总重量,b.AA重量 from (select 规格,sum(库存重量)/1000 as 总重量 from [sheet1$A:E] group by 规格 ) as a left join (select 规格,sum(库存重量)/1000 as AA重量 from [sheet1$A:E] where 等级 like '%AA%' group by 规格 ) as b on a.规格= b.规格 order by a.总重量 desc"
RST.Open sql, CNN, adOpenKeyset, adLockPessimistic
Cells(2, "p").CopyFromRecordset RST
RST.Close '第五步：按规格汇总库存总重量、AA重量及非AA重量（多表之间的外联接）
sql = "select a.规格,a.总重量,b.AA重量,c.非AA重量 from ((select 规格,sum(库存重量)/1000 as 总重量 from [sheet1$A:E] group by 规格 ) as a left join (select 规格,sum(库存重量)/1000 as AA重量 from [sheet1$A:E] where 等级 like '%AA%' group by 规格 ) as b on a.规格= b.规格 ) left join (select 规格,sum(库存重量)/1000 as 非AA重量 from [sheet1$A:E] where 等级 not like '%AA%'group by 规格) as c on a.规格=c.规格 order by a.总重量 desc"
RST.Open sql, CNN, adOpenKeyset, adLockPessimistic
Cells(2, "t").CopyFromRecordset RST
RST.Close
CNN.Close
Set RST = Nothing
Set CNN = Nothing
MsgBox Timer - t
End Sub
ULCs0p7p.rar
(37.77 KB, 下载次数: 132)
[此贴子已经被作者于2006-7-10 12:40:29编辑过] | 
沪公网安备 31011702000001号 沪ICP备11019229号-2
发表于 2006-7-8 21:51
楼主