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

figfig

QUOTE:

以下是引用office2008在2008-9-4 8:39:05的发言：

已解决一部分,下面增加了平均分和总分

然后在下面增加一条合计,看有红色的地方

谢谢LDY高手

我上传附件,在  "双表比较"  中 full join 在EXCEL里估计难,我做了一个 左右连接 请简化

"两两相同取其一" 中replace函数用不了,只能用 iif, left ,len函数组合而成,看看能否精简

"行列转换" 中　按要求　平均分，总分　再添一条合计．　看附件　，反一下　从　下表转成上表　，又如何办？

其他表的代码看看能否改进化？

excel,access 不支持full join,实现办法是left join+right join +union

Sub yy10()
 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
     r = .Range("a65536").End(xlUp).Row
    .Range("f2:l100").ClearContents

Sql = " select * from [双表比较$a1:d7] a left join  [双表比较$a12:d17] b on a.SKU&a.货物批号&a.生产日期=b.SKU&b.货物批号&b.生产日期"
sql2 = " select * from [双表比较$a1:d7] a right join  [双表比较$a12:d17] b on a.SKU&a.货物批号&a.生产日期=b.SKU&b.货物批号&b.生产日期"

sql3 = Sql & " union " & sql2

Sql = "select  iif(isnull(a.sku),b.sku,a.sku),iif(isnull(a.货物批号),b.货物批号,a.货物批号),iif(isnull(a.生产日期),b.生产日期,a.生产日期),a.数量,b.数量,iif(isnull(a.数量),0,a.数量)-iif(isnull(b.数量),0,b.数量),switch(isnull(a.数量),'库存',isnull(b.数量),'系统',true,'正常')  from (" & sql3 & " )"
  .Range("f2").CopyFromRecordset CNN.Execute(Sql)
End With
End Sub

[此贴子已经被作者于2008-9-4 15:44:33编辑过]

figfig

"两两相同取其一" 中replace函数用不了,只能用 iif, left ,len函数组合而成,看看能否精简

Sub yy9()
 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
     r = .Range("a65536").End(xlUp).Row
     
    
       Sql = " select iif(instr(iif(instr(cc,bb)>0,cc,bb&cc),aa)>0,iif(instr(cc,bb)>0,cc,bb&cc), aa & iif(instr(cc,bb)>0,cc,bb&cc)) from [两两相同取其一$a1:c8]"
     
      .Range("e2:g100").ClearContents
      .Range("e2").CopyFromRecordset CNN.Execute(Sql)
End With
End Sub

解答精彩，帖子比较多，就在这里加分  -ldy

[此贴子已经被ldy于2008-9-4 17:47:20编辑过]

office2008

62的代码好,解决了有空白就只取其一的问题.我也用instr做过,没能理出来,逻辑混乱,呵呵.

office2008

秘诀是要倒过来算,呵呵,figfig大师思维确实是好啊!

office2008

Sub yy11_4()     '行转列
 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
     
SQL = "select * from (select 姓名,'语文' as 课程,语文 from [行列转换$e9:h11]" _
  & " Union all" _
  & " select 姓名,'数学'as 课程,数学 from [行列转换$e9:h11]" _
  & " Union all" _
  & " select 姓名,'物理'as 课程,物理 from [行列转换$e9:h11] ) t " _
  & " order by t.姓名, " _
  & " iif(t.课程 = '语文', 1 ,iif(t.课程 = '数学' , 2 ,iif(t.课程 = '物理',3 ))) "  '语文,数学,物理 这样排序

      .Range("e15:j20").ClearContents
      .Range("e15").CopyFromRecordset CNN.Execute(SQL)
     
End With

End Sub

[此贴子已经被作者于2008-9-4 21:36:02编辑过]

office2008

行转列 加 平均分 和 总分

Sub yy11_5()     '行转列
 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
     r = .Range("a65536").End(xlUp).Row
    
     
      SQL = "  select * from (" _
  & " select 姓名 as 姓名 ,'语文' as 课程,  语文  from [行列转换$e9:h11]" _
  & " Union all " _
  & " select 姓名 as 姓名 ,'数学' as 课程,  数学  from [行列转换$e9:h11]" _
  & " Union all " _
  & " select 姓名 as 姓名 ,'物理' as 课程,  物理  from [行列转换$e9:h11]" _
  & " Union all" _
  & " select 姓名 as 姓名 ,'平均分' as 课程, (语文 + 数学 + 物理)/3  from [行列转换$e9:h11]" _
  & " Union all" _
  & " select 姓名 as 姓名 , '总分' as 课程,语文 + 数学 + 物理  from [行列转换$e9:h11] ) t" _
  & " order by 姓名, " _
  & " iif(t.课程 = '语文', 1 ,iif(t.课程 = '数学' , 2 ,iif(t.课程 = '物理',3 ,iif(t.课程 = '平均分',4 ,iif(t.课程 = '总分',5 ))))) "

      .Range("e15:j26").ClearContents
      .Range("e15").CopyFromRecordset CNN.Execute(SQL)
     
End With

End Sub

figfig

  建议用 switch

 SQL = "  select * from (" _
  & " select 姓名 as 姓名 ,'语文' as 课程,  语文  from [行列转换$e9:h11]" _
  & " Union all " _
  & " select 姓名 as 姓名 ,'数学' as 课程,  数学  from [行列转换$e9:h11]" _
  & " Union all " _
  & " select 姓名 as 姓名 ,'物理' as 课程,  物理  from [行列转换$e9:h11]" _
  & " Union all" _
  & " select 姓名 as 姓名 ,'平均分' as 课程, (语文 + 数学 + 物理)/3  from [行列转换$e9:h11]" _
  & " Union all" _
  & " select 姓名 as 姓名 , '总分' as 课程,语文 + 数学 + 物理  from [行列转换$e9:h11] ) t" _
  & " order by 姓名, " _
  & " switch(t.课程 = '语文', 1 ,t.课程 = '数学' , 2 ,t.课程 = '物理',3 ,t.课程 = '平均分',4 ,t.课程 = '总分',5 ) "

office2008

哈哈哈哈，我用了，跟你的一样，可就是不行。

仔细看了，原来 switch 我写成 swith

这个函数用处蛮多，我正奇怪着呢，谢谢 figfig 大师

[此贴子已经被作者于2008-9-4 22:24:29编辑过]

office2008

F1	F2
a	a1
b	b1
c	c1
a	a3
a	a4
b	b2
c	c2
c	c3
d	d5

效果:

f1	t1	t2	t3	t4	t5
a	a1		a3	a4
b	b1	b2
c	c1	c2	c3
d					d5

给高手练练手,让我们也学习学习.谢谢!

office2008

IP1	ip2	name	pwd	kkk
a1	a2	aaa	111	a
a1	a2	aaa	111	b
a1	a2	aaa	111	c
b1	b2	bbb	111	d
b1	b2	bbb	111	e
b1	b2	ccc	111	f

要求

IP1   ip2  name   pwd   kkk
a1    a2   aaa    111   a,b,c
b1    b2   bbb    111   d,e
b1    b2   ccc    111   f

包括楼上的附件,请帮助

 

 

 

fxjM3lC8.rar (20.17 KB, 下载次数: 3)

2008-9-5 22:24 上传

点击文件名下载附件
求助- sql中未知(空值)的解决办法(已解决)

[此贴子已经被作者于2008-9-5 22:25:14编辑过]