Question

vb +access 字段不重复值筛选

vb 字段不重复值筛选

表1
id
1
2
1
1
2
3
4
4

想要得到的结果为
id
1
2
3
4
谢谢大家了
我是初学者,能给出一个详细的" SQL = "吗?
谢谢大家了!

Answer 1

select distinct id form 表1
多个字段请用GROUP BY
参考:

Private Sub Command2_Click()
Dim Sql As String
If Option1(0).Value Then '全部符合条件
Sql = "Select 号码,期号 from main where 号码<>null and 时间>=#" & DateAdd("d", -Trim(Combo1.Text), Date) & "# order by 2 desc"
ElseIf Option1(2).Value Then '没有重复的....重复的不可见
Sql = "select 号码,期号 from main where 号码 not in(SELECT 号码 From main where 号码<>null and 时间>=#" & DateAdd("d", -Trim(Combo1.Text), Date) & "# group by 号码 having count(*)>1) and 号码<>null and 时间>=#" & DateAdd("d", -Trim(Combo1.Text), Date) & "# order by 1 desc"
ElseIf Option2.Value Then ''只有重复的数据
Dim Cum As String, i As Integer
For i = 1 To 7
If Option3(i - 1) Then Cum = i + 1
Next
Select Case Cum
Case 2, 3, 4, 5
Cum = "=" & Cum
Case 6
Cum = ">5"
Case 7
Sql = "7"
Case Else
Cum = ">1"
End Select
'MsgBox Cum
If Sql = "7" Then
Sql = "SELECT DISTINCT 号码, Min(期号) AS 期号 FROM main where 号码 in(SELECT 号码 From main where 号码<>null and 时间>=#" & DateAdd("d", -Trim(Combo1.Text), Date) & "# group by 号码 having count(*)>1) and 号码<>null and 时间>=#" & DateAdd("d", -Trim(Combo1.Text), Date) & "# GROUP BY 号码 order by 2 desc"
Else
Sql = "select 号码,期号 from main where 号码 in(SELECT 号码 From main where 号码<>null and 时间>=#" & DateAdd("d", -Trim(Combo1.Text), Date) & "# group by 号码 having count(*)" & Cum & ") and 号码<>null and 时间>=#" & DateAdd("d", -Trim(Combo1.Text), Date) & "# order by 1 desc"
End If
ElseIf Option1(1).Value Then '排除重复..只有一条重复的数据
Sql = "SELECT DISTINCT 号码, Min(期号) AS 期号 FROM main where 号码<>null and 时间>=#" & DateAdd("d", -Trim(Combo1.Text), Date) & "# GROUP BY 号码 order by 2 desc"
End If
ListAdd List2, Sql
AddLog "前 " & Combo1.Text & " 天数据"
End Sub

Answer 2

如果要按ID值进行分别统计,就需要分组统计,采用select …… from 表1 where …… group by ID
如查不统计，相同ID号只需要显示一条记录，则用distinct，如select distinct ID from 表1 where ……

Answer 3

select id form 表1 group by id

Answer 4

select distinct id form 表1 order by id