时间戳

首页 > 解决方案 > 在多个where条件中选择具有最大值的行

sql-server - 在多个where条件中选择具有最大值的行

问题描述

我根据此页面编辑了以下查询：

选择具有 MAX 值的记录

选择查询：

select 
    Users.Id, Users.[Name], Users.Family, Users.BirthDate,
    Users.Mobile, Users.[Description], Users.Email,
    Users.UserName, Users.fatherName,
    Users.archiveNumber, Users.[Address], Users.IsMarried,
    Users.Mazhab, 
    Cities.CityName, Religions.PersianName, Users.Date_insert, 
    Users.ImageName,
    MaghtaeTahsilis.[Name] as MaghtaeTahsilisName,
    FieldStudies.[Name] as FieldStudiesName,
    Eductionals.Institute, Eductionals.Moaddal, 
    Eductionals.FromYear, Eductionals.ToYear
from 
    Users 
left outer join 
    Eductionals on Users.id = Eductionals.UserID
left outer join 
    MaghtaeTahsilis on Eductionals.MaghtaeID = MaghtaeTahsilis.ID
left outer join 
    Cities on Users.City_Id = Cities.Id
left outer join 
    Religions on Users.Relegion_ID = Religions.ID
left outer join 
    FieldStudies on Eductionals.FieldStudy_ID = FieldStudies.ID
where 
    Users.UserName = @code_melli 
    and Eductionals.MaghtaeID = (select MAX(MaghtaeID) from Eductionals 
                                 where Eductionals.UserID = Users.Id)

此命令在选择MAX值时正常工作，但如果以下语句有NULL值，则不返回任何行。NULL如果是的话，我想显示价值NULL。

标签： sql-servertsqlselect

解决方案

你left outer join的 s 被条件变成了inner joins where。您的查询应如下所示：

select u.Id, u.[Name], u.Family, u.BirthDate, u.Mobile, u.[Description], u.Email, u.UserName, u.fatherName,
       u.archiveNumber, u.[Address], u.IsMarried, u.Mazhab, c.CityName, r.PersianName, u.Date_insert, u.ImageName,
       mt.[Name] As MaghtaeTahsilisName, fs.[Name] As FieldStudiesName, e.Institute, e.Moaddal, e.FromYear, e.ToYear
from Users u left outer join
     Eductionals e
     on u.id = e.UserID and
        e.MaghtaeID = (select MAX(e2.MaghtaeID)
                       from Eductionals e2
                       where e2.UserID = u.Id
                      ) left outer join
     MaghtaeTahsilis mt
     on e.MaghtaeID = mt.ID left outer join
     Cities c
     on u.City_Id = c.Id left outer join
     Religions r
     on u.Relegion_ID = r.ID left outer join
     FieldStudies fs
     on e.FieldStudy_ID = fs.ID
where u.UserName = @code_melli ;

第一个表的条件——在left joins 链中应该在where子句中。在on子句中的后续表上。

您会注意到我还添加了表别名，因此查询更易于编写和阅读。

您还可以使用窗口函数：

from Users u left outer join
     (select e2.*,
             row_number() over (partition by e2.userId order by e2.MaghtaeID desc) as seqnum
      from Eductionals e2
     ) e
     on u.id = e.UserID and
        e.seqnum = 1 left outer join
     . . .

推荐阅读