请教高手,如何用SQL或子查询实现以下查询结果

zhaogang1960

原帖由 saler111 于 2011-5-1 16:40 发表

对，，如果sx是一个变量，怎么写？
SELECT a.dm,a.sx,a.jr b.jr  FROM   a left join   b on   a.dm=b.dm and  a.sx='a' and b.sx='a'

变量用&连接如：
SQL = "SELECT a.dm,a." & sx & ",a.jr,iif(isnull(b.jr),'null',b.jr) FROM [Sheet1$] a left join [Sheet2$] b on a.dm=b.dm and a." & sx & "=b." & sx
但好像不能破坏左连接的语法(on   a.dm=b.dm and  a.sx='a' and b.sx='a' )

saler111

原帖由 zhaogang1960 于 2011-5-1 16:47 发表

变量用&连接如：
SQL = "SELECT a.dm,a." & sx & ",a.jr,iif(isnull(b.jr),'null',b.jr) FROM [Sheet1$] a left join [Sheet2$] b on a.dm=b.dm and a." & sx & "=b." & sx
但好像不能破坏左连接的语法(on   a. ...

sx作为一个变量怎么写?
dim iii as string
iii="a"
SELECT a.dm,a.sx,a.jr,b.jr  FROM   a left join   b on   a.dm=b.dm and  a.sx='" & iii & "' and b.sx='" & iii & "'       '---实现不了
好象SQL数据库中，做视图 也不能用变量,,,还有什么方法可以实现?

zhaogang1960

原帖由 saler111 于 2011-5-1 16:56 发表


sx作为一个变量怎么写?
dim iii as string
iii="a"
SELECT a.dm,a.sx,a.jr,b.jr  FROM   a left join   b on   a.dm=b.dm and  a.sx='" & iii & "' and b.sx='" & iii & "'       '---实现不了
好象SQL数据库 ...

变量只能在程序中使用

saler111

原帖由 zhaogang1960 于 2011-5-1 17:05 发表

变量只能在程序中使用

dim iii as string
iii="a"
"SELECT s.dm,s.jr,ss.jr  FROM (select * from a where sx='" & iii & "' )s  left join  (select * from b where sx='" & iii & "' )ss on   s.dm=ss.dm  "

zhaogang1960

原帖由 saler111 于 2011-5-1 17:07 发表

dim iii as string
iii="a"
"SELECT s.dm,s.jr,ss.jr  FROM (select * from a where sx='" & iii & "' )s  left join  (select * from b where sx='" & iii & "' )ss on   s.dm=ss.dm  "

Sub Macro1()
    Dim cnn As Object, SQL$
    Dim iii As String
    iii = "a"
    Set cnn = CreateObject("ADODB.Connection")
    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" & ThisWorkbook.FullName
    SQL = "SELECT s.*,iif(isnull(ss.jr),'null',ss.jr)  FROM (select * from [Sheet1$] where sx='" & iii & "' ) s  left join  (select * from [Sheet2$] where sx='" & iii & "' ) ss on   s.dm=ss.dm  and s.sx=ss.sx "
    [a1].CurrentRegion.Offset(1).ClearContents
    [a2].CopyFromRecordset cnn.Execute(SQL)
    cnn.Close
    Set cnn = Nothing
End Sub

saler111

原帖由 zhaogang1960 于 2011-5-1 17:23 发表

Sub Macro1()
    Dim cnn As Object, SQL$
    Dim iii As String
    iii = "a"
    Set cnn = CreateObject("ADODB.Connection")
    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Ex ...

请问一下,我下面语句怎么提示操作符丢失,我总找不到错误在哪?调试时,所有变量都能找到.
"SELECT a.kmdm,a.kmmc,a.bqjr,a.ljjr,a.byjr,b.bqjr,b.ljjr,b.byjr FROM (select * form " & goa & "  where szlx='" + szlxb + "' and qxbz='" + qxbzb + "' and bbjc='" + bbjcb + "' )a  left join  (select * form " & gob & "  where szlx='" + szlxb + "' and qxbz='" + qxbzb + "' and bbjc='" + bbjcb + "' )b   on   a.kmdm=b.kmdm  order by a.kmdm "

"SELECT a.kmdm,b.kmdm FROM (select * form tmis  ) as a  left join  (select * form tcbs ) as b  on a.kmdm=b.kmdm  order by a.kmdm  " 也不行,唉form错了,应from,不好意思

[ 本帖最后由 saler111 于 2011-5-1 17:59 编辑 ]

zhaogang1960

原帖由 saler111 于 2011-5-1 17:50 发表

请问一下,我下面语句怎么提示操作符丢失,我总找不到错误在哪?调试时,所有变量都能找到.
"SELECT a.kmdm,a.kmmc,a.bqjr,a.ljjr,a.byjr,b.bqjr,b.ljjr,b.byjr FROM (select * form " & goa & "  where szlx='" + s ...

猜不出来，不好意思

saler111

[quote]原帖由 zhaogang1960 于 2011-5-1 17:56 发表

谢谢你了,form错,晕死