标题: 求助:如何在查询中生成一个序号字段 [打印本页] 作者: zrj898 时间: 2012-7-19 19:40 标题: 求助:如何在查询中生成一个序号字段 比如1、2、3....的字段。有简单的办法比如函数吗?作者: todaynew 时间: 2012-7-19 20:32
用别名即可,比如
select 字段1 as 1,字段2 as 2 from tbname作者: Henry D. Sy 时间: 2012-7-20 11:56
答案是肯定的,但你要传例子, 作者: roych 时间: 2012-7-20 22:00
一般来说,可以考虑用DCount函数来处理。作者: Henry D. Sy 时间: 2012-7-21 15:10
cuxun 发表于 2012-7-21 11:49
有主键就好办.
没有主键也可以
select (select count(名称) from 商品分类表 b where b.名称<=a.名称) as 序号,a.名称
from 商品分类表 a
order by a.名称
复制代码
[attach]49725[/attach]
作者: 简 时间: 2012-7-22 15:21
Henry D. Sy 发表于 2012-7-21 15:10
没有主键也可以
问问,在Henry D版主的例子中,是不是要求名称必须唯一,如果像记录中有重复的,还能生成序号吗?
还有,在有重复记录的交叉表中,能生成序号不呢?作者: Henry D. Sy 时间: 2012-7-22 15:39
如果重复可以在增加第二个字段来比较作者: zrj898 时间: 2012-7-23 18:36
感谢大家,深受启发现在把我的解决办法提供一下,办法很笨,但也简单:
Private Sub StrDsql()
Dim SS1, SS2, SS3, SS4, SS5, SS6, SS7, SS8 As String
Dim iii, XH, PH, HH, Cb3 As Integer
If XmLx = "二维" And Cb01.Value = "按线号" Then
QSql = Sql1 & Sql2 & WHSql & Str01 & Str02 & Str04 & ODSql2X
End If
If XmLx = "三维" And Cb01.Value = "按线号" Then
QSql = Sql1 & Sql2 & WHSql & Str01 & Str02 & Str04 & ODSql3X
End If
If XmLx = "三维" And Cb01.Value = "按排号" Then
QSql = Sql1 & Sql2 & WHSql & Str01 & Str02 & Str04 & ODSql3P
End If
'以下是生成的qsql的语句
'print qsql
'SELECT a.线号, a.类型, b.子线号, b.设计线号, b.设计点号, b.简化桩号, a.点号, '278队/2012年' AS 队号,
'0 AS 序号, 0 AS 分栏, 0 as 分行 FROM 测线设计数据 AS a LEFT JOIN 设计物理点列表 AS b ON a.重新设计ID = b.ID
'where (a.类型 in ('G','S')) and a.项目ID= 51 and a.成果数据ID>0 and b.设计线号='2085' and val(b.设计点号)>=2001 and val(b.设计点号)<=2392 ORDER BY b.设计线号, b.设计点号
Sql = "DELETE 打印桩号临时表.* FROM 打印桩号临时表"
DoCmd.RunSQL Sql
Set Rst01 = DbTemp.OpenRecordset(QSql)
Rst01.MoveFirst
XH = 0
Cb3 = Rst01.RecordCount \ 3
If (Rst01.RecordCount Mod 3) > 0 Then Cb3 = Cb3 + 1
Do While Not Rst01.EOF
XH = XH + 1
HH = XH Mod Cb3
If HH = 0 Then HH = Cb3
If HH = Cb3 Then PH = (XH \ Cb3) Else PH = (XH \ Cb3) + 1
SS1 = Rst01.Fields("线号").Value
SS2 = Rst01.Fields("类型").Value
SS3 = Rst01.Fields("点号").Value
SS4 = Rst01.Fields("设计线号").Value
SS5 = Rst01.Fields("设计点号").Value
SS6 = Rst01.Fields("子线号").Value
SS7 = Rst01.Fields("简化桩号").Value
SS8 = Rst01.Fields("队号").Value
If PH = 1 Then
Sql = "insert into 打印桩号临时表 (队号,列1序号,列1行号,列1线号,列1点号,列1简桩) values " _
& "('" & SS8 & "'," & str(XH) & "," & str(HH) & ",'" & SS1 & "','" & SS3 & "','" & SS7 & "')"
DoCmd.RunSQL Sql
End If
If PH = 2 Then
Sql = "update 打印桩号临时表 as a " _
& "set a.列2序号=" & str(XH) _
& ", a.列2行号=" & str(HH) _
& ", a.列2线号=" & "'" & SS1 & "'" _
& ", a.列2点号=" & "'" & SS3 & "'" _
& ", a.列2简桩=" & "'" & SS7 & "'" _
& " where a.列1行号=" & str(HH)
DoCmd.RunSQL Sql
End If
If PH = 3 Then
Sql = "update 打印桩号临时表 as a " _
& "set a.列3序号=" & str(XH) _
& ", a.列3行号=" & str(HH) _
& ", a.列3线号=" & "'" & SS1 & "'" _
& ", a.列3点号=" & "'" & SS3 & "'" _
& ", a.列3简桩=" & "'" & SS7 & "'" _
& " where a.列1行号=" & str(HH)
DoCmd.RunSQL Sql
End If
Rst01.MoveNext
Me.桩号打印_子窗体.Requery
Loop
Rst01.Close
Me.桩号打印_子窗体.Requery
End Sub