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

sgrshh29

QUOTE:

以下是引用office2008在2008-9-3 23:45:21的发言：

姓名	课程	分数
张三	语文	74
张三	数学	83
张三	物理	93
李四	语文	74
李四	数学	84
李四	物理	94

转换成

姓名	语文	数学	物理
张三	74	83	93
李四	74	84	94

 ID = Join(Split("语文,数学,物理", ","), "','")
 SQL = " transform max(分数) select 姓名  from [行列转换$a1:c" & r & "] " _
 & " group by 姓名  pivot 课程 in ('" & ID & "') "   

如果

想转成

1,效果:
 姓名  语文  数学  物理  平均分  总分
 张三   74    83    93    83.33   250
 李四   74    84    94    84      252
 合计   148   167  187   167.33   502

请高手继续帮忙，谢谢！   

???

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

office2008

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

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

谢谢LDY高手

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

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

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

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

2KhHAibc.rar (37.5 KB, 下载次数: 12)

2008-9-4 08:43 上传

点击文件名下载附件

[此贴子已经被作者于2008-9-4 8:43:31编辑过]

sgrshh29

成绩问题：

Sql = "SELECT a.姓名, a.分数 AS 语文, b.分数 AS 数学, c.分数 AS 物理, round(d.平均分,2) AS 平均分, " _
      & "d.总分 AS 总分 FROM [sheet1$a1:c7] AS a, [sheet1$a1:c7] AS b, [sheet1$a1:c7] AS c, (SELECT 姓名, " _
      & "Sum(分数)/Count(课程) AS 平均分, Sum(分数) AS 总分 FROM [sheet1$a1:c7] GROUP BY 姓名) AS d " _
      & "WHERE a.姓名 = b.姓名 And a.姓名 = c.姓名 And a.姓名 = d.姓名 And a.课程 = '语文' And " _
      & "b.课程 = '数学' And c.课程 = '物理' UNION SELECT '合计' AS 姓名, Sum(aa.语文) AS 语文, " _
      & "Sum(aa.数学) AS 数学, Sum(aa.物理) AS 物理, round(Sum(aa.平均分),2) AS 平均分, " _
      & "Sum(aa.总分) AS 总分 FROM (SELECT a.姓名, a.分数 AS 语文, b.分数 AS 数学, c.分数 AS 物理, " _
      & "d.平均分 AS 平均分, d.总分 AS 总分 FROM [sheet1$a1:c7] AS a, [sheet1$a1:c7] AS b, " _
      & "[sheet1$a1:c7] AS c, (SELECT 姓名, Sum(分数)/Count(课程) AS 平均分, Sum(分数) AS 总分 " _
      & "FROM [sheet1$a1:c7] GROUP BY 姓名) AS d WHERE a.姓名=b.姓名 and a.姓名=c.姓名 and " _
      & "a.姓名=d.姓名 and a.课程='语文' AND b.课程='数学' AND c.课程='物理') as aa " _
      & "Group BY '合计' ORDER BY a.姓名 DESC;"

office2008

[em17][em17][em17]

这么多代码看起来都累啊,高手啊!

figfig

QUOTE:

'建议仍然用 trnasform pivot 的强大功能

Sub yy11()   '列转行
 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 姓名, '总分' ,sum(分数)  from [行列转换$a1:c7] group by 姓名"
      Sql = Sql & " union (select 姓名, '平均分' ,avg(分数)  from [行列转换$a1:c7] group by 姓名)"
      Sql = " select * from [行列转换$a1:c7] union (" & Sql & ")"
      SQL2 = Sql
      Sql = " select  '合计'  ,课程 ,sum(分数) from (" & Sql & ") group by 课程"
      Sql = SQL2 & " union (" & Sql & ")"
     
     
      Sql = "transform FIRST(分数) select 姓名 from  (" & Sql _
      & ") group by 姓名 order by 姓名 DESC pivot 课程 in ('语文','数学','物理','平均分','总分')  "
     
      .Range("e2:h100").ClearContents
      .Range("e2").CopyFromRecordset CNN.Execute(Sql)
End With
End Sub

[此贴子已经被作者于2008-9-4 12:42:38编辑过]

ifhwfu

牛人啊！以前 爱歌学习 把她的所有代码实例注释并共享出来，现在 figfig 大师又给所有的 SQL 难题作了解答。学习了，可以不是一时半会能理解的。

