SQL Server XML路径未在SQL连接表中生成串联结果

dpiehjr4  于 2022-12-26  发布在  其他
关注(0)|答案(1)|浏览(111)

我正在尝试制作一个表格,显示所有入住医院的患者。我可以很好地连接客户、患者、入住、预约数据,但警报表有多行,我尝试聚合/连接/汇总。我尝试创建XML语句,但似乎不起作用。我希望患者的所有警报都是一行中的单个逗号分隔字符串。以下是我所拥有:

select DISTINCT
    a.ResourceAbbreviation1, a.AppointmentType, a.StatusNum, c.sLastName,
    pt.Name, pt.WeightString, pt.AgeShort, pt.Breed, pt.Species, pt.Gender, pt.NewPatient,
    (select SUBSTRING((
        select ',' + al.stext AS 'data()'
        FOR XML PATH('')
        ), 2, 9999) as cautions),
    pt.Classification, p.kPatientId
from dbo.entpatients pt
join alerts al 
    on al.kpatientid = pt.IDPatient
join dbo.PatientCheckIns P
    on pt.IDPatient=p.kPatientId
join dbo.EntAppointments a 
    on a.IDPatient = p.kPatientId
join dbo.clients c 
    on c.kID=a.IDClient
where cast (a.StartTime as date) = cast(getdate() as date)
and a.StatusNum=4;
eyh26e7m

eyh26e7m1#

您需要将alerts表引用作为FROM移动到subselect中。
我还建议使用AS text()而不是AS data()(或者完全省略)来避免不需要的空格,使用STUFF()而不是SUBSTRING()来去掉前导逗号,额外的嵌套SELECT也是不需要的。

select DISTINCT
    a.ResourceAbbreviation1, a.AppointmentType, a.StatusNum, c.sLastName,
    pt.Name, pt.WeightString, pt.AgeShort, pt.Breed, pt.Species, pt.Gender, pt.NewPatient,
    STUFF((
        select ',' + al.stext AS 'text()'
        from alerts al 
        where al.kpatientid = pt.IDPatient
        FOR XML PATH('')
        ), 1, 1, '') as cautions,
    pt.Classification,
    p.kPatientId
from dbo.entpatients pt
join dbo.PatientCheckIns P
    on pt.IDPatient=p.kPatientId
join dbo.EntAppointments a 
    on a.IDPatient = p.kPatientId
join dbo.clients c 
    on c.kID=a.IDClient
where cast (a.StartTime as date) = cast(getdate() as date)
and a.StatusNum=4;

如果您的警报文本可能包含特殊的XML字符(如〈、〉或&),而这些字符可能会被编码,那么我建议您使用一个稍微修改过的表单,使用.value()函数来提取连接的文本。

STUFF((
        select ',' + al.stext AS 'text()'
        from alerts al 
        where al.kpatientid = pt.IDPatient
        FOR XML PATH(''), TYPE
        ).value('text()[1]','nvarchar(max)'), 1, 1, '') as cautions,

这样可以避免在结果中看到<>&之类的编码。
如果您使用的是SQL Server 2017或更高版本,您还可以切换到相对较新的STRING_AGG()函数。

(
        select STRING_AGG(',', al.stext)
        from alerts al 
        where al.kpatientid = pt.IDPatient
    ) as cautions,

我还要回顾一下您对DISTINCT的需求。在某些情况下,当您有意希望查询返回希望消除的重复行时,使用DISTICT是合适的。例如,如果您知道同一个患者可能多次访问相同的选定数据,则DISTICT可能是合适的。但是,如果您不知道原因而将其放入以消除重复,这可能是欠约束连接或其他逻辑问题的迹象,需要进一步检查。

相关问题