SQL问题，麻烦帮看下代码错哪了。

jackyun · 发表于 2009-4-18 11:03

原本是发在数据透视表区的，
看了看数据透视表区网友的回帖，觉的难度还是蛮大的。
虽然我不太了解数透，但感觉难度主要还是在sql语句上。
试着先用VBA把Sql用法做出来。

运行出错，请诸位帮我看下错哪了（需求见结果表红色部分）
非常感谢

Sub test_sql()
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("K2:M100").ClearContents
   r = .Range("a65536").End(xlUp).Row
   Sql = "select 日期,姓名,职位,班组,在职状态  from [数据表$] where 日期 between 2009-1-1 and 2009-1-25 "
   Sql = "select 姓名,职位,班组 from (" & Sql & ") where 在职状态<>'离职'" _
   & " group by 姓名"

   .Range("k2").CopyFromRecordset CNN.Execute(Sql)
End With
End Sub

[ 本帖最后由 jackyun 于 2009-4-18 11:27 编辑 ]

office2008 · 发表于 2009-4-18 12:42

Sub test_sql()
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("K2:M100").ClearContents
   r = .Range("a65536").End(xlUp).Row

   Sql = "select 日期,姓名,职位,班组,iif(isnull(在职状态),'未离职','离职') as 在职状态 from [数据表$]  where 日期 between #2009-1-1# and #2009-1-25# "

   Sql = " select 姓名,职位,班组 from (" & Sql & ")  where 在职状态<>'离职' group by 姓名,职位,班组 "

   .Range("k2").CopyFromRecordset CNN.Execute(Sql)

End With
End Sub

jackyun · 发表于 2009-4-18 15:19

原帖由 office2008 于 2009-4-18 12:42 发表
Sub test_sql()
With Worksheets("数据表")
Set CNN = CreateObject("adodb.connection")
CNN.Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;hdr=yes;';Data Source=" & ThisW ...

非常感谢2008兄，修改完全正确。我错的地方不止一点点，在职状态错了，日期格式错了，组定义也错了...$#!%$^$%#^&^!
学习一下。感谢一下。
另：
2008兄是根据原来的SQL语句做的修正，完全没有问题。
问题在于原来我写的那句SQL语句没包含姓名不重复，按月剔除离职人员，按月得到日期最大的职位，按月得到日期最大的班组的条件。原因很简单，不会写.（再次露出菜鸟本色...)
所以把日期改成where 日期 between #2009-1-1# and #2009-4-25#
得出的结果有重复，见附图。需求在1楼附件结果表里红色部分有标注。或见附图。

猜一下写法：

Sql = "select 日期,姓名,职位,班组,iif(isnull(在职状态),'未离职','离职') as 在职状态 from [数据表$] where 日期 between #2009-1-1# and #2009-4-25# "

Sql = " select 姓名, 职位,班组 from (" & Sql & ") where 在职状态<>'离职' or (max(日期<#2009-4-25# * isnull(职位)=false)) or (max(日期<#2009-4-25# * isnull(班组)=false)) group by 姓名,职位,班组 "

我乱写的，意思应该是这样吧，按月得到日期最大的职位和按月得到日期最大的班组，
那个姓名去重复和剔除离职人员，我连意思都猜不出了。呵呵。
见笑了。Excelhome的菜鸟大师终于出现了。

jackyun · 发表于 2009-4-18 17:26

周六周日沉得快。up 一下。

jackyun · 发表于 2009-4-18 21:09

home的人气就是旺，一转眼又沉了。

有哪位兄弟来指点一下啊，谢谢了。

jackyun · 发表于 2009-4-19 05:28

凌晨起来看一下，还没人光临，继续等

office2008 · 发表于 2009-4-19 09:31

