sql三表查询问题，数据结构如下，要求查询结果如图

USE [PASSTEST]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[patientinfo](
    [patientid] [varchar](50) NULL,
    [patientname] [varchar](50) NULL,
    [birthdate] [varchar](20) NULL,
    [startdate] [varchar](20) NULL,
    [enddate] [varchar](20) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[patientinfo] ([patientid], [patientname], [birthdate], [startdate], [enddate]) VALUES (N'28406504', N'蒋跃波', N'1993-06-19', N'2020-06-01', N'2020-07-20')
INSERT [dbo].[patientinfo] ([patientid], [patientname], [birthdate], [startdate], [enddate]) VALUES (N'28406083', N'李亿鑫', N'1988-01-09', N'2020-06-01', N'2020-06-21')
INSERT [dbo].[patientinfo] ([patientid], [patientname], [birthdate], [startdate], [enddate]) VALUES (N'28407632', N'吴尔曼', N'1991-07-10', N'2020-06-01', N'2020-07-10')

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[drugdict](
    [drugcode] [varchar](50) NULL,
    [drugname] [varchar](50) NULL,
    [is_anti] [int] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[drugdict] ([drugcode], [drugname], [is_anti]) VALUES (N'XY31204', N'注射用头孢呋辛钠', 1)
INSERT [dbo].[drugdict] ([drugcode], [drugname], [is_anti]) VALUES (N'XY31203', N'注射用替加环素', 1)
INSERT [dbo].[drugdict] ([drugcode], [drugname], [is_anti]) VALUES (N'XY31188', N'注射用头孢孟多酯钠', 1)
INSERT [dbo].[drugdict] ([drugcode], [drugname], [is_anti]) VALUES (N'XY31181', N'替硝唑片', 0)
INSERT [dbo].[drugdict] ([drugcode], [drugname], [is_anti]) VALUES (N'XY31131', N'注射用脂溶性维生素', 0)
INSERT [dbo].[drugdict] ([drugcode], [drugname], [is_anti]) VALUES (N'XY31151', N'金银花软胶囊', 0)

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[costinfo](
    [patientid] [varchar](50) NULL,
    [drugcode] [varchar](50) NULL,
    [drugcost] [varchar](10) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[costinfo] ([patientid], [drugcode], [drugcost]) VALUES (N'28406504', N'XY00352', N'48.0000')
INSERT [dbo].[costinfo] ([patientid], [drugcode], [drugcost]) VALUES (N'28406504', N'XY00865', N'43.1200')
INSERT [dbo].[costinfo] ([patientid], [drugcode], [drugcost]) VALUES (N'28406504', N'XY30127', N'32.4200')
INSERT [dbo].[costinfo] ([patientid], [drugcode], [drugcost]) VALUES (N'28406504', N'XY30909', N'61.7600')
INSERT [dbo].[costinfo] ([patientid], [drugcode], [drugcost]) VALUES (N'28406083', N'XY30278', N'120.0000')
INSERT [dbo].[costinfo] ([patientid], [drugcode], [drugcost]) VALUES (N'28406083', N'XY31188', N'59.4200')
INSERT [dbo].[costinfo] ([patientid], [drugcode], [drugcost]) VALUES (N'28406083', N'XY31203', N'37.7600')
INSERT [dbo].[costinfo] ([patientid], [drugcode], [drugcost]) VALUES (N'28406083', N'XY31203', N'37.7600')
INSERT [dbo].[costinfo] ([patientid], [drugcode], [drugcost]) VALUES (N'28406083', N'XY31151', N'15.3600')
INSERT [dbo].[costinfo] ([patientid], [drugcode], [drugcost]) VALUES (N'28406083', N'CY30777', N'32.4200')
INSERT [dbo].[costinfo] ([patientid], [drugcode], [drugcost]) VALUES (N'28407632', N'XY31131', N'28.4300')
INSERT [dbo].[costinfo] ([patientid], [drugcode], [drugcost]) VALUES (N'28407632', N'XY31131', N'28.4300')
INSERT [dbo].[costinfo] ([patientid], [drugcode], [drugcost]) VALUES (N'28407632', N'XY31203', N'37.7600')
INSERT [dbo].[costinfo] ([patientid], [drugcode], [drugcost]) VALUES (N'28407632', N'XY31188', N'59.4200')

要求查询结果

我自己写的不太对，所以请大佬帮帮忙

--应该比较清晰，日期和合并字符串的格式你处理一下就可以了
select a.patientname,
max(datediff(now,birthdate))/365 年龄,
max(datediff(enddata,startdate)) 住院天数,
concat_ws(',',drugname) 药物名称,
sum(drugcost) 药品总金额,
sum(case when is_anti=1 then drugcost else 0 end) 抗菌药品总金额,
sum(case when is_anti=1 then drugcost else 0 end)/sum(drugcost) 占比
from dbo.patientinfo a 
join dbo.costinfo b 
on a.patientid=b.patientid
join dbo.drugdict c 
on b.drugcode=c.drugcode
group by a.patientname

;with T1 as
(select b.patientid,b.drugcode,b.drugcost,c.drugname,c.is_anti from dbo.costinfo b
left join dbo.drugdict c on c.drugcode=b.drugcode),
T2 as
(
select patientid,drugnames=
stuff((select ','+drugname from T1 as b
     where b.patientid=a.patientid   
     for xml path('')), 1, 1, ''),
sum(cast(a.drugcost as float)) as SumPrice,
sum(case when is_anti=1 then cast(a.drugcost as float) else 0 end) as Price1
from  T1 as a
group by patientid
)

select a.patientname,
datediff(YEAR,a.birthdate,getdate()) 年龄,
datediff(DAY,a.startdate,a.enddate)+1 住院天数,
b.drugnames 药物名称,
b.SumPrice 药品总金额,b.Price1 抗菌药物总金额,b.Price1*100/b.SumPrice 抗菌药物金额占比
from dbo.patientinfo a 
left join T2 b on a.patientid=b.patientid

;with T1 as
(select b.patientid,b.drugcode,b.drugcost,c.drugname,c.is_anti from dbo.costinfo b
left join dbo.drugdict c on c.drugcode=b.drugcode),
T2 as
(
select patientid,drugnames=
stuff((select ','+drugname from T1 as b
     where b.patientid=a.patientid and b.is_anti=1
     for xml path('')), 1, 1, ''),
sum(cast(a.drugcost as float)) as SumPrice,
sum(case when is_anti=1 then cast(a.drugcost as float) else 0 end) as Price1
from  T1 as a
group by patientid
)

select a.patientname as 患者姓名,
datediff(YEAR,a.birthdate,getdate()) 年龄,
datediff(DAY,a.startdate,a.enddate)+1 住院天数,
b.drugnames 抗菌药物名称,
b.SumPrice 药品总金额,b.Price1 抗菌药物总金额,b.Price1*100/b.SumPrice 抗菌药物金额占比
from dbo.patientinfo a 
left join T2 b on a.patientid=b.patientid

---加个占比小数位

;with T1 as
(select b.patientid,b.drugcode,b.drugcost,c.drugname,c.is_anti from dbo.costinfo b
left join dbo.drugdict c on c.drugcode=b.drugcode),
T2 as
(
select patientid,drugnames=
stuff((select ','+drugname from T1 as b
     where b.patientid=a.patientid and b.is_anti=1
     for xml path('')), 1, 1, ''),
sum(cast(a.drugcost as float)) as SumPrice,
sum(case when is_anti=1 then cast(a.drugcost as float) else 0 end) as Price1
from  T1 as a
group by patientid
)

select a.patientname as 患者姓名,
datediff(YEAR,a.birthdate,getdate()) 年龄,
datediff(DAY,a.startdate,a.enddate)+1 住院天数,
b.drugnames 抗菌药物名称,
b.SumPrice 药品总金额,b.Price1 抗菌药物总金额,round(b.Price1*100/b.SumPrice,2) 抗菌药物金额占比
from dbo.patientinfo a 
left join T2 b on a.patientid=b.patientid