先将值传递给DATAGRID Private Sub Command1_Click() Set con_public = New ADODB.Connection Set rst_public = New ADODB.Recordset Set rst_thpublic = New ADODB.Recordset con_public.Open str_mdbpath rst_public.CursorLocation = adUseClient rst_thpublic.CursorLocation = adUseClient '对机型为空且供应商都为空时的情况进行查询 If Combox_model.Text = "" And Combox_gysmc.Text = "" Then rst_public.Open "select * from tab_storage where cgrkrq between '" & Format(DTPicker_ksrq.Value, "yyyy/MM/dd") & "' and '" & Format(DTPicker_jsrq.Value, "yyyy/MM/dd") & "'", con_public, adOpenKeyset, adLockOptimistic rst_thpublic.Open "select * from tab_storage where cgrkrq between '" & Format(DTPicker_ksrq.Value, "yyyy/MM/dd") & "' and '" & Format(DTPicker_jsrq.Value, "yyyy/MM/dd") & "' and cgthrq<>''", con_public, adOpenKeyset, adLockOptimistic DataGrid1.Caption = "共办理采购入库" & rst_public.RecordCount & "台" DataGrid2.Caption = "共办理采购退货" & rst_thpublic.RecordCount & "台" Set DataGrid1.DataSource = rst_public DataGrid1.Columns(0).Caption = "供应商名称" DataGrid1.Columns(1).Caption = "机型" DataGrid1.Columns(2).Caption = "IMEI" DataGrid1.Columns(3).Caption = "采购入库日期" DataGrid1.Columns(4).Caption = "采购退货日期" Set DataGrid2.DataSource = rst_thpublic DataGrid2.Columns(0).Caption = "供应商名称" DataGrid2.Columns(1).Caption = "机型" DataGrid2.Columns(2).Caption = "IMEI" DataGrid2.Columns(3).Caption = "采购入库日期" DataGrid2.Columns(4).Caption = "采购退货日期" Exit Sub End If end sub '再导出EXCEL打印 Private Sub Command2_Click() '导出入库数据EXCEL的数据导出 On Error Resume Next: Dim i As Integer, j As Integer Dim xlapp As excel.Application Dim xlbook As excel.Workbook Dim xlsheet As excel.Worksheet Set xlapp = CreateObject("excel.application") xlapp.Visible = True Set xlbook = xlapp.Workbooks.Add Set xlsheet = xlbook.Worksheets(1) '循环添加相关的标题 For j = 0 To 4 xlsheet.Cells(1, j + 1) = DataGrid1.Columns(j).Caption Next j '循环写入相关数据 For i = 1 To rst_public.RecordCount For j = 0 To DataGrid1.Columns.Count xlsheet.Cells(i + 1, j + 1) = DataGrid1.Columns(j) Next j rst_public.MoveNext Next i rst_public.MoveFirst End Sub
select 金額 as 1月,0.00 as 2月,0.00 as 3月.....into #1 from 表 where 甲='1月' union select 0,金額,0,00....where 甲='2月' . . . . select sum(1月) as 1月,sum(2月) as 2月,.....from #1
--竖表变行表 --创建表 create table A( 工号 varchar(20), 日期 smalldatetime, 时间 varchar(20) ) --插入测试数据 insert into A select '01', '2004-10-01', '07:50' union select '01', '2004-10-01', '11:35' union select '01', '2004-10-01', '14:20' union select '01', '2004-10-02', '08:01' union select '01', '2004-10-02', '14:30' union select '02', '2004-10-01', '07:55' union select '02', '2004-10-02', '07:58' union select '03', '2004-10-01', '07:56' --创建存储过程 create proc p_t as declare @id varchar(20),@date smalldatetime,@time varchar(50) select * into #a from A order by 工号,日期,时间 update #a set 时间=case when (工号=@id) and (日期=@date) then @time else 时间 end, @time=case when (工号=@id) and (日期=@date) then @time+','+时间 else 时间 end, @id=工号,@date=日期 select 工号,日期,max(时间) as 时间 from #a group by 工号,日期 order by 工号 --执行 exec p_t --测试结果 工号 日期 时间 --------------------------------------------- 01 2004-10-01 00:00:00 07:50,11:35,14:20 01 2004-10-02 00:00:00 08:01,14:30 02 2004-10-01 00:00:00 07:55 02 2004-10-02 00:00:00 07:58 03 2004-10-01 00:00:00 07:56 (所影响的行数为 5 行)select [Empid] from [Employee] 得出结果: 张三 李四 王五 ... ...想要将它合并成这种结果: 张三,李四,王五...,...declare @s varchar(8000) set @s='' select @s=@s+','+[Empid] from [Employee] print stuff(@s,1,1,'') declare @sql varchar(2000) set @sql='' select @sql=@sql+[Empid]+',' from [Employee] set @sql=left(@sql,len(@sql)-1) print @sql
这是一段在sql server查询分析器中执行的代码 它首先建立一张表A,然后再按它指定的条件进行输出(就是那个存储过程的功能) 你要是只想要取一个字段的值,可以直接用的Recordset对象,把字段值读出来 如:设已存在数据库test,里面有表test1(name,address,phone三个字段) dim rs as recordset,cn as new connection '其中lic240是SQL服务器的名称。 cn.open "driver={sql server};server=lic240;uid=sa;pwd=;database=test" set rs=cn.execute(select * from test1) '下面这段代码可以把数据中的每条记录中的各个字段值 do while not rs.eof rs(0) 'name字段的值 rs(1) 'address 字段的值 rs(2) 'phone 字段的值 rs.movenext loop
Private Sub Command1_Click()
Set con_public = New ADODB.Connection
Set rst_public = New ADODB.Recordset
Set rst_thpublic = New ADODB.Recordset
con_public.Open str_mdbpath
rst_public.CursorLocation = adUseClient
rst_thpublic.CursorLocation = adUseClient
'对机型为空且供应商都为空时的情况进行查询
If Combox_model.Text = "" And Combox_gysmc.Text = "" Then
rst_public.Open "select * from tab_storage where cgrkrq between '" & Format(DTPicker_ksrq.Value, "yyyy/MM/dd") & "' and '" & Format(DTPicker_jsrq.Value, "yyyy/MM/dd") & "'", con_public, adOpenKeyset, adLockOptimistic
rst_thpublic.Open "select * from tab_storage where cgrkrq between '" & Format(DTPicker_ksrq.Value, "yyyy/MM/dd") & "' and '" & Format(DTPicker_jsrq.Value, "yyyy/MM/dd") & "' and cgthrq<>''", con_public, adOpenKeyset, adLockOptimistic
DataGrid1.Caption = "共办理采购入库" & rst_public.RecordCount & "台"
DataGrid2.Caption = "共办理采购退货" & rst_thpublic.RecordCount & "台"
Set DataGrid1.DataSource = rst_public
DataGrid1.Columns(0).Caption = "供应商名称"
DataGrid1.Columns(1).Caption = "机型"
DataGrid1.Columns(2).Caption = "IMEI"
DataGrid1.Columns(3).Caption = "采购入库日期"
DataGrid1.Columns(4).Caption = "采购退货日期"
Set DataGrid2.DataSource = rst_thpublic
DataGrid2.Columns(0).Caption = "供应商名称"
DataGrid2.Columns(1).Caption = "机型"
DataGrid2.Columns(2).Caption = "IMEI"
DataGrid2.Columns(3).Caption = "采购入库日期"
DataGrid2.Columns(4).Caption = "采购退货日期"
Exit Sub
End If
end sub
'再导出EXCEL打印
Private Sub Command2_Click()
'导出入库数据EXCEL的数据导出
On Error Resume Next:
Dim i As Integer, j As Integer
Dim xlapp As excel.Application
Dim xlbook As excel.Workbook
Dim xlsheet As excel.Worksheet
Set xlapp = CreateObject("excel.application")
xlapp.Visible = True
Set xlbook = xlapp.Workbooks.Add
Set xlsheet = xlbook.Worksheets(1)
'循环添加相关的标题
For j = 0 To 4
xlsheet.Cells(1, j + 1) = DataGrid1.Columns(j).Caption
Next j
'循环写入相关数据
For i = 1 To rst_public.RecordCount
For j = 0 To DataGrid1.Columns.Count
xlsheet.Cells(i + 1, j + 1) = DataGrid1.Columns(j)
Next j
rst_public.MoveNext
Next i
rst_public.MoveFirst
End Sub
我的意思是
甲 金额
1月 25
2月 20
3月 40
到datagrid显示为
甲 1月 2月 3月 4月。
25 20 40
4月没钱的就空着。请各位老大帮忙哈!
print rs("甲"),
rs.MoveNext
loop
rs.MoveFirst
do while not rs.eof
print rs("金额"),
rs.MoveNext
loop
union
select 0,金額,0,00....where 甲='2月'
.
.
.
.
select sum(1月) as 1月,sum(2月) as 2月,.....from #1
--创建表
create table A(
工号 varchar(20),
日期 smalldatetime,
时间 varchar(20)
)
--插入测试数据
insert into A select '01', '2004-10-01', '07:50'
union select '01', '2004-10-01', '11:35'
union select '01', '2004-10-01', '14:20'
union select '01', '2004-10-02', '08:01'
union select '01', '2004-10-02', '14:30'
union select '02', '2004-10-01', '07:55'
union select '02', '2004-10-02', '07:58'
union select '03', '2004-10-01', '07:56'
--创建存储过程
create proc p_t
as
declare @id varchar(20),@date smalldatetime,@time varchar(50)
select * into #a from A order by 工号,日期,时间
update #a set 时间=case when (工号=@id) and (日期=@date) then @time else 时间 end,
@time=case when (工号=@id) and (日期=@date) then @time+','+时间 else 时间 end,
@id=工号,@date=日期
select 工号,日期,max(时间) as 时间 from #a group by 工号,日期 order by 工号
--执行
exec p_t
--测试结果
工号 日期 时间
---------------------------------------------
01 2004-10-01 00:00:00 07:50,11:35,14:20
01 2004-10-02 00:00:00 08:01,14:30
02 2004-10-01 00:00:00 07:55
02 2004-10-02 00:00:00 07:58
03 2004-10-01 00:00:00 07:56
(所影响的行数为 5 行)select [Empid] from [Employee]
得出结果:
张三
李四
王五
...
...想要将它合并成这种结果:
张三,李四,王五...,...declare @s varchar(8000)
set @s=''
select @s=@s+','+[Empid] from [Employee]
print stuff(@s,1,1,'')
declare @sql varchar(2000)
set @sql=''
select @sql=@sql+[Empid]+',' from [Employee]
set @sql=left(@sql,len(@sql)-1)
print @sql
它首先建立一张表A,然后再按它指定的条件进行输出(就是那个存储过程的功能)
你要是只想要取一个字段的值,可以直接用的Recordset对象,把字段值读出来
如:设已存在数据库test,里面有表test1(name,address,phone三个字段)
dim rs as recordset,cn as new connection
'其中lic240是SQL服务器的名称。
cn.open "driver={sql server};server=lic240;uid=sa;pwd=;database=test"
set rs=cn.execute(select * from test1)
'下面这段代码可以把数据中的每条记录中的各个字段值
do while not rs.eof
rs(0) 'name字段的值
rs(1) 'address 字段的值
rs(2) 'phone 字段的值
rs.movenext
loop
谢谢