数据查询问题

[iwzw]

一个数据库中有一个记录为date格式数据，比如为“2002/03/16","2003/08/18"等，如何用查询语句快速得到所有的年份（比如2002，2003...等等）？
是否可以用source＝select distinct 日期 from 表1类似的语句实现？

[iwzw]

谢谢各位朋友。你们提供的方法我都试了，为什么查讯不出结果？请看代码：
Dim Cn As New ADODB.Connection
Dim rs As New ADODB.Recordset

Private Sub Form_Load()
Dim Source As String
Dim ActiveConnection As String
Cn.ConnectionString = "uid=admin;pwd=1234;DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & App.Path & "\日记.mdb"
Cn.Open
On Error Resume Next
' Source = "select distinct 录入时间 from 日记"
' Source = "select distinct DatePart('yyyy', 录入时间) from 日记"
' Source = "select distinct LEFT(CAST(录入时间 AS varchar(10)), 4) from 日记"
' Source = "select distinct SUBSTRING（录入时间，1，4）from 日记"
Source = "select distinct year(录入时间) from 日记"
rs.Open Source, Cn, 3, 3
rs.MoveFirst
Do Until rs.EOF '
List1.AddItem IIf(Len(DatePart("yyyy", rs!录入时间)) = 1, 0 & DatePart("yyyy", rs!录入时间), DatePart("yyyy", rs!录入时间))
rs.MoveNext
Loop
rs.Close
End Sub

[leo4587]

我試過了， select distinct year(fieldname) from tablename 沒有錯。

Len(DatePart("yyyy", rs!录入时间)) 為什麼會等於1呢？

錯誤可能不在sql語句喔！！！

[iwzw]

代码摘录有错，应为：List1.AddItem DatePart("yyyy", rs!录入时间)

现在问题是查讯后，提示“在对应所需名称或序数的集合中，未找到项目”，但表中明明有项目的，用“select distinct 录入时间 from 日记”可以证明。

哪位朋友用select distinct year(fieldname) from tablename 查讯通过测试，请贴出代码以便比较一下，谢谢！

[golden24kcn]

SELECT DISTINCT LEFT(CAST(日期列 AS varchar(10)), 4) FROM 表

[daviddivad]

如果是ACCESS
SELECT YEAR（日期） FROM 表1

如果是SQL SERVER
SELECT SUBSTRING（日期，1，4） FROM 表1

[mudai]

select year(列) from 表1

[飘零风]

select distinct convert(char(4),日期,111) from table

这样就可以了吧。Access里边应该也可以这样用。

[lxqlogo0]

SELECT DISTINCT YEAR(日期)
FROM table

[Jackile]

同意楼上！

[Reker熊]

Year(日期字段)

Datepart(yy,日期字段)

[newoldfive]

If Option5.Value = True Then
listbox.FixedRows = 1
MousePointer = vbHourglass
Dim rs5 As New ADODB.Recordset
Dim rs6 As New ADODB.Recordset
Dim rscount As New ADODB.Recordset
Dim countrscount As Integer
Dim countw As Integer
Dim compyear As Integer
str1 = "select year(ctime) from synthesisanalyse group by year(ctime)"
rscount.Open str1, cn, adOpenKeyset, adLockOptimistic, adCmdText
countrscount = 0
Do While Not rscount.EOF
countrscount = countrscount + 1
rscount.MoveNext
Loop

str1 = "select usertype,typename from waterpriceinfo "
rs5.Open str1, cn, adOpenKeyset, adLockOptimistic, adCmdText
On Error GoTo errorbox
countw = 0
Do While Not rs5.EOF
countw = 1 + countw
rs5.MoveNext
Loop
rs5.MoveFirst
Dim fld As ADODB.Field

' Setup the hflxResults

'listbox.Clear
listbox.FixedCols = 0
listbox.FixedRows = 1
listbox.Cols = countw + 1 '加1是年份列
listbox.Rows = countrscount + 1
listbox.Row = 0
listbox.Col = 0

listbox.Text = "年份"
Do While Not rs5.EOF
listbox.Col = listbox.Col + 1
listbox.Row = 0
listbox.Text = rs5.Fields(1)
rs5.MoveNext
Loop
rs5.MoveFirst