Sub test_sql()
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("K2:M100").ClearContents
      r = .Range("a65536").End(xlUp).Row

      Sql = " select * from ( select c.姓名,iif(isnull(c.职位),d.职位,c.职位) as 职位,iif(isnull(c.职位), " _
         & " iif(isnull(c.班组),d.班组,c.班组),iif(isnull(c.班组),d.班组,c.班组)) as 班组 from (select a.姓名, " _
         & " b.职位,b.班组 from (select max(日期) as 日期,姓名 from (select 日期,姓名,iif(在职状态='离职',1,0) as 在职状态 " _
         & " from [数据表$a1:f" & r & "]) group by 姓名 having sum(在职状态)<1) a left join [数据表$a1:f" & r & "] b on a.姓名 " _
         & " = b.姓名 and a.日期=b.日期 ) c left join [数据表$a1:f" & r & "] d on c.姓名= d.姓名) where 职位<>''and 班组<>'' "

      .Range("k2").CopyFromRecordset CNN.Execute(Sql)
End With
End Sub

jackyun · 发表于 2009-4-19 14:36

原帖由 office2008 于 2009-4-19 09:31 发表
Sub test_sql()
With Worksheets("数据表")
Set CNN = CreateObject("adodb.connection")
CNN.Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;hdr=yes;';Data Sour ...

太牛了，又是2008兄出手相助，非常感谢！
这个嵌套写得非常牛X，不过无法做日期选择了，原来的代码日期 between #2009-1-1# and #2009-4-25#
中2个日期2009-1-1和 2009-4-25 原本是2个单元格的值，我可以通过这2个单元格的值来选择时间段，为了方便解答者解答，我直接把它们改成了代码里的数字，我描述不清，反而误导了解答者，这完全怪我，非常抱歉。

试改了一下，加上日期选择，如下
Sql = " select * from ( select c.姓名,iif(isnull(c.职位),d.职位,c.职位) as 职位,iif(isnull(c.职位), " _
         & " iif(isnull(c.班组),d.班组,c.班组),iif(isnull(c.班组),d.班组,c.班组)) as 班组 from (select a.姓名, " _
         & " b.职位,b.班组 from (select max(日期)>=" & [S1].Value & " and max(日期)<=" & [T1].Value & " as 日期,姓名 from (select 日期,姓名,iif(在职状态='离职',1,0) as 在职状态 " _
         & " from [数据表$a1:f" & r & "]) group by 姓名 having sum(在职状态)<1) a left join [数据表$a1:f" & r & "] b on a.姓名 " _
         & " = b.姓名 and a.日期=b.日期 ) c left join [数据表$a1:f" & r & "] d on c.姓名= d.姓名) where 职位<>''and 班组<>'' "
=============================================================================================
注：
[S1].Value =2009-1-1
[T1].Value =2009-4-25 （可以自定义，从而选择时间段）

结果不正确，估计是嵌套的 left join 没有使用前面的 select 范围。

不知道怎么改一下代码。

再次感谢2008兄，非常感谢。

office2008 · 发表于 2009-4-19 15:07

把日期放进去应该可以的。就是你那表设计成那样复杂干什么呢？

jackyun · 发表于 2009-4-19 15:45

原帖由 office2008 于 2009-4-19 15:07 发表
把日期放进去应该可以的。就是你那表设计成那样复杂干什么呢？

不好意思，总是麻烦你，2008兄。
1. 日期直接象我那样的放法肯定不对，见附图说明。

2. 这表是交老板使用的，用于计算headcount 和 work overtime, 控制成本用的，老板还要从这表里抽数据再统计再交总部他的老板。
所以从这个角度出发，设计复杂没问题，能让老板使用不复杂是关键，他只要选选日期就行了。不然老板心情不好，问题很严重...呵呵！

原来是用函数公式做的，老板只要选下日期，就可以得到他想要的时间段数据。
实际工作中表里还包括其它各类统计，都是关联在这张表上的，
随着历年来人数增多，统计项目增加，数据量越来越大，速度也越来越卡，函数公式已经不能胜任了，所以我在想办法调整，分析后发现，瓶颈就在我提问的部分，只要解决到提问的部分，总问题就解决了...

非常感谢2008兄对我的帮助。

[ 本帖最后由 jackyun 于 2009-4-19 17:26 编辑 ]

		自动登录	找回密码
密码			免费注册

[求助] SQL问题，麻烦帮看下代码错哪了。

回复 1楼 jackyun 的帖子

回复 6楼 jackyun 的帖子

回复 8楼 jackyun 的帖子