SQL根据2个日期选择列值

Question

我有一个很大的select语句，并且从许多其他表和连接构建而来。

我需要另一个包含相同数据但基于2个不同日期的select语句。

目前在以下

Date  | ID |Account|Prod|Location|Cost|Owner | 
10-10-2017 | #73818 | B17H12|M104|EastNY |400 |Jay Bob| 
10-11-2017 | #77618 | B16H14|M104|EastNY |200 |Jay Bob| 
10-11-2017 | #73818 | B17H12|M106|WestNY |300 |Jay Bob| 
10-10-2017 | #75543 | B13H17|M106|WestNY |900 |Jay Bob| 

新语句选择的结果，我需要同比较上面一样，但返回的2个不同的日期，我选择的状态，说第10和第11，因此表将这样

Owner | ID | Account | Date 1 | Prod |Location | Cost | Date 2  | Prod | Location |Cost| 
Jay Bob | #73818 | B17H12 | 10-10-2017 | M104 | EastNY | 400 | 10-11-2017 | M106 | EastNY |300 | 
Jay Bob | #77618 | B16H14 | 10-10-2017 | NULL | NULL | NULL | 10-11-2017 | M104 | EastNY |200 | 
Jay Bob | #75543 | B13H17 | 10-10-2017 | M106 | WestNY | 900 | 10-11-2017 | NULL | NULL  |NULL| 

原来这里是擦洗声明 unit.sample_date有日期和unit.entity_id是ID 我不会需要很多的情况下，这些线路等，但一些我需要的小号我需要的选择是这两个不同日期的列比较的子集。

