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

figfig

QUOTE:

以下是引用sgrshh29在2008-9-7 11:52:00的发言：
也出二题，凑凑热闹。要求用sql语句解决，二个是选择题答卷的统计问题，29份答卷，10道答题，每题有5个选项（a、b、c、d、e），要统计每道题的每个选项数目。

Sub fig2()

Dim arr()
Set x = CreateObject("adodb.connection")
x.Open "provider=microsoft.jet.oledb.4.0;extended properties=excel 8.0;data source=" & ThisWorkbook.FullName

For i = 1 To 10
j = j + 1
ReDim Preserve arr(1 To j)
arr(j) = "select '题" & i & "' as aa,题" & i & " as bb   from [选择题答案统计$a1:k30] "
Next
sql1 = Join(arr, " union all ")
Sql = " transform count(a.bb) select a.aa from  (" & sql1 & ")  a  group by len(a.aa),a.aa    pivot a.bb"

Set y = x.Execute(Sql)
Sheet2.[m20:z5000].ClearContents
Sheet2.[m20].CopyFromRecordset y
End Sub

lookice

请教：sql中表名来自其它表字段值，怎么查询该表？

有表A,表B

表A：字段 tablename（存放相关表名称）如"表B"

select * from (select tablename from 表A where 条件)

这样查表B肯定不行！不知道怎么改？

lookice

QUOTE:

For i = 1 To 10
j = j + 1
ReDim Preserve arr(1 To j)
arr(j) = "select '题" & i & "' as aa,题" & i & " as bb   from [选择题答案统计$a1:k30] "
Next
sql1 = Join(arr, " union all ")
Sql = " transform count(a.bb) select a.aa from  (" & sql1 & ")  a  group by len(a.aa),a.aa    pivot a.bb"

vba中不知道能否使用select into创建临时表？如果可以上面for循环可以使用identity(int,1,1)

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

ldy

QUOTE:

以下是引用lookice在2008-9-8 0:18:00的发言：

请教：sql中表名来自其它表字段值，怎么查询该表？

有表A,表B

表A：字段 tablename（存放相关表名称）如"表B"

select * from (select tablename from 表A where 条件)

这样查表B肯定不行！不知道怎么改？

select * from (select tablename from 表A where 条件)   等同于 select * from '表B'

如果去掉 单引号是可以以执行的。

参照VBA语法 修改 为 select * from tables('表B')

但一样无法运行，不知道ado中有没有类似的函数可以用变量来表示 表，也许不是tables了，但是什么没有查到。

或许ado不支持着样表达，如果是这样 只有写成两句，先取得 "表B"

在用语句 组织成 select * from 表B

顶起来，看看哪位能给个肯定的答复

lookice

查找满足条件的记录比较（内部机制原理分析）

excel表

name date salary

a    200805  2000

b    200806  2200

c    200806  2000

a    200608  2100

.....

要得到a,各月salary是多少

方法：

1、函数法：if(a2:a5="a",c2:c5,"")
2、筛选
3、sql语句 (select salary from [sheet1$a1:c5] where name='a')
4、vba循环法
 for i=2 to 5
  if cells(i,1)="a" then
     cells(range("a65536").end(xlup).end+1,4)=cells(i,3).value 
  end if
 next i

vba循环法很容易看出查找满足条件的机制：循环比较
1、而函数、筛选、SQL又是以什么机制（原理）去查找满足条件的记录？

2、excel筛选功能是不是sql的界面操作版？（个人意见）

3、4中方法各适合什么背景？速度优劣？谁是最地层机制？

对于excel功能的内部机制原理不知道大家有什么建议？欢迎大家讨论！

[此贴子已经被作者于2008-9-8 0:58:53编辑过]

sgrshh29

QUOTE:

以下是引用figfig在2008-9-7 19:45:00的发言：

Sub fig2()

