时间戳

首页 > 解决方案 > 我如何从多个表中获得所需的结果，如下所示

sql - 我如何从多个表中获得所需的结果，如下所示

问题描述

这是三个表 表：车辆类型 

VehTypID    Name 
1   Car
2   Public Bus
3   Ambulance
4   Trucks
5   Auto

表：服务 

VehTypID    Name 
1   Towing Alert 
1   Over Speed Alert
3   Location 
4   Odo Meter
4   Speed Limit
4   Fuel Limit
4   SOS
4   Emergency Control

表：通知和报告 

VehTypID    Name 
1   Popup
1   Email 
1   SMS
1   WhatsApp
2   Digital Status
4   Email Reports
4   Daily Summary Reports
4   Live Tracking

并且预期结果是预期结果 

Vehicle Types   Services             NotificationAndReports
Car             Towing Alert         Popup
null            Over Speed Alert     Email 
null            null                 SMS
null            null                 WhatsApp
Public Bus      null                 Digital Status
Ambulance       Location             null
Trucks          Odo Meter            Email Reports
null            Speed Limit          Daily Summary Reports
null            Fuel Limit           Live Tracking
null            SOS                  null
null            Emergency Control    null
Auto            null                 null

我使用左连接来获取数据，但没有给出上述结果，而是返回多个值。

标签： sqlleft-join

解决方案

尝试这个：-

CREATE TABLE #VehicleTypes(
VehTypID    BIGINT,
Name NVARCHAR(max))
INSERT INTO #VehicleTypes
SELECT * FROM (
SELECT 1  as bbbb ,'Car'                    as aaaa union all
SELECT 2  as bbbb,'Public Bus'              as aaaa union all
select 3  as bbbb, 'Ambulance'              as aaaa union all
select 4  as bbbb, 'Trucks'             as aaaa union all
select 5  as bbbb, 'Auto'                   as aaaa ) AS a


CREATE TABLE #Services 
(
VehTypID    BIGINT,
Name NVARCHAR(max))

INSERT INTO #Services
SELECT * FROM (
select 1 as bbbb,  'Towing Alert'                       as aaaa    union all 
select 1 as bbbb,  'Over Speed Alert'                   as aaaa    union all
select 3 as bbbb,  'Location'                           as aaaa    union all
select 4 as bbbb,  'Odo Meter'                          as aaaa    union all
select 4 as bbbb,  'Speed Limit'                        as aaaa    union all
select 4 as bbbb,  'Fuel Limit'                     as aaaa    union all
select 4 as bbbb,  'SOS'                                as aaaa    union all
select 4 as bbbb,  'Emergency CONTROL'                  as aaaa    ) AS a

CREATE TABLE #NotificationAndReports
(
VehTypID    BIGINT,
Name NVARCHAR(max))
INSERT INTO #NotificationAndReports
SELECT * FROM (
select 1 as bbbb ,  'Popup'                         as aaaa     union all
select 1 as bbbb ,  'Email'                         as aaaa     union all
select 1 as bbbb ,  'SMS'                           as aaaa     union all
select 1 as bbbb ,  'WhatsApp'                      as aaaa     union all
select 2 as bbbb ,  'Digital Status'                as aaaa     union all
select 4 as bbbb ,  'Email Reports'                 as aaaa     union all
select 4 as bbbb ,  'Daily Summary Reports'         as aaaa     union all
select 4 as bbbb ,  'Live Tracking'                 as aaaa     ) AS a






SELECT  ROW_NUMBER()OVER (ORDER BY ( SELECT 1 )) AS rno,  ROW_NUMBER()OVER (PARTITION BY #VehicleTypes.VehTypID ORDER BY ( SELECT 1 )) AS PrntID,
ROW_NUMBER()OVER (PARTITION BY #Services.Name ORDER BY ( SELECT 1 ))  AS ChildRn,
#VehicleTypes.Name AS  VehicleTypes,#Services.Name AS Services,#NotificationAndReports.Name AS NotificationAndReports
INTO #temp
FROM #VehicleTypes
  RIGHT JOIN #Services
        ON #Services.VehTypID = #VehicleTypes.VehTypID
  LEFT JOIN #NotificationAndReports
        ON #NotificationAndReports.VehTypID = #VehicleTypes.VehTypID 
        ORDER BY #VehicleTypes.VehTypID


SELECT CASE WHEN PrntID = 1 THEN VehicleTypes ELSE NULL END AS VehicleTypes,CASE WHEN ChildRn = 1 THEN Services ELSE NULL END AS Services,NotificationAndReports
 FROM #temp ORDER BY rno


DROP TABLE #NotificationAndReports,#Services,#VehicleTypes,#temp`

推荐阅读