时间戳

首页 > 解决方案 > 如果海员拥有超过 1 个签证，则返回单行记录 - MS SQL

sql - 如果海员拥有超过 1 个签证，则返回单行记录 - MS SQL

问题描述

我需要帮助。目前，如果海员拥有超过 1 个签证，以下脚本将返回超过 1 行。但是，我需要它返回单排，尽管海员有多少签证。

USE eCrewDos
GO
select distinct case mintab.sex
   when 'M' then 'Mr.'
select distinct case mintab.sex
   when 'M' then 'Mr.'
   when 'F' then 'Ms.'
   else 'Mr.'
   end as  'Title:'
,  mintab.LastName     as  'Last Name:'
,  mintab.MiddleName   as  'Middle Name:'
,  mintab.FirstName    as  'First Name:'
,  mintab.Sex          as  'Gender'
,  convert(varchar(10), mintab.DateOfBirth, 120) as  'Date of birth'
,  mintab.PlaceOfBirth as  'Place of Birth:'
,  countries.CountryName  as 'Nationality:'
,  contacts.MobilePhone1  as 'Mobile Phone 1:'
,  contacts.Email1     as  'Email Address:'
,  pass.PassportNo     as  'Passport:'
,  convert(varchar(10), pass.ValidityDateTo, 120) as  'Passport Expires:'
,  pass.IssuedPlace    as  'Passport - Issue Place:'
,  pass.CountryName    as  'Passport - Issue Country:'
,  visa.DocumentName as 'Visa Name'
,  visa.ReferenceNo as 'Visa Reference No'
,  visa.CountryName as 'Visa - Issue Country'
,  visa.issuedt as 'Visa - Issue Date'
,  visa.expirydt as 'Visa - Expiry Date'
,  lastserv.RankName   as  'Position'
,  airport.AirportCode + ' ' + airport.Name as 'Closest Airport'
,  owners.CompanyName  as  'Company name'
,  vessels.VesselName  as  'Vessel Name'
,  mintab.SeafarerID   as  'Seafarer ID'
from
(select distinct seafarers.SeafarerID
,      seafarers.LastName
,      seafarers.MiddleName
,      seafarers.FirstName
,      seafarers.Sex
,      seafarers.DateOfBirth
,      seafarers.PlaceOfBirth
,      seafarers.CountryID    
,      max(services.StartDate) as startdate
from crewing.seafarers seafarers
     left join crewing.Services services
     on        seafarers.SeafarerID = services.SeafarerID
,    crewing.GeneralCompanies genco
where services.SeafarerID = seafarers.SeafarerID
and   services.IsDeleted = 0
and   seafarers.SeafarerID = services.SeafarerID
and   services.ServiceType in ('C', 'H', 'P')
and   services.StartDate > '2017-01-01'
--and   seafarers.GeneralCompanyID not in (6, 8, 10, 21)
and   seafarers.LastName not like 'Test%'
and   seafarers.StatusID = 3
and   seafarers.SubStatusID in (4, 5)
---- MODIFY THE BELOW DATE TO THE FIRST OF LAST MONTH --------- 
and   seafarers.LastModifiedDate > '2020-08-01'
group by seafarers.SeafarerID
,      seafarers.LastName
,      seafarers.MiddleName
,      seafarers.FirstName
,      seafarers.Sex
,      seafarers.DateOfBirth
,      seafarers.PlaceOfBirth
,      seafarers.CountryID) mintab
left join
    (select serv.SeafarerID
     ,      ranks.RankName
     ,      serv.VesselID
     ,      max(serv.StartDate) as startdate
     from crewing.services serv
     ,    crewing.Ranks    ranks
     where serv.IsDeleted = 0
     and   serv.ServiceType in ('C', 'H', 'P')
     and   serv.RankID = ranks.RankId
     group by serv.SeafarerID
     ,        ranks.RankName
     ,        serv.VesselID) lastserv
        on (lastserv.SeafarerID = mintab.SeafarerID and lastserv.startdate = mintab.startdate)
 left join
    (select docs.SeafarerDocumentID
    ,       docs.SeafarerID 
    ,       coun.CountryName
    ,       passport.IssuedPlace
    ,       passport.PassportNo
    ,       docs.ValidityDateTo
     from crewing.SeafarerDocuments docs
     ,    crewing.SeafarerPassportDocuments passport
     ,    crewing.Countries coun
     where docs.seafarerDocumentID = passport.SeafarerDocumentID
     and   docs.IsDeleted = 0
     and   docs.DocumentCountryID = coun.CountryId) pass
        on mintab.SeafarerID = pass.SeafarerID
 left join 
(select d.DocumentName
,       srd.referenceno 
,       c.countryname
,       c.countrycode
,       convert(varchar(10)
,       sd.ValidityDateFrom,120) issuedt
,       convert(varchar(10),sd.ValidityDateTo,120) expirydt
,       sd.SeafarerID
from crewing.SeafarerDocuments sd
join crewing.documents d on sd.DocumentID = d.documentid
join crewing.seafarerfiles sf on sd.seafarerfileid = sf.seafarerfileid
join crewing.countries c on sd.documentcountryid = c.countryid
join[eCrewDOS].[Crewing].[SeafarerReferenceDocuments] srd on srd.seafarerdocumentid = 
sd.seafarerDocumentID
where d.documenttypeid = 16  and sd.validitydateto > getdate()) visa
on mintab.SeafarerID = visa.SeafarerID

,   crewing.SeafarerContacts contacts
,   crewing.Countries countries
,   crewing.Airport airport
,   crewing.SeafarerDocuments seafdocs
,   crewing.vessels vessels
    left join 
        crewing.Companies owners
        on vessels.OwnerId = owners.CompanyId
    left join
        crewing.Companies managers
        on vessels.managerId = managers.CompanyId
 where mintab.CountryID = countries.CountryId
 and   contacts.AirportId = airport.AirportId
 and   mintab.SeafarerID = seafdocs.SeafarerID
 and   seafdocs.ValidityDateTo > getdate()
 and   seafdocs.DocumentID = 692
 and   seafdocs.IsDeleted = 0
 and   lastserv.VesselID = vessels.VesselId
and   mintab.SeafarerID = contacts.SeafarerId
order by mintab.FirstName 
--order by mintab.LastName, mintab.FirstName

就像是

  1. 海员 A - 签证 1 xxx，签证 2 xxx，签证 3 xxx
  2. 海员 B - 签证 A xxx，签证 2 xxx
  3. 海员 C 签证 A xxx

请帮忙。谢谢

标签： sqlsql-server-2012

解决方案

字符串聚合可用于将多个值聚合到单个列

请参考答案：SQL Server 中的 List Aggreggate

为了

Field A | Field B
1       |  A
1       |  B
2       |  A

询问

select FieldA, string_agg(FieldB, '') as data
from yourtable
group by FieldA

会给出结果

1 | AB
2 | A

对于低于 2017 的版本，请使用

select distinct
SUBSTRING (
 stuff(( select distinct ',' + [FieldB] from tablename order by 1 FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') 
 ,1,0,'' ) 
 ,2,9999)
 from 
    tablename

推荐阅读