EXCEL(VBA)~SQL 经典写法范本汇集(五)

来源:原创 点击数:8619 　 | 收藏 | 复制

时　间:2012-04-11 09:16:33
作　者:欢乐小爪 ID：20149 城市：杭州
摘　要:EXCEL(VBA)~SQL 经典写法范本汇集(五)
正　文:

龙爪(461699029) 20:11:20

sql
select 原料名称,物流号,sum(重量) as 重量Kg,sum(金额) as 金额元 from [原料入库表$A2:H18] where 原料名称='甜味素' and 日期>=#2012-03-01# and 日期<=#2012-03-09# group by 原料名称,物流号
Gavin(549199361) 20:13:16

sql

select 原料名称,物流号,sum(重量) as 重量Kg,sum(金额) as 金额元 from [原料入库表$A2:H18] where 原料名称='甜味素' and 日期 between ‘2012-03-01’ and ‘2012-03-09’ group by 原料名称,物流号

对于一个原料表名和对于一个入库表的查询重量

SQL = "select a.原料名称,sum(iif(isnull(b.重量),0,b.重量)) as 重量 from [原料表$B2:B1000] a left join [原料入库表$A2:H1000] b on a.原料名称=b.原料名称 group by a.原料名称 order by a.原料名称"

********************************

表的查询区域写法

Dim rng As Range
Set rng = [a4].Resize([a1].CurrentRegion.Rows.Count - 3,[a1].CurrentRegion.Columns.Count)
sql = "Select " & Join(brr, ",") & " from [Sheet1$" & rng.Address(0, 0) & "]"
---------------------------------------------------------------------------------------------

sub hh（）

If OptionButton1.Value = True Then :YLRKRow = yuanliaoruku.Range("A65536").End(xlUp).row
chaxunbiaoquyu = "原料入库表$A2:H" & YLRKRow
End If
If OptionButton2.Value = True Then :YLCKRow = yuanliaochuku.Range("A65536").End(xlUp).row
chaxunbiaoquyu = "原料出库表$A2:H" & YLCKRow
End If
If OptionButton3.Value = True Then : YLKCRow = yuanliaokuchu.Range("A65536").End(xlUp).row
chaxunbiaoquyu = "原料出库表$A2:H" & YLKCRow
End If
If CDylName.Text <> "所有原料" Then chaxunziju = "原料名称='" & CDylName.Text & "'"
If CDwuliuName.Text <> "所有物流号" Then chaxunziju = chaxunziju & " and 物流号='" & CDwuliuName.Text & "'"

chaxunmingcheng = "原料入出库及库存表汇总查询"
查询模块.yuanliaochaxunmokuai

------------------------------------------------------------------

Public chaxunmingcheng As String
Public chaxunziju As String
Public chaxunbiaoquyu As String

Dim CNN As New ADODB.Connection
Dim RST As New ADODB.Recordset
Dim spath, SQL, Crt As String

'需引用 microsoft activeX Data Objects 2.8 library
Private Sub Open_database()
' On Error Resume Next
CNN.Open "provider=microsoft.jet.oledb.4.0;extended properties=excel 8.0;data source=" & ThisWorkbook.FullName
End Sub
Private Sub Close_database()
' On Error Resume Next
RST.Close
CNN.Close
Set RST = Nothing
Set CNN = Nothing
End Sub
Sub exe_cnn()
' On Error Resume Next
Dim R%, F%
RST.Open SQL, CNN, adOpenKeyset, adLockOptimistic
R = 3
For F = 0 To RST.Fields.Count - 1
Sheets("Temp").Cells(R, F + 1) = RST.Fields(F).Name
Next F
Sheets("Temp").Range("A4").CopyFromRecordset RST
Sheets("Temp").Range("A25").CopyFromRecordset CNN.Execute(SQL)
Call Close_database
End Sub

Sub yuanliaochaxunmokuai()
Sheets("Temp").Select
Sheets("Temp").Rows.Clear
Dim tt As String
Select Case chaxunmingcheng
Case "原料入出库及库存表汇总查询"
Call Open_database
SQL = "select 原料名称,物流号,sum(重量) as 重量Kg,sum(金额) as 金额元 from [" & chaxunbiaoquyu & "]" & _
" where " & chaxunziju & "日期 between #" & UserF原料管理.CDqDate.Value & "# and #" & UserF原料管理.CDzDate.Value & "# " & _
"group by 原料名称,物流号" 'ORDER BY子句将按升序排列

Case "原料入出库及库存表以原料名称排序明细查询"
Call Open_database
SQL = "select * from [" & chaxunbiaoquyu & "]" & _
" where " & chaxunziju & "日期 between #" & UserF原料管理.CDqDate.Value & "# and #" & UserF原料管理.CDzDate.Value & "# " & _
"ORDER BY 原料名称"

Case "原料入出库及库存表以物流号排序明细查询"
Call Open_database
SQL = "select * from [" & chaxunbiaoquyu & "]" & _
" where " & chaxunziju & "日期 between #" & UserF原料管理.CDqDate.Value & "# and #" & UserF原料管理.CDzDate.Value & "# " & _
"ORDER BY 物流号"
Case Else
Exit Sub
End Select
Call exe_cnn
Sheets("Temp").Range("A2").Value = "查询日期为" & Now() & "----" & Left(chaxunbiaoquyu, 5) & Right(chaxunmingcheng, 4)
Sheets("Temp").Range("A1").Value = "查询条件：日期为" & UserF原料管理.CDqDate.Value & "～" & UserF原料管理.CDzDate.Value & " " & chaxunziju

End Sub