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

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