str2 = "select year(ctime),typename,sum(watervalue) from waterpriceinfo,synthesisanalyse "
str2 = str2 + " where waterpriceinfo.usertype=synthesisanalyse.usertype group by typename ,year(ctime) "
rs6.Open str2, cn, adOpenKeyset, adLockOptimistic, adCmdText
listbox.Row = 0

If Not rs6.EOF Then '第一行的年直接写
listbox.Row = listbox.Row + 1
listbox.Col = 0
listbox.Text = rs6.Fields(0)
Do While rs6.Fields(1) <> rs5.Fields(1) And Not rs5.EOF
rs5.MoveNext
listbox.Col = listbox.Col + 1
Loop
rs5.MoveFirst
listbox.Col = listbox.Col + 1 '还须后移
If IsNull(rs6.Fields(2)) Then
listbox.Text = ""
Else
listbox.Text = rs6.Fields(2)
End If
compyear = rs6.Fields(0)
rs6.MoveNext
End If
Do While Not rs6.EOF '第一行后的年都要比较
If rs6.Fields(0) <> compyear Then
compyear = rs6.Fields(0)
listbox.Row = listbox.Row + 1
listbox.Col = 0
listbox.Text = rs6.Fields(0)
End If
'If rs6.Fields(1) <> rs5.Fields(1) Then
Do While rs6.Fields(1) <> rs5.Fields(1) And Not rs5.EOF
rs5.MoveNext
listbox.Col = listbox.Col + 1
Loop
' rs5.MoveFirst
listbox.Col = listbox.Col + 1
If IsNull(rs6.Fields(2)) Then
listbox.Text = ""
Else
listbox.Text = rs6.Fields(2)
End If
rs5.MoveFirst
listbox.Col = 0
' Else
' listbox.Text = rs6.Fields(2)
' rs5.MoveFirst
' End If
rs6.MoveNext
Loop

MousePointer = vbDefault
If listbox.Rows = 1 Then
listbox.Rows = 2
End If

End If


这是我在学校时编的，通过运行。
看是否用得着。

[shenen]

Dim Cn As New ADODB.Connection
Dim rs As New ADODB.Recordset

Private Sub Form_Load()
Dim Source As String
Dim ActiveConnection As String
Cn.ConnectionString = "uid=admin;pwd=1234;DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & App.Path & "\日记.mdb"
Cn.Open
On Error Resume Next
' Source = "select distinct 录入时间 from 日记"
' Source = "select distinct DatePart('yyyy', 录入时间) from 日记"
' Source = "select distinct LEFT(CAST(录入时间 AS varchar(10)), 4) from 日记"
' Source = "select distinct SUBSTRING（录入时间，1，4）from 日记"
Source = "select distinct year(录入时间) AS 录入时间 from 日记"
rs.Open Source, Cn, 3, 3
rs.MoveFirst
Do Until rs.EOF '
List1.AddItem IIf(Len(DatePart("yyyy", rs!录入时间)) = 1, 0 & DatePart("yyyy", rs!录入时间), DatePart("yyyy", rs!录入时间))
rs.MoveNext
Loop
rs.Close
End Sub

[shenen]

如果还不行，建议你注释掉On Error Resume Next以查看错误原因

[leo4587]

Option Explicit
Public conn As New ADODB.Connection
Public rs1 As New ADODB.Recordset

Private Sub Form_Load()
Dim connect_str As String
Dim i As Integer
connect_str = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=newman;password=newman;"
connect_str = connect_str & "Initial Catalog=Pubs;Data Source=192.168.11.254;Connect Timeout=30;Application Name=ListenEZ"
conn.open connect_str

If rs1.state = 1 Then rs1.Close
rs1.open "select distinct year(ord_date) as years from sales", conn, 3, 1

For i = 1 To rs1.RecordCount
List1.AddItem rs1("years")
Next i
End Sub

剛寫的一段代碼，已以通過了運行，你可以將數據庫連接改一下就行了。
希望對你有幫助。

[iwzw]

up