时间戳

首页 > 解决方案 > 如何在主要描述列中包含父描述？

sql - 如何在主要描述列中包含父描述？

问题描述

我有一个 sql 查询：

SELECT
    Object.Description,
    categories.Description AS ParentDescription
FROM ObjectInspectionLog oil
INNER JOIN Object ON oil.ObjectId = Object.ObjectId
INNER JOIN Object as categories ON Object.ParentId = categories.ObjectId
WHERE oil.Notes IS NULL
GROUP BY Object.Description, categories.Description
ORDER BY MIN(Object.ObjectId)

仅供参考：我的实际命名不使用名称对象
ObjectInspectionLog：

ObjectInspectionLogId   JobId   ObjectId    Notes
3,  6669,   15, NULL    
4,  6669,   2,  NULL    
5,  6669,   3,  Rear tires worn 
6,  6669,   4,  NULL    
7,  6669,   5,  NULL    
8,  6669,   14, NULL


Object

ObjectId    ParentId    Description
1,  NULL,   Chassis
2,  1,  Front Tires
3,  1,  Rear Tires
4,  1,  Windshield Condition
5,  1,  Headlights
13, NULL,   Adaptive Equipment
14, 13, Occupied Wheelchair Lift
15, 14, Dual post lift


目前，我返回的内容如下所示：（为简洁起见，删除了一些信息）

Description ParentDescription
Windshield Condition,   Chassis
Headlights, Chassis
Occupied Wheelchair Lift,   Adaptive Equipment
ETC

我的目标是获取包含父描述（机箱、自适应设备等）的列表
我想要返回的是：

Description ParentDescription
Chassis, NULL
Windshield Condition,   Chassis
Headlights, Chassis
Adaptive Equipment, NULL
Occupied Wheelchair Lift,   Adaptive Equipment
ETC

标签： sqlsql-servertsql

解决方案

看起来您想要输出对象的行，无论它们是否被 引用ObjectInspectionLog，例如ObjectId 1应该产生一个输出行。目前尚不清楚您为什么使用group by.

以下代码使用outer连接来更接近您可能想要的：

-- Sample data.
declare @ObjectInsopectionLog as Table ( ObjectInspectionLogId Int, JobId Int, ObjectId Int, Notes VarChar(32) );

insert into @ObjectInsopectionLog ( ObjectInspectionLogId, JobId, ObjectId, Notes ) values
  ( 3,  6669,   15, NULL ),
  ( 4,  6669,   2,  NULL ),
  ( 5,  6669,   3,  'Rear tires worn' ),
  ( 6,  6669,   4,  NULL ),
  ( 7,  6669,   5,  NULL ),
  ( 8,  6669,   14, NULL );

select * from @ObjectInsopectionLog;

declare @Objects as Table ( ObjectId Int, ParentId Int, Description VarChar(32) );

insert into @Objects ( ObjectId, ParentId, Description ) values
  ( 1,  NULL, 'Chassis' ),
  ( 2,  1, 'Front Tires' ),
  ( 3,  1, 'Rear Tires' ),
  ( 4,  1, 'Windshield Condition' ),
  ( 5,  1, 'Headlights' ),
  ( 13, NULL, 'Adaptive Equipment' ),
  ( 14, 13, 'Occupied Wheelchair Lift' ),
  ( 15, 14, 'Dual post lift' );

select * from @Objects;

-- Do the work.
select Child.Description, Parent.Description as ParentDescription, OIL.ObjectId as OIL_ObjectId
  from @ObjectInsopectionLog as OIL full outer join -- Return all rows from either side of this   join .
    @Objects as Child on Child.ObjectId = OIL.ObjectId left outer join -- Return all   Child   rows regardless of   Parent .
    @Objects as Parent on Parent.ObjectId = Child.ParentId
  where OIL.Notes is NULL
  order by Child.ObjectId;

推荐阅读