帮忙解决下,asp+access 数据库查询有关问题
帮忙解决下,asp+access 数据库查询问题
数据库中有表:tb
id c_id name hangye
1 1 f 4
2 1 4 4
3 2 4 5
4 2 e h
5 3 l h
各字段中,相同的记录显示一次,不重复的全部显示
查询结果为:
c_id name hangye
1 f/4 4
2 4/e 5/h
3 1 h
------解决方案--------------------
数据库中有表:tb
id c_id name hangye
1 1 f 4
2 1 4 4
3 2 4 5
4 2 e h
5 3 l h
各字段中,相同的记录显示一次,不重复的全部显示
查询结果为:
c_id name hangye
1 f/4 4
2 4/e 5/h
3 1 h
------解决方案--------------------
- VBScript code
<% sConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\AA.mdb;" Set conn = CreateObject("ADODB.Connection") conn.Provider = "MSDataShape" conn.ConnectionString = "Data " & sConn conn.Open sql = "SHAPE{SELECT DISTINCT cid FROM tb} " & _ "APPEND({" &_ "SELECT cid, name, hangye FROM tb " &_ "} AS tbl " & _ "RELATE cid TO cid)" s = "" Set rs = CreateObject("ADODB.Recordset") rs.CursorLocation = 3 rs.Open sql, conn, 1, 1 Do While Not rs.EOF s = s & rs("cid").Value Set rs1 = rs("tbl").Value n = "/" h = "/" Do While Not rs1.EOF If InStr(n, "/" & rs1("name").Value &"/") = 0 Then n = n & rs1("name").Value & "/" End If If InStr(h, "/" & rs1("hangye").Value &"/") = 0 Then h = h & rs1("hangye").Value & "/" End If rs1.MoveNext Loop rs1.Close Set rs1 = Nothing If n<>"" Then n = Left(n, Len(n)-1) n = Right(n, Len(n)-1) End If If h<>"" Then h = Left(h, Len(h)-1) h = Right(h, Len(h)-1) End If s = s & " " & n & " " & h & "<br>" & vbCrLf rs.MoveNext Loop rs.Close Set rs = Nothing conn.Close Set conn = Nothing Response.Write s %>