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

office2008

 第一题：网上的方法

一知半解，那位高手有心人能代码解说一下。谢谢！

Sub yy15()

 With Worksheets("行列转换2")
   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("e2:n10").ClearContents
     r = .Range("a65536").End(xlUp).Row
     
    SQL = " select f1 ,max(iif(mid(f2,2,2)=1,f2,'')) as t1," _
                   & " max(iif(mid(f2,2,2)=2,f2,'')) as t2," _
                   & " max(iif(mid(f2,2,2)=3,f2,'')) as t3," _
                   & " max(iif(mid(f2,2,2)=4,f2,'')) as t4," _
                   & " max(iif(mid(f2,2,2)=5,f2,'')) as t5," _
                   & " max(iif(mid(f2,2,2)=6,f2,'')) as t6," _
                   & " max(iif(mid(f2,2,2)=7,f2,'')) as t7" _
                   & " from (" _
                   & " select t.* , (select count(1) from [行列转换2$a1:b" & r & "] " _
                   & " where f1 = t.f1 and f2 < t.f2) + 1 from [行列转换2$a1:b" & r & "] t ) m" _
                   & " group by f1  "
     
 
  
     .Range("e2").CopyFromRecordset CNN.Execute(SQL)
End With
End Sub

figfig

QUOTE:

以下是引用office2008在2008-9-6 12:09:00的发言：

 第一题：网上的方法

一知半解，那位高手有心人能代码解说一下。谢谢！

Sub yy15()

 With Worksheets("行列转换2")
   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("e2:n10").ClearContents
     r = .Range("a65536").End(xlUp).Row
     
    SQL = " select f1 ,max(iif(mid(f2,2,2)=1,f2,'')) as t1," _
                   & " max(iif(mid(f2,2,2)=2,f2,'')) as t2," _
                   & " max(iif(mid(f2,2,2)=3,f2,'')) as t3," _
                   & " max(iif(mid(f2,2,2)=4,f2,'')) as t4," _
                   & " max(iif(mid(f2,2,2)=5,f2,'')) as t5," _
                   & " max(iif(mid(f2,2,2)=6,f2,'')) as t6," _
                   & " max(iif(mid(f2,2,2)=7,f2,'')) as t7" _
                   & " from (" _
                   & " select t.* , (select count(1) from [行列转换2$a1:b" & r & "] " _
                   & " where f1 = t.f1 and f2 < t.f2) + 1 from [行列转换2$a1:b" & r & "] t ) m" _
                   & " group by f1  "
     
 
  
     .Range("e2").CopyFromRecordset CNN.Execute(SQL)
End With
End Sub

晕，楼上的代码不就是简单的对号如座吗，跟下面的一样，觉得这种思路有点简单，

  Sql = " select f1 ,max(iif(mid(f2,2,2)=1,f2,'')) as t1," _
                   & " max(iif(mid(f2,2,2)=2,f2,'')) as t2," _
                   & " max(iif(mid(f2,2,2)=3,f2,'')) as t3," _
                   & " max(iif(mid(f2,2,2)=4,f2,'')) as t4," _
                   & " max(iif(mid(f2,2,2)=5,f2,'')) as t5," _
                   & " max(iif(mid(f2,2,2)=6,f2,'')) as t6," _
                   & " max(iif(mid(f2,2,2)=7,f2,'')) as t7" _
                   & " from " _
                   & "  [行列转换2$a1:b" & r & "] " _
                   & " group by f1  "

office2008

看懂了就简单,象我一样不怎么懂就觉得复杂,我的那个代码是CSDN网上复制过来的,他是用的 CASE WHEN . 在 CSDN 上 SQL SERVER 2005 板块中发过来的几个帖子都能被你们解决,因为语言支持的问题说明我们这边的高手绝对不会比他们差,

figfig

如果变量有几百个，这个写法根本不行

呵呵，提供一下本人的写法 [em04]

 Sql = " transform max(f2) select f1 from [行列转换2$a1:b10] group by f1 pivot mid(f2,2,2)"
    

[此贴子已经被作者于2008-9-6 17:01:09编辑过]

office2008

怪不得网上“ 牛人 ”盛行 ， 原来是指你们一部分人啊，这也太浓缩了吧，呵呵慢点，得先让我看懂了再说吗。呵呵

[em17][em17]

ldy

先收下，以后慢慢看[em17]

sgrshh29

也出二题，凑凑热闹。要求用sql语句解决。具体见附件。一个是根据进库出库数量统计期初数和期末数（不同商品要同时处理），二个是选择题答卷的统计问题，29份答卷，10道答题，每题有5个选项（a、b、c、d、e），要统计每道题的每个选项数目。 WsvR6Dgl.rar (3.63 KB, 下载次数: 5)

2008-9-7 11:51 上传

点击文件名下载附件
SQL难题接龙(请高手继续关注)

office2008

大师就是大师,出的题太难了

figfig

QUOTE:

以下是引用sgrshh29在2008-9-7 11:52:00的发言：
也出二题，凑凑热闹。要求用sql语句解决。具体见附件。一个是根据进库出库数量统计期初数和期末数（不同商品要同时处理）

以下代码是针对题目的3个月统计的,如在实际应用中需把代码扩展到12个月,

Sub fig()
Set x = CreateObject("adodb.connection")
x.Open "provider=microsoft.jet.oledb.4.0;extended properties=excel 8.0;data source=" & ThisWorkbook.FullName
Sql1 = " (select month(日期) as m1, 商品编号 as s1,    sum(进货数量) as j1,    sum(销售数量) as j2 from [商品进出库期初期末$a1:d25]  group by month(日期),商品编号)"
sql3 = "  iif(isnull(c.j1),0,c.j1)-iif(isnull(c.j2),0,c.j2)+iif(isnull(b.j1),0,b.j1)-iif(isnull(b.j2),0,b.j2)+a.j1-a.j2"
Sql = "select '2007-0' & a.m1, a.s1, " & sql3 & "+a.j2-a.j1 ,a.j1,a.j2 ," & sql3 & " from ( " & Sql1 & " a left join " & Sql1 & " b on a.s1=b.s1 and a.m1-b.m1=1)  left join " & Sql1 & " c on  a.s1=c.s1 and a.m1-c.m1=2"

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

office2008

厉害,这也能做.

[em17][em17][em17]