时间戳

首页 > 解决方案 > DATEDIFF 函数返回 2 行

sql - DATEDIFF 函数返回 2 行

问题描述

我有正常工作的查询，我得到以下输出：

9   1116    JOHN    0590056093  9106809105      3   A       NULL    D
9   1117    SARA    0015562451  9203410410      3   A       NULL    D
9   1118    DAVID   5560101753  9375115360      3   B       NULL    D

添加datediff列后，我的输出如下

9   1116    JOHN    0590056093  9106809105      3   A       NULL    D    10
9   1116    JOHN    0590056093  9106809105      3   A       NULL    D    1
9   1117    SARA    0015562451  9203410410      3   A       NULL    D    10
9   1117    SARA    0015562451  9203410410      3   A       NULL    D    1
9   1118    DAVID   5560101753  9375115360      3   B       NULL    D    10
9   1118    DAVID   5560101753  9375115360      3   B       NULL    D    1

列中显示 1 的原因是什么datediff？

询问 ：

select distinct t1.*, fs.Name+' '+ed.Academic as takhasos, Articles.Title, MT.Name as paye,
datediff(m,Projects.StartDateProject, Projects.EndDateProject) as datedif 
from 
(select RC.ID, RCU.UserID, u.Name + ' ' + u.Family AS NameFamily, u.UserName, u.Mobile, u.Email, groupED.IdMaghtae
    from ResearchersCores AS RC LEFT OUTER JOIN
ResearchersCoreUsers AS RCU ON RC.ID = RCU.ResearchersCoreID LEFT OUTER JOIN
Users AS u ON u.Id = RCU.UserID LEFT OUTER JOIN 
(SELECT Eductionals.UserID , Max(Eductionals.MaghtaeID) AS IdMaghtae
    FROM Eductionals
    GROUP BY Eductionals.UserID) groupED
    ON u.Id = groupED.UserID 
WHERE        (RC.IsEnable = 1) AND (RCU.isEnable = 1) AND (RCU.RoleID = 5) ) t1 left outer join
Eductionals as ED ON ED.UserID = t1.UserID AND t1.IdMaghtae = ed.MaghtaeID left outer join
FieldStudies as FS ON ed.FieldStudy_ID = FS.ID left outer join
Articles ON Articles.UserID = t1.UserID left outer join
Projects ON Projects.RecordID = t1.ID and Projects.ControllerID = 8 left outer join
MaghtaeTahsilis MT On MT.ID = t1.IdMaghtae
where t1.id = 9

我想要以下输出

9   1116    JOHN    0590056093  9106809105      3   A       NULL    D    10
9   1117    SARA    0015562451  9203410410      3   A       NULL    D    10
9   1118    DAVID   5560101753  9375115360      3   B       NULL    D    10

标签： sqlsql-servertsqldistinctdatediff

解决方案

您的查询用于distinct删除重复的行。但是您添加的表达式在这些否则重复的行上具有不同的值，因此它们现在显示在结果集中。

一种选择是改用group bydatediff max()。这要求select您在子句中重复该子句中的所有其他列group by：

select 
    t1.col1,
    t1.col2,
    ..., 
    max(datediff(m,Projects.StartDateProject, Projects.EndDateProject)) as datedif 
from ...
where t1.id = 9
group by t1.col1, t1.col2, ...

推荐阅读