with temp as (select platform as pltfrm, total_01, total_02, entity_identification.display_name as Platform from hardware 
    LEFT join host_info on hardware.id = host_info.hardware_id 
    left join entity_identification on host_info.entity_id = entity_identification.entity_id 
    where platform= 'Plat01') 
    select unit.sample_date, 
     entity_identification.display_name as hostname, 
     entity_identification.object_id, 
     pim.[Product_Code] as 'PrdCode', 
     pim.ProductName, 
     ha4.attr_value as Region, 
     ha5.attr_value as Plat01_Account, 
     ha1.attr_value as 'Environment', 
     ha2.attr_value as 'Inventory_Code', 
     ha3.attr_value as 'moving_Group', 
     enid01.object_id as 'moving_Group_ID', 
     unit.exist_space_type as 'Current_space_type', 
     unit.recomm_space_type as 'Recommended_space_type', 
     CASE 
     when ha.attr_value IS NULL then 'N/A' else ha.attr_value end as 'Price_Model', 
     unit.exist_cost as 'Current_space_Cost', 
     unit.recomm_cost as 'Recommended_space_Cost', 
     unit.exist_cost - unit.recomm_cost as 'Savings_Month', 
     (unit.exist_cost - unit.recomm_cost)/30 as 'Savings_Daily', 
     CASE 
     when unit.exist_cost > unit.recomm_cost AND ((unit.recomm_space_type not like 'idle%') AND (substring(unit.recomm_space_type, 0, 2) != substring(unit.exist_space_type, 0, 2))) then 'too_much' 
     when unit.exist_cost < unit.recomm_cost AND ((unit.recomm_space_type not like 'idle%') AND (substring(unit.recomm_space_type, 0, 2) != substring(unit.exist_space_type, 0, 2))) then 'too_little' 
     when unit.exist_cost = unit.recomm_cost then 'Just_Right' 
     when substring(unit.recomm_space_type, 0, 2) = substring(unit.exist_space_type, 0, 2) then 'update' 
     when unit.recomm_space_type like 'idle%' then 'remove' end as 'Status_On_Date', 

     v_system_info_hist.total_01 as 'Cur_LT01_Total_T01Low', 
     ((unit.exist_cost)/(v_system_info_hist.total_01)*1000) as 'Cur_Cost_Per_T01', 
     case 
     when temp.total_01 IS NULL then 0 else temp.total_01 end as 'Rec_Total_LT01_T01Low', 
     case 
     when temp.total_01 IS NULL then 0 else ((unit.recomm_cost)/(temp.total_01)*1000) end as 'Rec_Cost_Per_T01', 
     case 
     when temp.total_01 IS NULL then ((unit.exist_cost)/(v_system_info_hist.total_01)*1000) else (((unit.exist_cost)/(v_system_info_hist.total_01)*1000)-((unit.recomm_cost)/(temp.total_01)*1000)) end as 'Savings_per_T01', 
     case 
     when temp.total_02 IS NULL then ((unit.exist_cost)/(v_system_info_hist.total_02)) else ((unit.exist_cost/v_system_info_hist.total_01)*temp.total_01) - (unit.recomm_cost) end as 'LT01_Projected_Total_Potential_Cost_Saving', 
     v_system_info_hist.total_02 as 'Cur_T02_Count', 
     unit.exist_cost/v_system_info_hist.total_02 as 'Cur_Cost_Per_T02', 
     case 
     when temp.total_02 IS NULL then 0 else temp.total_02 end as 'Rec_T02_Count', 
     case 
     when temp.total_02 IS NULL then 0 else ((unit.recomm_cost)/(temp.total_02)) end as 'Rec_Cost_Per_T02', 
     case 
     when temp.total_02 IS NULL then ((unit.exist_cost)/(v_system_info_hist.total_02)) else (((unit.exist_cost)/(v_system_info_hist.total_02))-((unit.recomm_cost)/(temp.total_02))) end as 'Savings_per_T02', 
     case 
     when temp.total_02 IS NULL then ((unit.exist_cost)/(v_system_info_hist.total_02)) else (((unit.exist_cost/v_system_info_hist.total_02)*temp.total_02) - (unit.recomm_cost) ) end as 'T02_Projected_Total_Potential_Cost_Saving', 
     case 
     when ha6.attr_value IS NULL AND unit.exist_cost > unit.recomm_cost AND ((unit.recomm_space_type not like 'idle%') AND (substring(unit.recomm_space_type, 0, 2) != substring(unit.exist_space_type, 0, 2))) then 85 
     when ha6.attr_value IS NULL AND unit.exist_cost < unit.recomm_cost AND ((unit.recomm_space_type not like 'idle%') AND (substring(unit.recomm_space_type, 0, 2) != substring(unit.exist_space_type, 0, 2))) then 80 
     when ha6.attr_value IS NULL AND unit.exist_cost = unit.recomm_cost then 5 
     when ha6.attr_value IS NULL AND substring(unit.recomm_space_type, 0, 2) = substring(unit.exist_space_type, 0, 2) then 88 
     when ha6.attr_value IS NULL AND unit.recomm_space_type like 'idle%' then 90 else ha6.attr_value end as 'Confidence_Score', 
     case 
     when ha7.attr_value IS NULL AND unit.exist_cost > unit.recomm_cost AND ((unit.recomm_space_type not like 'idle%') AND (substring(unit.recomm_space_type, 0, 2) != substring(unit.exist_space_type, 0, 2))) then 3 
     when ha7.attr_value IS NULL AND unit.exist_cost < unit.recomm_cost AND ((unit.recomm_space_type not like 'idle%') AND (substring(unit.recomm_space_type, 0, 2) != substring(unit.exist_space_type, 0, 2))) then 5 
     when ha7.attr_value IS NULL AND unit.exist_cost = unit.recomm_cost then 5 
     when ha7.attr_value IS NULL AND substring(unit.recomm_space_type, 0, 2) = substring(unit.exist_space_type, 0, 2) then 2 
     when ha7.attr_value IS NULL AND unit.recomm_space_type like 'idle%' then 2 else ha7.attr_value end as 'PSFT_Ticket_Sev', 
     pim.SeniorTechOwner, 
     pim.TechOwner, 
     pim.SupportEmail, 
     pim.SeniorProductOwner, 
     pim.PSFT, 
     pim.SlackChannel, 
     v_ig.ig_name as Availability_Zone, 
     pim.[Asset_Id] as 'Asset_Id' 
    FROM [dbo].[unit_u_transfer_hist] unit 
    LEFT JOIN host_attributes on host_attributes.host_name = unit.entity_id and host_attributes.attr_key = 'attr_5' 
    LEFT JOIN host_attributes ha on ha.host_name = unit.entity_id and ha.attr_key = 'attr_10' 
    LEFT JOIN host_attributes ha1 on ha1.host_name = unit.entity_id and ha1.attr_key = 'attr_3' 
    LEFT JOIN host_attributes ha2 on ha2.host_name = unit.entity_id and ha2.attr_key = 'attr_4' 
    LEFT JOIN host_attributes ha3 on ha3.host_name = unit.entity_id and ha3.attr_key = 'attr_9' 
    LEFT JOIN host_attributes ha4 on ha4.host_name = unit.entity_id and ha4.attr_key = 'VE_LOCATION_DATA' 
    LEFT JOIN host_attributes ha5 on ha5.host_name = unit.entity_id and ha5.attr_key = 'VE_LOCATION_DOM' 
    LEFT JOIN host_attributes ha6 on ha6.host_name = unit.entity_id and ha6.attr_key = 'attr_14' 
    LEFT JOIN host_attributes ha7 on ha7.host_name = unit.entity_id and ha7.attr_key = 'attr_15' 
    LEFT JOIN v_system_info_hist on v_system_info_hist.entity_id = unit.entity_id and dbo.v_system_info_hist.sample_date = unit.sample_date 
    LEFT JOIN entity_identification enid01 on v_system_info_hist.parent_entity_id = enid01.entity_id and dbo.v_system_info_hist.sample_date = unit.sample_date 
    LEFT JOIN temp on unit.recomm_space_type like temp.platform+'%' 

    LEFT JOIN v_ig on v_ig.ig_id = unit.ig_id 
    LEFT JOIN [dbo].[ud_tkm_pimm] pim on pim.[Product_Code] = Replace(host_attributes.attr_value, 'PRD00000','PRD') 
    OR pim.[Product_Code] = Replace(host_attributes.attr_value, 'PRD0000','PRD') 
    LEFT JOIN entity_identification on entity_identification.entity_id = unit.entity_id 
