表名:T_WorkLogsData
主要列:ID_u (Guid型)
ApplyDate_dt(时间类型)
WorkLogData_ch(varchar型)
UserID_i(BigInt型)
假如,同一部门的几个人都要在某天参加一项会议,则此表中会有每个人的一条记录,ApplyDate_dt同一天,WorkLogData_ch也为同一件事(例如存"开会"这两个字),此时我要以ApplyDate_dt和WorkLogData_ch为条件查出不重复的记录,即得到的结果中
ID_u,UserID_i是谁的都不要紧,但ApplyDate_dt和WorkLogData_ch不能同时重复,如果同时重复就将其做为一条结果显示
SELECT DISTINCT ApplyDate_dt, WorkLogData_ch FROM T_WorkLogsData虽然能例举出这唯一的记录,但不能包含其他字段(即我要表中的所有字段)
请问我应该怎么写这条SQL语句
11 个解决方案
#1
--如果ID_u,UserID_id的數據沒關係的話
Select Min(ID_u) As ID_u, ApplyDate_dt, WorkLogData_ch, Min(UserID_i) As UserID_i From T_WorkLogsData Group By ApplyDate_dt, WorkLogData_ch
Select Min(ID_u) As ID_u, ApplyDate_dt, WorkLogData_ch, Min(UserID_i) As UserID_i From T_WorkLogsData Group By ApplyDate_dt, WorkLogData_ch
#2
楼上的写法会出问题的,因为ID_u是Guid类型,所以Min(ID_u)会出错,如果是整型值,这个方法就OK了
#3
强
#4
相同的ApplyDate_dt, WorkLogData_ch的情況下,UserID_i會不會重復?
#5
理论上不会
#6
Select
A.*
From
T_WorkLogsData A
Inner Join
(Select ApplyDate_dt, WorkLogData_ch, Min(UserID_i) As UserID_i From T_WorkLogsData Group By ApplyDate_dt, WorkLogData_ch) B
On A.ApplyDate_dt = B.ApplyDate_dt And A.WorkLogData_ch = B.WorkLogData_ch And A.UserID_i = B.UserID_i
A.*
From
T_WorkLogsData A
Inner Join
(Select ApplyDate_dt, WorkLogData_ch, Min(UserID_i) As UserID_i From T_WorkLogsData Group By ApplyDate_dt, WorkLogData_ch) B
On A.ApplyDate_dt = B.ApplyDate_dt And A.WorkLogData_ch = B.WorkLogData_ch And A.UserID_i = B.UserID_i
#7
上面的方法应该OK,我想再问一下,如何我要查询某几个人的这种不重复记录,那么这么改写应该就可以了吧
Select
A.*
From
T_WorkLogsData A
Inner Join
(Select ApplyDate_dt, WorkLogData_ch, Min(UserID_i) As UserID_i From T_WorkLogsData where UserID_i in (1,2,3)Group By ApplyDate_dt, WorkLogData_ch) B
On A.ApplyDate_dt = B.ApplyDate_dt And A.WorkLogData_ch = B.WorkLogData_ch And A.UserID_i = B.UserID_i
UserID_i in (1,2,3)中的几个用户ID是我想要查的
Select
A.*
From
T_WorkLogsData A
Inner Join
(Select ApplyDate_dt, WorkLogData_ch, Min(UserID_i) As UserID_i From T_WorkLogsData where UserID_i in (1,2,3)Group By ApplyDate_dt, WorkLogData_ch) B
On A.ApplyDate_dt = B.ApplyDate_dt And A.WorkLogData_ch = B.WorkLogData_ch And A.UserID_i = B.UserID_i
UserID_i in (1,2,3)中的几个用户ID是我想要查的
#8
不好意思,太懒了,刚试了一下,上边我自以为是的改法不行
#9
因为表里要存不同部门的数据,我只想查找到指定部门下几个人员的这种不重复记录,有办法吗?
#10
你寫的那個語句倒是沒有錯誤。
#11
沒有語法錯誤。
效果沒有達到你的要求?
效果沒有達到你的要求?
#1
--如果ID_u,UserID_id的數據沒關係的話
Select Min(ID_u) As ID_u, ApplyDate_dt, WorkLogData_ch, Min(UserID_i) As UserID_i From T_WorkLogsData Group By ApplyDate_dt, WorkLogData_ch
Select Min(ID_u) As ID_u, ApplyDate_dt, WorkLogData_ch, Min(UserID_i) As UserID_i From T_WorkLogsData Group By ApplyDate_dt, WorkLogData_ch
#2
楼上的写法会出问题的,因为ID_u是Guid类型,所以Min(ID_u)会出错,如果是整型值,这个方法就OK了
#3
强
#4
相同的ApplyDate_dt, WorkLogData_ch的情況下,UserID_i會不會重復?
#5
理论上不会
#6
Select
A.*
From
T_WorkLogsData A
Inner Join
(Select ApplyDate_dt, WorkLogData_ch, Min(UserID_i) As UserID_i From T_WorkLogsData Group By ApplyDate_dt, WorkLogData_ch) B
On A.ApplyDate_dt = B.ApplyDate_dt And A.WorkLogData_ch = B.WorkLogData_ch And A.UserID_i = B.UserID_i
A.*
From
T_WorkLogsData A
Inner Join
(Select ApplyDate_dt, WorkLogData_ch, Min(UserID_i) As UserID_i From T_WorkLogsData Group By ApplyDate_dt, WorkLogData_ch) B
On A.ApplyDate_dt = B.ApplyDate_dt And A.WorkLogData_ch = B.WorkLogData_ch And A.UserID_i = B.UserID_i
#7
上面的方法应该OK,我想再问一下,如何我要查询某几个人的这种不重复记录,那么这么改写应该就可以了吧
Select
A.*
From
T_WorkLogsData A
Inner Join
(Select ApplyDate_dt, WorkLogData_ch, Min(UserID_i) As UserID_i From T_WorkLogsData where UserID_i in (1,2,3)Group By ApplyDate_dt, WorkLogData_ch) B
On A.ApplyDate_dt = B.ApplyDate_dt And A.WorkLogData_ch = B.WorkLogData_ch And A.UserID_i = B.UserID_i
UserID_i in (1,2,3)中的几个用户ID是我想要查的
Select
A.*
From
T_WorkLogsData A
Inner Join
(Select ApplyDate_dt, WorkLogData_ch, Min(UserID_i) As UserID_i From T_WorkLogsData where UserID_i in (1,2,3)Group By ApplyDate_dt, WorkLogData_ch) B
On A.ApplyDate_dt = B.ApplyDate_dt And A.WorkLogData_ch = B.WorkLogData_ch And A.UserID_i = B.UserID_i
UserID_i in (1,2,3)中的几个用户ID是我想要查的
#8
不好意思,太懒了,刚试了一下,上边我自以为是的改法不行
#9
因为表里要存不同部门的数据,我只想查找到指定部门下几个人员的这种不重复记录,有办法吗?
#10
你寫的那個語句倒是沒有錯誤。
#11
沒有語法錯誤。
效果沒有達到你的要求?
效果沒有達到你的要求?