Dim arr()
Set x = CreateObject("adodb.connection")
x.Open "provider=microsoft.jet.oledb.4.0;extended properties=excel 8.0;data source=" & ThisWorkbook.FullName

For i = 1 To 10
j = j + 1
ReDim Preserve arr(1 To j)
arr(j) = "select '题" & i & "' as aa,题" & i & " as bb   from [选择题答案统计$a1:k30] "
Next
sql1 = Join(arr, " union all ")
Sql = " transform count(a.bb) select a.aa from  (" & sql1 & ")  a  group by len(a.aa),a.aa    pivot a.bb"

Set y = x.Execute(Sql)
Sheet2.[m20:z5000].ClearContents
Sheet2.[m20].CopyFromRecordset y
End Sub

[em17]

我是这样写的，与上面的写法一致。

Sheet2.[m20:r50].ClearContents
Set cnn = CreateObject("adodb.connection")
cnn.Open "provider=microsoft.jet.oledb.4.0;extended properties=excel 8.0;data source=" & ThisWorkbook.FullName
For i = 1 To 10
Sql = Sql & " select '题" & i & "' as th, 题" & i & " as da from [选择题答案统计$a1:k30] union all"
Next i
Sql = Left(Sql, Len(Sql) - 10)
Sql = "transform count(a.da) select a.th from (" & Sql & ") a group by a.th pivot a.da"
Sheet2.[m20].CopyFromRecordset cnn.Execute(Sql)
Set cnn = Nothing

sgrshh29

QUOTE:

以下是引用figfig在2008-9-7 18:56:00的发言：

以下代码是针对题目的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

这个还有些问题，如果数据断月（某个月没有数据）？，如果跨年度？

figfig

QUOTE:

以下是引用sgrshh29在2008-9-8 10:15:00的发言：

这个还有些问题，如果数据断月（某个月没有数据）？，如果跨年度？

如果数据断月（某个月没有数据）？，已解决

如果跨年度？已解决

新代码可查询不同月份和年度

Sub fig8()
Set x = CreateObject("adodb.connection")
x.Open "provider=microsoft.jet.oledb.4.0;extended properties=excel 8.0;data source=" & ThisWorkbook.FullName

kk = Year(Application.Max(Sheet1.[a2:a25])) & Right(100 + Month(Application.Max(Sheet1.[a2:a25])), 2)
ll = Year(Application.Min(Sheet1.[a2:a25])) & Right(100 + Month(Application.Min(Sheet1.[a2:a25])), 2)

 For mm = ll To kk

sql5 = "select '" & mm & "' as mm,商品编号 as ss,sum(进货数量)-sum(销售数量) as tt  from [商品进出库期初期末$a1:d25]  where  year(日期)&right(100+month(日期),2)<=" & mm & "  group by 商品编号  "
sql6 = sql5 & " union all " & sql6
If Right(mm, 2) = 12 And Left(ll, 4) <> Left(kk, 4) Then
mm = Left(ll, 4) + 1 & "00"
End If

Next
sql6 = Left(sql6, Len(sql6) - 10)

sql7 = " select year(日期)&right(100+month(日期),2) as mm,商品编号 as ss, sum(进货数量) as vv,sum(销售数量) as ww from [商品进出库期初期末$a1:d25] group by year(日期)&right(100+month(日期),2), 商品编号"
sql8 = " select aa.mm,aa.ss,aa.tt+iif(isnull(bb.ww),0,bb.ww)-iif(isnull(bb.vv),0,bb.vv),bb.vv,bb.ww ,aa.tt from (" & sql6 & ") aa left join (" & sql7 & ") bb on aa.mm&aa.ss=bb.mm&bb.ss order by aa.mm,aa.ss"

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

[此贴子已经被作者于2008-9-8 21:39:14编辑过]

office2008

[em02][em02][em02][em17][em17],厉害,几大网站光顾,用VBA+SQL高手很多,达到你这样境界的高手应该不多.

gongqiulin

虚心学习！谢谢 ！