where unit.sample_date >= DATEADD(day, -13, GETDATE()) ORDER BY PrdCode desc 

Answer 1

您没有指定数据库，所以我使用了SQL Server语法。您需要重新选择两个日期，然后完全外部联接。

DECLARE @first datetime, @second datetime 
SELECT @first='10/10/2017', @second='10/11/2017' 

SELECT isnull(first.Owner, second.Owner) Owner, 
    isnull(first.ID, second.ID) ID, 
    isnull(first.Account, second.Account) Account, 
    @first [Date 1], 
    first.Prod, 
    first.Location, 
    first.Cost, 
    @second [Date 2], 
    second.Prod, 
    second.Location, 
    second.Cost 
FROM 
(SELECT * FROM Table WHERE [email protected]) first 
FULL OUTER JOIN 
(SELECT * FROM Table WHERE [email protected]) second 
    ON first.Owner=second.Owner 
     and first.ID=second.ID 
     and first.Account=second.Account 

Answer 2

从你的结果集，它看起来像你的两个日期都是基于具有相同ID的2个不同的记录。很难告诉你如何做到这一点，没有看到你从查询表中的细节，但基本的想法是留在ID和使他们不同

即

条件再次加入表

SELECT d.ID, d.Date AS Date1, d2.Date AS Date2 
    FROM IDTABLE d 
    LEFT JOIN IDTABLE d2 
    ON d.ID=d2.ID 
    AND d2.Date<d1.Date