SQL将子查询中的多行组合到一个字段中(SQL Combine several rows from sub

SQL将子查询中的多行组合到一个字段中(SQL Combine several rows from sub-query into one field)

我正在努力将查询 - 与另一个视图组合成单个查询（或spw）。

第一个查询：

SELECT 'Status: ' + Status + CHAR(10) + 'Crew Information:' + CHAR(10) + 'Instructor: ' + IP + CHAR(10) + 'Student: ' + SP + CHAR(10) + CASE WHEN ST.ACM1 = 'NA' THEN '' ELSE 'ACM1: ' + ST.ACM1 + Char(10) END + CASE WHEN ST.ACM2 = 'NA' THEN '' ELSE 'ACM2: ' + ST.ACM2 + Char(10) END + 'Lesson: ' + Lesson + CHAR(10) + CASE WHEN ST.ScheduleRemarks = '' THEN '' ELSE ' REM:' + ST.ScheduleRemarks + Char(10) END + 'Equipment: ' + EquipmentName + ' (' + Callsign + ')' + CHAR(10) + 'Start: ' + CONVERT(VARCHAR(10), ScheduleTO, 104) + ' - ' + CONVERT(VARCHAR(5), ScheduleTO, 108) + ' UTC' + CHAR(10) + 'Start: ' + CONVERT(VARCHAR(10), ScheduleTD, 104) + ' - ' + CONVERT(VARCHAR(5), ScheduleTD, 108) + ' UTC' + CHAR(10) + CASE WHEN ST.ATC = 'NA' THEN '' ELSE ST.ATC END AS SInfo, IDIP, IDSP, IDA1, IDA2 FROM (SELECT CASE WHEN Schedule.StatusRelease = 1 THEN 'OK' ELSE CASE WHEN Schedule.StatusRequest = 1 THEN 'STBY' ELSE 'N/A' END END AS Status, dbo.Schedule.ContactIDIP, dbo.Schedule.ContactIDSP, dbo.MasterLesson.Lesson, dbo.Equipment.EquipmentName, dbo.CallSignList.Callsign, dbo.Schedule.Meeting, dbo.Schedule.ScheduleRemarks, dbo.Schedule.StatusRelease, dbo.Schedule.StatusRequest, dbo.Schedule.ScheduleDay, dbo.ContactList.FullNameTLC AS IP, ContactList1.FullNameTLC AS SP, CASE WHEN Schedule.ContactIDACM1 = 0 THEN 'NA' ELSE ContactList2.FullNameTLC END AS ACM1, CASE WHEN Schedule.ContactIDACM2 = 0 THEN 'NA' ELSE ContactList3.FullNameTLC END AS ACM2, dbo.Schedule.ScheduleTO, dbo.Schedule.ScheduleTD, CASE WHEN Schedule.RouteID = 0 THEN 'NA' ELSE 'Route: ' + Airport.ICAO + ' - ' + Airport1.ICAO + ' - ' + Airport2.ICAO END AS ATC, dbo.ContactList.Id AS IDIP, ContactList1.Id AS IDSP, ContactList2.Id AS IDA1, ContactList3.Id AS IDA2 FROM dbo.Schedule INNER JOIN dbo.StudentLesson ON dbo.Schedule.ScheduleLessonID = dbo.StudentLesson.StudentLessonID INNER JOIN dbo.MasterLesson ON dbo.StudentLesson.LessonID = dbo.MasterLesson.ID INNER JOIN dbo.Equipment ON dbo.Schedule.EquipmentID = dbo.Equipment.ID LEFT OUTER JOIN dbo.CallSignList ON dbo.CallSignList.ID = dbo.Schedule.CallSignListID LEFT OUTER JOIN dbo.Route ON dbo.Route.ID = dbo.Schedule.RouteID INNER JOIN dbo.ContactList ON dbo.ContactList.Id = dbo.Schedule.ContactIDIP INNER JOIN dbo.ContactList AS ContactList1 ON ContactList1.Id = dbo.Schedule.ContactIDSP LEFT OUTER JOIN dbo.ContactList AS ContactList2 ON dbo.Schedule.ContactIDACM1 = ContactList2.Id LEFT OUTER JOIN dbo.ContactList AS ContactList3 ON dbo.Schedule.ContactIDACM2 = ContactList3.Id LEFT OUTER JOIN dbo.Airport ON dbo.Route.AirportID_Dep = dbo.Airport.ID LEFT OUTER JOIN dbo.Airport AS Airport1 ON dbo.Route.AirportID_Via = Airport1.ID LEFT OUTER JOIN dbo.Airport AS Airport2 ON dbo.Route.AirportID_Arr = Airport2.ID WHERE (dbo.Schedule.StatusRelease = 1) AND (dbo.Schedule.ScheduleDay > GETDATE() - 45) OR (dbo.Schedule.ScheduleDay > GETDATE() - 45) AND (dbo.Schedule.StatusRequest = 1)) AS ST

第二个查询：

SELECT DISTINCT ST.IDIP, substring((Select + CHAR(10) +STn1.SInfo AS [text()] FROM dbo.ST1 STn1 WHERE STn1.IDIP = ST.IDIP ORDER BY STn1.IDIP FOR XML PATH ('')), 2, 1000) [ScheduleInfo] FROM dbo.ST1 ST

我的问题是 - 第一个查询被保存为视图。 所以第二个查询工作正常。

但是 - 如何将它们合并为一个查询？ 当我选择第一个查询作为'STsub'作为子查询

并用STsub替换dbo.ST1 - 我得到一个无效的对象或错误。

我做错了什么？

I am struggling with combining a query - with another view into a single query (or spw).

First query:

SELECT 'Status: ' + Status + CHAR(10) + 'Crew Information:' + CHAR(10) + 'Instructor: ' + IP + CHAR(10) + 'Student: ' + SP + CHAR(10) + CASE WHEN ST.ACM1 = 'NA' THEN '' ELSE 'ACM1: ' + ST.ACM1 + Char(10) END + CASE WHEN ST.ACM2 = 'NA' THEN '' ELSE 'ACM2: ' + ST.ACM2 + Char(10) END + 'Lesson: ' + Lesson + CHAR(10) + CASE WHEN ST.ScheduleRemarks = '' THEN '' ELSE ' REM:' + ST.ScheduleRemarks + Char(10) END + 'Equipment: ' + EquipmentName + ' (' + Callsign + ')' + CHAR(10) + 'Start: ' + CONVERT(VARCHAR(10), ScheduleTO, 104) + ' - ' + CONVERT(VARCHAR(5), ScheduleTO, 108) + ' UTC' + CHAR(10) + 'Start: ' + CONVERT(VARCHAR(10), ScheduleTD, 104) + ' - ' + CONVERT(VARCHAR(5), ScheduleTD, 108) + ' UTC' + CHAR(10) + CASE WHEN ST.ATC = 'NA' THEN '' ELSE ST.ATC END AS SInfo, IDIP, IDSP, IDA1, IDA2 FROM (SELECT CASE WHEN Schedule.StatusRelease = 1 THEN 'OK' ELSE CASE WHEN Schedule.StatusRequest = 1 THEN 'STBY' ELSE 'N/A' END END AS Status, dbo.Schedule.ContactIDIP, dbo.Schedule.ContactIDSP, dbo.MasterLesson.Lesson, dbo.Equipment.EquipmentName, dbo.CallSignList.Callsign, dbo.Schedule.Meeting, dbo.Schedule.ScheduleRemarks, dbo.Schedule.StatusRelease, dbo.Schedule.StatusRequest, dbo.Schedule.ScheduleDay, dbo.ContactList.FullNameTLC AS IP, ContactList1.FullNameTLC AS SP, CASE WHEN Schedule.ContactIDACM1 = 0 THEN 'NA' ELSE ContactList2.FullNameTLC END AS ACM1, CASE WHEN Schedule.ContactIDACM2 = 0 THEN 'NA' ELSE ContactList3.FullNameTLC END AS ACM2, dbo.Schedule.ScheduleTO, dbo.Schedule.ScheduleTD, CASE WHEN Schedule.RouteID = 0 THEN 'NA' ELSE 'Route: ' + Airport.ICAO + ' - ' + Airport1.ICAO + ' - ' + Airport2.ICAO END AS ATC, dbo.ContactList.Id AS IDIP, ContactList1.Id AS IDSP, ContactList2.Id AS IDA1, ContactList3.Id AS IDA2 FROM dbo.Schedule INNER JOIN dbo.StudentLesson ON dbo.Schedule.ScheduleLessonID = dbo.StudentLesson.StudentLessonID INNER JOIN dbo.MasterLesson ON dbo.StudentLesson.LessonID = dbo.MasterLesson.ID INNER JOIN dbo.Equipment ON dbo.Schedule.EquipmentID = dbo.Equipment.ID LEFT OUTER JOIN dbo.CallSignList ON dbo.CallSignList.ID = dbo.Schedule.CallSignListID LEFT OUTER JOIN dbo.Route ON dbo.Route.ID = dbo.Schedule.RouteID INNER JOIN dbo.ContactList ON dbo.ContactList.Id = dbo.Schedule.ContactIDIP INNER JOIN dbo.ContactList AS ContactList1 ON ContactList1.Id = dbo.Schedule.ContactIDSP LEFT OUTER JOIN dbo.ContactList AS ContactList2 ON dbo.Schedule.ContactIDACM1 = ContactList2.Id LEFT OUTER JOIN dbo.ContactList AS ContactList3 ON dbo.Schedule.ContactIDACM2 = ContactList3.Id LEFT OUTER JOIN dbo.Airport ON dbo.Route.AirportID_Dep = dbo.Airport.ID LEFT OUTER JOIN dbo.Airport AS Airport1 ON dbo.Route.AirportID_Via = Airport1.ID LEFT OUTER JOIN dbo.Airport AS Airport2 ON dbo.Route.AirportID_Arr = Airport2.ID WHERE (dbo.Schedule.StatusRelease = 1) AND (dbo.Schedule.ScheduleDay > GETDATE() - 45) OR (dbo.Schedule.ScheduleDay > GETDATE() - 45) AND (dbo.Schedule.StatusRequest = 1)) AS ST

Second query:

SELECT DISTINCT ST.IDIP, substring((Select + CHAR(10) +STn1.SInfo AS [text()] FROM dbo.ST1 STn1 WHERE STn1.IDIP = ST.IDIP ORDER BY STn1.IDIP FOR XML PATH ('')), 2, 1000) [ScheduleInfo] FROM dbo.ST1 ST

My problem is - the first query is saved as a view. So the second query works just fine.

But - how can I merge them into a single query? when I select the first query as 'STsub' as a sub-query

and replace dbo.ST1 with STsub - I get an invalid object or errors.

What I am doing wrong?

最满意答案

我会尝试使用公用表表达式：

;WITH STsub as ( ... First Query ... ) SELECT DISTINCT ... FROM STsub

此外，查询可能会写得更优化一点。 目前，它是使用公共IDIP自行连接所有行，每行调用一次初始查询。 如果您计划拥有大量数据，则无法扩展。

I would try using a common table expression:

;WITH STsub as ( ... First Query ... ) SELECT DISTINCT ... FROM STsub

Also, the query could maybe be written a bit more optimized. Currently it is self joining all of the rows with a common IDIP calling the initial query once per row. This will not scale if you plan on having a lot of data.