ifhwfu

如果有心人能把 figfig 所有的回答都搜集起来，并用附件加入代码，我想肯定又是一个学习 SQL 最好的帖子了。

我想搜集 figfig 的所有恢复帖，但是搜索 figfig （按姓名搜索），只能搜索 figfig 的主题帖。但是 figfig 大师很少发表主题帖，他只是回帖。

不知论坛怎么搜索得到 figfig 的所有回复帖子。

虽然能搜索到 figfig 的“文集”(含有附件的文件帖子)，但是，很多时候，figfig 没有上传附件，都是在跟帖中直接恢复代码。

office2008

用SQL2 寄存, SQL 嵌套,妙啊。

[em17][em17][em17]

ldy

QUOTE:

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

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

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

谢谢LDY高手

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

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

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

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

只改了一个 ，也是要左右连接，只不过变成两个左连接

Sub 双表比较()
 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
 sq1 = "select a.SKU,a.货物批号,a.生产日期,iif(isnull(a.数量),0,a.数量)as a数量,iif(isnull(b.数量),0,b.数量)as b数量 from[双表比较$a1:d7]a left join[双表比较$a12:d17]b on a.SKU=b.SKU and a.货物批号=b.货物批号 and a.生产日期=b.生产日期"
 sq2 = "select a.SKU,a.货物批号,a.生产日期,iif(isnull(b.数量),0,b.数量)as a数量,iif(isnull(a.数量),0,a.数量)as b数量 from[双表比较$a12:d17]a left join[双表比较$a1:d7]b on a.SKU=b.SKU and a.货物批号=b.货物批号 and a.生产日期=b.生产日期"
 Sql = "select a.SKU,a.货物批号,a.生产日期 ,a数量,b数量,(a数量-b数量),iif(a数量,iif(b数量,'正常','系统'),'库房')from(" & sq1 & " union " & sq2 & ")"
  .Range("f2").CopyFromRecordset cnn.Execute(Sql)
   Debug.Print Len(Sql) '455
 End With
End Sub

Sub 双表比较2()'  SKU,货物批号,生产日期,数量 分别=  f1, f2,f3,f4     hdr=no
With Worksheets("双表比较")
  Set cnn = CreateObject("adodb.connection")
  cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;hdr=no;';Data Source=" & ThisWorkbook.FullName
  r = .Range("a65536").End(xlUp).Row
  .Range("f2:l100").ClearContents
 sq1 = "select a.f1,a.f2,a.f3,iif(isnull(a.f4),0,a.f4)as af4,iif(isnull(b.f4),0,b.f4)as bf4 from[双表比较$a2:d7]a left join[双表比较$a12:d17]b on a.f1=b.f1 and a.f2=b.f2 and a.f3=b.f3"
 sq2 = "select a.f1,a.f2,a.f3,iif(isnull(b.f4),0,b.f4)as af4,iif(isnull(a.f4),0,a.f4)as bf4 from[双表比较$a13:d17]a left join[双表比较$a1:d7]b on a.f1=b.f1 and a.f2=b.f2 and a.f3=b.f3"
 Sql = "select a.f1,a.f2,a.f3 ,af4,bf4,(af4-bf4),iif(af4,iif(bf4,'正常','系统'),'库房')from(" & sq1 & " union " & sq2 & ")"
  .Range("f2").CopyFromRecordset cnn.Execute(Sql)
   Debug.Print Len(Sql) '420
 End With
End Sub

ldy

QUOTE:

以下是引用ifhwfu在2008-9-4 12:58:38的发言：

如果有心人能把 figfig 所有的回答都搜集起来，并用附件加入代码，我想肯定又是一个学习 SQL 最好的帖子了。

我想搜集 figfig 的所有恢复帖，但是搜索 figfig （按姓名搜索），只能搜索 figfig 的主题帖。但是 figfig 大师很少发表主题帖，他只是回帖。

不知论坛怎么搜索得到 figfig 的所有回复帖子。

虽然能搜索到 figfig 的“文集”(含有附件的文件帖子)，但是，很多时候，figfig 没有上传附件，都是在跟帖中直接恢复代码。

汗 论坛基本功能 。点击 姓名后的 ※短信 ※好友 ※档案 ※帖子 ※邮件    当中的 ※帖子

http://club.excelhome.net/query.asp?stype=1&nSearch=3&keyword=figfig&BoardID=2&SearchDate=ALL