[讨论][分享]SQL接龙(figfig,烟雨,ldy,sgrshh29等完成)

office2008

请 figfig大师和其他高手再看看70楼的问题如何解决

谢谢!

sgrshh29

QUOTE:

以下是引用figfig在2008-9-8 21:27:00的发言：

如果数据断月（某个月没有数据）？，已解决

如果跨年度？已解决

新代码可查询不同月份和年度

Sub fig8()
Set x = CreateObject("adodb.connection")
x.Open "provider=microsoft.jet.oledb.4.0;extended properties=excel 8.0;data source=" & ThisWorkbook.FullName

kk = Year(Application.Max(Sheet1.[a2:a25])) & Right(100 + Month(Application.Max(Sheet1.[a2:a25])), 2)
ll = Year(Application.Min(Sheet1.[a2:a25])) & Right(100 + Month(Application.Min(Sheet1.[a2:a25])), 2)

 For mm = ll To kk

sql5 = "select '" & mm & "' as mm,商品编号 as ss,sum(进货数量)-sum(销售数量) as tt  from [商品进出库期初期末$a1:d25]  where  year(日期)&right(100+month(日期),2)<=" & mm & "  group by 商品编号  "
sql6 = sql5 & " union all " & sql6
If Right(mm, 2) = 12 And Left(ll, 4) <> Left(kk, 4) Then
mm = Left(ll, 4) + 1 & "00"
End If

Next
sql6 = Left(sql6, Len(sql6) - 10)

sql7 = " select year(日期)&right(100+month(日期),2) as mm,商品编号 as ss, sum(进货数量) as vv,sum(销售数量) as ww from [商品进出库期初期末$a1:d25] group by year(日期)&right(100+month(日期),2), 商品编号"
sql8 = " select aa.mm,aa.ss,aa.tt+iif(isnull(bb.ww),0,bb.ww)-iif(isnull(bb.vv),0,bb.vv),bb.vv,bb.ww ,aa.tt from (" & sql6 & ") aa left join (" & sql7 & ") bb on aa.mm&aa.ss=bb.mm&bb.ss order by aa.mm,aa.ss"

Set y = x.Execute(sql8)
Sheet1.[f15:z500].ClearContents
Sheet1.[f15].CopyFromRecordset y
End Sub

figfig [em27]。如果时间段很长，这个查询也太复杂了一点,也许加一个自定义函数要简单许多。看来excel不适宜做这种复杂的sql查询，如果在access中，这种查询是极简单的。

[此贴子已经被作者于2008-9-10 9:26:51编辑过]

sgrshh29

QUOTE:

以下是引用office2008在2008-9-10 0:50:00的发言：

请 figfig大师和其他高手再看看70楼的问题如何解决

谢谢!

可能要借用一个自定义函数，单用查询不容易解决。

kzb

学习，学习，再学习

lanyuu

70楼的原来有人出过这个问题，可以用字典来解决，要是单纯用sql，就像楼上说的要用自定义函数，不知道excel支持吗，看网上有做的

gly1126

回92楼之跨年跨月查询

sheets("商品进出库期初期末").range("a1:d25").name="dataarea"
sqltext="select * from (select 年,月,商品编号,sum(s结存数量+s销售数量-s进货数量) as 上月结存数,sum(s进货数量) as 月进货数量,sum(s销售数量) as 月销售数量,sum(s结存数量) as 本月结存数  from (select year(日期) as 年,format(month(日期),'00') AS 月,商品编号,sum(进货数量) as s进货数量,sum(销售数量) as s销售数量,0 as s结存数量 from dataarea group by year(日期),month(日期),商品编号 union all select 年,月,商品编号,0,0,sum(进货数量)-sum(销售数量) from dataarea,(select distinct year(日期) as 年 from dataarea) as kjnd,(select distinct format(month(日期),'00') as 月 from dataarea) as kjqj where year(日期)&format(month(日期),'00')<=年&月 group by 年,月,商品编号) group by 年,月,商品编号) where 月进货数量<>0 or 月销售数量<>0"

sgrshh29

QUOTE:

以下是引用gly1126在2008-9-11 14:48:00的发言：

回92楼之跨年跨月查询

sheets("商品进出库期初期末").range("a1:d25").name="dataarea"
sqltext="select * from (select 年,月,商品编号,sum(s结存数量+s销售数量-s进货数量) as 上月结存数,sum(s进货数量) as 月进货数量,sum(s销售数量) as 月销售数量,sum(s结存数量) as 本月结存数  from (select year(日期) as 年,format(month(日期),'00') AS 月,商品编号,sum(进货数量) as s进货数量,sum(销售数量) as s销售数量,0 as s结存数量 from dataarea group by year(日期),month(日期),商品编号 union all select 年,月,商品编号,0,0,sum(进货数量)-sum(销售数量) from dataarea,(select distinct year(日期) as 年 from dataarea) as kjnd,(select distinct format(month(日期),'00') as 月 from dataarea) as kjqj where year(日期)&format(month(日期),'00')<=年&月 group by 年,月,商品编号) group by 年,月,商品编号) where 月进货数量<>0 or 月销售数量<>0"

这个视图的构思太巧妙了，佩服！！！

[em23][em23][em23]

figfig

QUOTE:

70 楼问题已解决,代码如下,增加避免重复数值功能

Sub yy2()
Dim arr()
 With Worksheets("特殊合并")
   Set CNN = CreateObject("adodb.connection")
    CNN.Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;hdr=yes;';Data Source=" & ThisWorkbook.FullName
     .Range("g2:n10").ClearContents
     r = .Range("a65536").End(xlUp).Row
 Sheets("特殊合并").[a1:e7].Name = "tbl"

sql1 = "select distinct ip1,ip2,name,pwd,(select top 1 kkk from (select distinct * from tbl)  where ip1&ip2&name&pwd=a.ip1&a.ip2&a.name&a.pwd)"
j = 1
For i = 1 To 13

sql2 = " &(select top 1 iif(isnull(kkk),'',','&kkk) from (select distinct * from tbl) where ip1&ip2&name&pwd=a.ip1&a.ip2&a.name&a.pwd and kkk not in (select top " & i & " kkk from (select distinct * from tbl) where  ip1&ip2&name&pwd=a.ip1&a.ip2&a.name&a.pwd   )) "
ReDim Preserve arr(0 To j)

arr(i) = sql2
j = j + 1
Next
sql3 = Join(arr, "")

sql4 = " from (select distinct * from tbl) a"
Sql = sql1 & sql3 & sql4
'MsgBox Sql
     .Range("g2").CopyFromRecordset CNN.Execute(Sql)
  
End With
End Sub

[此贴子已经被作者于2008-9-11 23:10:12编辑过]

office2008

谢谢!figfig大师厉害,上面所有问题都解决了,呵呵.[em17][em17][em17]

office2008

还有一个问题：请FIGFIG大师和其他高手看看有没有办法,谢谢

姓名	调薪时间	调薪原因	调整后薪资	税项
王二	2005-3-1	调整	4000	税前
王二	2005-7-1	调整	5000	税前
王二	2006-3-1	调整	6000	税前
张三	2005-3-1	调整	3300	税前
张三	2005-8-1	调整	3800	税前
李四	2005-8-1	调整	2600	税前
李四	2006-3-1	调整	4000	税前

姓名	调薪时间	调整后薪资	调薪时间	调整后薪资	调薪时间	调整后薪资
王二	2005-3-1	4000	2005-7-1	5000	2006-3-1	6000
张三	2005-3-1	3300	2005-8-1	3800
李四	2005-8-1	2600	2006-3-1	4000