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

figfig

QUOTE:

以下是引用office2008在2008-9-2 15:34:52的发言：

移动号码2	业务1	2008-05-04	失败标记2
移动号码2	业务1	2008-05-04	失败标记1
移动号码2	业务1	2008-05-04	失败标记1
移动号码2	业务1	2008-05-05	失败标记2
移动号码2	业务1	2008-05-06	失败标记2
移动号码2	业务1	2008-05-06	失败标记2

移动号码2	业务1	2008-05-04	失败标记1
移动号码2	业务1	2008-05-04	失败标记2
移动号码2	业务1	2008-05-05	失败标记2
移动号码2	业务1	2008-05-06	失败标记2

少2条记录，不过做成这个样子，那是狂人级别了，谢谢

Sub fig()
 With Worksheets("连续3天失败标记")
   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("f2:i1000").ClearContents
       r = .Range("a65536").End(xlUp).Row
       Sql = "select a.num,a.operation,a.dt,a.errtype FROM [连续3天失败标记$a1:d50] a,[连续3天失败标记$a1:d50] b,[连续3天失败标记$a1:d50] c  where a.num=b.num and a.num=c.num and b.num=c.num and a.operation=b.operation and a.operation=c.operation and b.operation=c.operation and day(b.dt)-day(a.dt)=1 and day(c.dt)-day(b.dt)=1 and day(c.dt)-day(a.dt)=2"
     Sql = Sql & " union (select b.num,b.operation,b.dt,b.errtype FROM [连续3天失败标记$a1:d50] a,[连续3天失败标记$a1:d50] b,[连续3天失败标记$a1:d50] c  where a.num=b.num and a.num=c.num and b.num=c.num and a.operation=b.operation and a.operation=c.operation and b.operation=c.operation and day(b.dt)-day(a.dt)=1 and day(c.dt)-day(b.dt)=1 and day(c.dt)-day(a.dt)=2)"
      Sql = Sql & " union (select c.num,c.operation,c.dt,c.errtype FROM [连续3天失败标记$a1:d50] a,[连续3天失败标记$a1:d50] b,[连续3天失败标记$a1:d50] c  where a.num=b.num and a.num=c.num and b.num=c.num and a.operation=b.operation and a.operation=c.operation and b.operation=c.operation and day(b.dt)-day(a.dt)=1 and day(c.dt)-day(b.dt)=1 and day(c.dt)-day(a.dt)=2)"
     Sql = " select distinct num & operation & dt & errtype  from (" & Sql & ") where len(num)>0"
   Sql = " select num,operation,dt,errtype from [连续3天失败标记$a1:d50]  where num & operation & dt & errtype in ( " & Sql & ")"
   .Range("f2").CopyFromRecordset CNN.Execute(Sql)
End With
End Sub

office2008

虽然你以后肯定会想提高速度的,但目的是达到了,也就是说成功了,一个字"牛"

 

[em17][em17][em17]

figfig

QUOTE:

以下是引用office2008在2008-9-2 16:42:30的发言：

虽然你以后肯定会想提高速度的,但目的是达到了,也就是说成功了,一个字"牛"

 

[em17][em17][em17]

下面代码已经优化了，速度应该没问题了，呵呵

Sub fig()
 With Worksheets("连续3天失败标记")
   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("f2:i1000").ClearContents
       r = .Range("a65536").End(xlUp).Row
       Sql = "select a.num,a.operation,a.dt,a.errtype FROM [连续3天失败标记$a1:d888] a,[连续3天失败标记$a1:d888] b,[连续3天失败标记$a1:d888] c  where a.num=b.num and a.num=c.num and b.num=c.num and a.operation=b.operation and a.operation=c.operation and b.operation=c.operation and day(b.dt)-day(a.dt)=1 and day(c.dt)-day(b.dt)=1 and day(c.dt)-day(a.dt)=2"
     Sql = Sql & " union (select b.num,b.operation,b.dt,b.errtype FROM [连续3天失败标记$a1:d888] a,[连续3天失败标记$a1:d888] b,[连续3天失败标记$a1:d888] c  where a.num=b.num and a.num=c.num and b.num=c.num and a.operation=b.operation and a.operation=c.operation and b.operation=c.operation and day(b.dt)-day(a.dt)=1 and day(c.dt)-day(b.dt)=1 and day(c.dt)-day(a.dt)=2)"
      Sql = Sql & " union (select c.num,c.operation,c.dt,c.errtype FROM [连续3天失败标记$a1:d888] a,[连续3天失败标记$a1:d888] b,[连续3天失败标记$a1:d888] c  where a.num=b.num and a.num=c.num and b.num=c.num and a.operation=b.operation and a.operation=c.operation and b.operation=c.operation and day(b.dt)-day(a.dt)=1 and day(c.dt)-day(b.dt)=1 and day(c.dt)-day(a.dt)=2)"
     Sql = " select distinct num ,operation , dt , errtype  from (" & Sql & ") where len(num)>0"
   Sql = " select aa.num,aa.operation,aa.dt,aa.errtype from [连续3天失败标记$a1:d888] aa,(" & Sql & ") bb  where aa.num=bb.num and aa.operation=bb.operation and aa.dt=bb.dt and aa.errtype=bb.errtype"
   .Range("f2").CopyFromRecordset CNN.Execute(Sql)
End With
End Sub

figfig

刚才最后一句太耗时间，所以范围取小一点到d50，现在应该没问题了，又改到d888了

office2008

刚才最后一句太耗时间，所以范围取小一点到d50，现在应该没问题了，又改到d888了

范围为什么改大了反而快?

figfig

QUOTE:

以下是引用office2008在2008-9-2 17:06:34的发言：

刚才最后一句太耗时间，所以范围取小一点到d50，现在应该没问题了，又改到d888了

范围为什么改大了反而快?

不是范围的关系，最后一句原来用了4个连续嵌套，所以卡住了，现在用交叉表查询，范围扩大到d8888也没问题了

[此贴子已经被作者于2008-9-2 17:23:29编辑过]

office2008

如果重复只取一条(不考虑errtype是不是不同),留最后一条,谢谢!

[此贴子已经被作者于2008-9-2 17:59:14编辑过]

figfig

QUOTE:

以下是引用office2008在2008-9-2 17:24:01的发言：
如果重复只取一条,留最后一条,谢谢!

Sql = " select distinct aa.num,aa.operation,aa.dt,aa.errtype from [连续3天失败标记$a1:d888] aa,(" & Sql & ") bb  where aa.num=bb.num and aa.operation=bb.operation and aa.dt=bb.dt and aa.errtype=bb.errtype"

figfig

QUOTE:

以下是引用office2008在2008-9-2 15:10:09的发言：

这样的讨论下去很好啊，增长知识和思路，谢谢各位一起讨论。

下面插一个小问题

我看见网上很多 select isnull(字段，null).......

在EXCEL试了 不行啊。请教大家，谢谢

Sql = "select isnull(num) as aa from [连续3天失败标记$a1:d888]"

office2008

假如为空,则为空,否则为字段本身?　AA,BB两个字段

通过 iif(len(aa)>0,aa,bb) 可以　

　　　iif(isnull(AA),bb,aa)　可以

　　　　iif(AA is null,bb,aa)　这样也可以

为何和网上　一样 isnull(aa,bb)不可以？

[此贴子已经被作者于2008-9-2 18:13:15编辑过]