求助：這個查詢代碼如何寫？

yhf

附件db.mdb中有2個表：學(xué)校、表1。 請教諸位：現(xiàn)要在窗體1中點擊 更新 按鈕后，根據(jù)右側(cè)子窗體內(nèi)容 生成左側(cè)列表 內(nèi)容。謝謝�。ㄗⅲ河覀�(cè)子窗體中記錄數(shù)是不確定的，但至少2條）

Henry D. Sy

描述不清，表1應(yīng)該是不存在的，只是你想要的結(jié)果對嗎？

anthonywang9

試試這個
SELECT *
FROM
(SELECT ID as 組合ID,學(xué)校 as 組合學(xué)校,代碼 as 組合代碼
from 學(xué)校
union
SELECT [a.ID]&[b.ID] AS 組合ID, [a.學(xué)校]&";"&[a.代碼]&";"&[b.學(xué)校]&";"&[b.代碼] AS 組合學(xué)校, [a.代碼]&"/"&[b.代碼] AS 組合代碼
FROM 學(xué)校 AS a, 學(xué)校 AS b
WHERE a.ID<b.ID
union
SELECT [a.ID]&[b.ID]&[c.ID] AS 組合ID, [a.學(xué)校]&";"&[a.代碼]&";"&[b.學(xué)校]&";"&[b.代碼]&";"&[c.學(xué)校]&";"&[c.代碼] AS 組合學(xué)校, [a.代碼]&"/"&[b.代碼]&"/"&[c.代碼] AS 組合代碼
FROM 學(xué)校 AS a, 學(xué)校 AS b, 學(xué)校 AS c
WHERE a.ID<b.ID and b.id<c.ID
union
SELECT [a.ID]&[b.ID]&[c.ID]&[d.ID] AS 組合ID, [a.學(xué)校]&";"&[a.代碼]&";"&[b.學(xué)校]&";"&[b.代碼]&";"&[c.學(xué)校]&";"&[c.代碼]&";"&[d.學(xué)校]&";"&[d.代碼] AS 組合學(xué)校, [a.代碼]&"/"&[b.代碼]&"/"&[c.代碼]&"/"&[c.代碼] AS 組合代碼
FROM 學(xué)校 AS a, 學(xué)校 AS b, 學(xué)校 AS c, 學(xué)校 AS d
WHERE a.ID<b.ID and b.id<c.ID and c.id<d.ID)  AS [%$##@_Alias]
ORDER BY cint([組合ID]);

yhf

Henry D. Sy 發(fā)表于 2012-6-16 11:52
描述不清，表1應(yīng)該是不存在的，只是你想要的結(jié)果對嗎？

表1存在，更新后的數(shù)據(jù)保存在表1.謝謝！

yhf

anthonywang9 發(fā)表于 2012-6-16 12:38
試試這個
SELECT *
FROM

太棒了，謝謝！

yhf

anthonywang9 發(fā)表于 2012-6-16 12:38
試試這個
SELECT *
FROM

謝謝anthonywang9 ：我試了一下，可用。但只適用最多4條記錄，學(xué)校表內(nèi)記錄數(shù)大于4時，查詢生成的數(shù)據(jù)不全(比如5條記錄時，組合ID就缺少12345，6條記錄時出錯：溢出)

anthonywang9

額，我以為你的學(xué)校校區(qū)“東南西北”完了啊~~
如果你的源表【學(xué)�！坑涗洈�(shù)（r）是可變的話，那么就存在r個表間自連接，這種情況的話最好用VBA寫吧，單純的SQL語句可能不能寫出實現(xiàn)這樣的功能。

yhf

anthonywang9 發(fā)表于 2012-6-16 15:14
額，我以為你的學(xué)校校區(qū)“東南西北”完了啊~~
如果你的源表【學(xué)校】記錄數(shù)（r）是可變的話，那么就存在r個 ...

請教 anthonywang9: VBA該如何寫？ 謝謝！

Henry D. Sy

好像沒有規(guī)律，如果沒有規(guī)律那只能手工輸入記錄了。{:soso_e110:}
看了半天也看不出具體規(guī)律。
比如看不到134組合！

anthonywang9

Sub a()
Dim i As Integer: Dim m%
Dim Tsql As String: Dim sql() As String
i = DCount("ID", "學(xué)校")
ReDim sql(1 To i)
For m = 1 To i
sql(m) = "select " + QuerySub1(m) + "," + QuerySub2(m) + "," + QuerySub3(m) + QuerySub4(m) + QuerySub5(m)
Tsql = Tsql + " union " + sql(m)
Next m
Tsql = "selcet * from (" + Right(Tsql, Len(Tsql) - 6) + ") order by cint([組合ID])"
End Sub

Function QuerySub1(count As Integer) As String
Dim i%
For i = 1 To count
QuerySub1 = QuerySub1 + "&[a" & i & ".ID]"
Next i
QuerySub1 = Right(QuerySub1, Len(QuerySub1) - 1) + " as 組合ID"
End Function

Function QuerySub2(count As Integer) As String
Dim i%
For i = 1 To count
QuerySub2 = QuerySub2 + "&"";""&[a" & i & ".學(xué)校]&"";""&[a" & i & ".代碼]"
Next i
QuerySub2 = Right(QuerySub2, Len(QuerySub2) - 5) + " as 組合學(xué)校"
End Function

Function QuerySub3(count As Integer) As String
Dim i%
For i = 1 To count
QuerySub3 = QuerySub3 + "&""/""&[a" & i & ".代碼]"
Next i
QuerySub3 = Right(QuerySub3, Len(QuerySub3) - 5) + " as 組合代碼"
End Function

Function QuerySub4(count As Integer) As String
Dim i%
For i = 1 To count
QuerySub4 = QuerySub4 + ",學(xué)校 as a" & i
Next i
QuerySub4 = " from " + Right(QuerySub4, Len(QuerySub4) - 1)
End Function

Function QuerySub5(count As Integer) As String
Dim i%
If count = 1 Then
QuerySub5 = ""
Else
For i = 1 To count - 1
QuerySub5 = QuerySub5 + " and a" & i & ".ID<a" & (i + 1) & ".ID"
Next i
QuerySub5 = " where " + Right(QuerySub5, Len(QuerySub5) - 4)
End If
End Function
Tsql是最終的查詢語句，完全的sql語句拼接策略。由于Tsql長度過長，我的access無法支持調(diào)試。其中對于每一個m值的sql(m)我已經(jīng)調(diào)試過，沒有問題。
你可以建立i = DCount("ID", "學(xué)校")個中間查詢，其中QueryDef(i)=sql(i),然后再使用union方法。