使用 Distinct 时仍然得到重复值？
使用SQL Server 2000
如何避免重复值？
查询
SELECT DISTINCT
Modification.dbo.Reference.Dates AS DailyDate,
tmp_Cardevent2.PERSONID,
tmp_Cardevent2.empname,
tmp_cardevent2.cardno,
tmp_Cardevent2.titlecode,
tmp_Cardevent2.titlename,
tmp_Cardevent2.departname,
CASE
WHEN tmp_Cardevent2.CardEventDate = Modification.dbo.Reference.Dates
THEN tmp_Cardevent2.CardEventDate
ELSE '-----'
END AS EMPDATE,
CASE
WHEN tmp_Cardevent2.CardEventDate = Modification.dbo.Reference.Dates
THEN tmp_Cardevent2.Intime
ELSE '-----'
END AS INTIME
WHEN tmp_Cardevent2.CardEventDate = Modification.dbo.Reference.Dates
THEN tmp_Cardevent2.outtime
ELSE '-----'
END AS outtime
FROM tmp_Cardevent2
CROSS JOIN Modification.dbo.Reference
ORDER BY
PERSONID, DAILYDATE DESC
输出：
DailyDates, Personid, empname, cardno, titlecode, titlename, departname, empdate, intime, outtime
12/30/2008 A201 A Cherian 3201 018 Chief Air Traffic Service Assistant Air Traffic Services ----- -----
12/30/2008 A201 A Cherian 3201 018 Chief Air Traffic Service Assistant Air Traffic Services 20081230 07:51:31 15:54:38
12/30/2008 A201 A Cherian 3201 018 Chief Air Traffic Service Assistant Air Traffic Services 20081230 07:51:31 15:54:38
我的查询有什么问题，为什么会得到重复的值？如何避免重复值。
请帮忙解决我的问题。
Using SQL Server 2000
How to avoid the duplicate values?
Query
SELECT DISTINCT
Modification.dbo.Reference.Dates AS DailyDate,
tmp_Cardevent2.PERSONID,
tmp_Cardevent2.empname,
tmp_cardevent2.cardno,
tmp_Cardevent2.titlecode,
tmp_Cardevent2.titlename,
tmp_Cardevent2.departname,
CASE
WHEN tmp_Cardevent2.CardEventDate = Modification.dbo.Reference.Dates
THEN tmp_Cardevent2.CardEventDate
ELSE '-----'
END AS EMPDATE,
CASE
WHEN tmp_Cardevent2.CardEventDate = Modification.dbo.Reference.Dates
THEN tmp_Cardevent2.Intime
ELSE '-----'
END AS INTIME
WHEN tmp_Cardevent2.CardEventDate = Modification.dbo.Reference.Dates
THEN tmp_Cardevent2.outtime
ELSE '-----'
END AS outtime
FROM tmp_Cardevent2
CROSS JOIN Modification.dbo.Reference
ORDER BY
PERSONID, DAILYDATE DESC
Output:
DailyDates, Personid, empname, cardno, titlecode, titlename, departname, empdate, intime, outtime
12/30/2008 A201 A Cherian 3201 018 Chief Air Traffic Service Assistant Air Traffic Services ----- -----
12/30/2008 A201 A Cherian 3201 018 Chief Air Traffic Service Assistant Air Traffic Services 20081230 07:51:31 15:54:38
12/30/2008 A201 A Cherian 3201 018 Chief Air Traffic Service Assistant Air Traffic Services 20081230 07:51:31 15:54:38
What problem in my query, why am getting duplicate values? How to avoid the duplicate values.
Please can any help to solve my problem.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
这些日期是 DateTime 类型还是 Date 类型？如果它们包含时间，这将是它们不相等的原因。当结果被格式化时，时间就被削减了。
尝试仅选择这些值的日期部分。
Are these dates of type DateTime or just Date? If they include a time, this will be the reason why they are not equal. The time is just cut when the result is formatted.
Try to select only the date part of these values.
首先，您给定的结果集：
与给定的查询不匹配，查询中没有“outtime”列：
不知道表模式以及它们如何相互关联，以及列数据类型：
这很难了解重复的真正原因是什么。但是，根据问题中的有限信息，日期和/或时间列之一的格式很可能隐藏了值的实际差异。
first off, your given result set:
does not match your given query, there is no "outtime" column in the query:
Without knowing the table schemas and how they relate to each other, as well as the column datatypes of:
it is hard to understand what is the true cause of the duplicates. However, based on the limited info inthe question, it is most likely that the formatting of one one the date and/or time columns is hiding an actual difference in values.
您可以尝试将另一个不同的选择包装为子查询，例如，
需要 q 并将名称查询作为伪表。
不太确定为什么第一个不同的不起作用，也许 CASE 混淆了它的优化器？
You could try wrapping another select distinct around that as a subquery, e.g.
Where q is required and names query as a pseudo-table.
Not really sure why the first distinct isn't working, perhaps the CASE is confusing its optimizer?