构造查询以返回子查询结果

Question

SELECT TOP 1 dbo.tbl_Lifting_Gear.e_id, dbo.tbl_Lifitng_Details.det_con, dbo.tbl_contracts.clientID, dbo.tbl_contracts.contractNumber, dbo.tbl_contracts.fin_approved, 
         dbo.tbl_work_locations.work_location, dbo.tbl_contracts.wLocationID 
    FROM     dbo.tbl_Lifitng_Details RIGHT OUTER JOIN 
         dbo.tbl_Lifting_Gear INNER JOIN 
         dbo.tbl_work_locations INNER JOIN 
         dbo.tbl_contracts ON dbo.tbl_work_locations.work_id = dbo.tbl_contracts.wLocationID ON dbo.tbl_Lifting_Gear.con_id = dbo.tbl_contracts.conNo AND 
         dbo.tbl_Lifting_Gear.lifting_loc = dbo.tbl_contracts.conLoc ON dbo.tbl_Lifitng_Details.det_con = dbo.tbl_Lifting_Gear.con_id AND 
         dbo.tbl_Lifitng_Details.det_loc = dbo.tbl_Lifting_Gear.lifting_loc 
    WHERE tbl_lifting_gear.con_id = @con AND tbl_lifting_gear.lifting_loc = @loc 

     (
     SELECT TOP 1 e_id AS defects 
     FROM tbl_Lifting_Gear 
     WHERE tbl_Lifting_Gear.con_id = @con AND tbl_Lifting_Gear.e_defects = 'Y' AND lifting_loc = @loc) 

     (
     SELECT TOP 1 e_id AS addInfo 
     FROM tbl_Lifting_Gear 
     WHERE tbl_Lifting_Gear.con_id = @con AND tbl_Lifting_Gear.e_add = 'Y' AND lifting_loc = @loc) 

     (
     SELECT TOP 1 e_id AS mark 
     FROM tbl_Lifting_Gear 
     WHERE tbl_Lifting_Gear.con_id = @con AND lifting_loc = @loc AND tbl_Lifting_Gear.inspected = 'N') 

     (
     SELECT TOP 1 e_id AS thorough 
     FROM tbl_Lifting_Gear 
     WHERE lifting_through IS NOT NULL AND lifting_through <> 0 AND con_id = @con AND lifting_loc = @loc) 

如何构造此查询，以便子查询的结果作为主查询的一部分返回？ （作为缺陷，addInfo，标记和彻底）。认为我在错误的地方有AS语句，但是当我尝试将它们放在（）中的每个子查询之外时，它会返回语法错误。

Answer 1

子查询只需添加到您的选择列表：

SELECT TOP 1 dbo.tbl_Lifting_Gear.e_id, dbo.tbl_Lifitng_Details.det_con, dbo.tbl_contracts.clientID, dbo.tbl_contracts.contractNumber, dbo.tbl_contracts.fin_approved, 
         dbo.tbl_work_locations.work_location, dbo.tbl_contracts.wLocationID, 
         (
     SELECT TOP 1 e_id AS defects 
     FROM tbl_Lifting_Gear 
     WHERE tbl_Lifting_Gear.con_id = @con AND tbl_Lifting_Gear.e_defects = 'Y' AND lifting_loc = @loc), 
     (
     SELECT TOP 1 e_id AS addInfo 
     FROM tbl_Lifting_Gear 
     WHERE tbl_Lifting_Gear.con_id = @con AND tbl_Lifting_Gear.e_add = 'Y' AND lifting_loc = @loc), 
     (
     SELECT TOP 1 e_id AS mark 
     FROM tbl_Lifting_Gear 
     WHERE tbl_Lifting_Gear.con_id = @con AND lifting_loc = @loc AND tbl_Lifting_Gear.inspected = 'N'), 
     (
     SELECT TOP 1 e_id AS thorough 
     FROM tbl_Lifting_Gear 
     WHERE lifting_through IS NOT NULL AND lifting_through <> 0 AND con_id = @con AND lifting_loc = @loc) 
    FROM     dbo.tbl_Lifitng_Details RIGHT OUTER JOIN 
         dbo.tbl_Lifting_Gear INNER JOIN 
         dbo.tbl_work_locations INNER JOIN 
         dbo.tbl_contracts ON dbo.tbl_work_locations.work_id = dbo.tbl_contracts.wLocationID ON dbo.tbl_Lifting_Gear.con_id = dbo.tbl_contracts.conNo AND 
         dbo.tbl_Lifting_Gear.lifting_loc = dbo.tbl_contracts.conLoc ON dbo.tbl_Lifitng_Details.det_con = dbo.tbl_Lifting_Gear.con_id AND 
         dbo.tbl_Lifitng_Details.det_loc = dbo.tbl_Lifting_Gear.lifting_loc 
    WHERE tbl_lifting_gear.con_id = @con AND tbl_lifting_gear.lifting_loc = @loc