求同样的产品最新的配置日期

[Weaseatiti]

product
productName createdDate
product1 2016-1-2
product1 2016-3-1
product1 2016-3-2
product1 2016-3-15
product2 2016-2-1

productConfig
productname configname configDate
product1 config1 2015-1-1
product1 config2 2016-1-3
product1 config3 2016-3-5
product2 configm 2016-1-15

求离createdate日期最近的 同样的productname的 config
productName createdDate configname
product1 2016-1-2 config1
product1 2016-3-1 config2
product1 2016-3-2 config2
product1 2016-3-15 config3
product2 2016-2-1 configm

[SRCS000]

select 
 tmp.productName
,tmp.createdDate
,tmp.configname           
,tmp.configDate
(
SELECT  p.productName
,p.createdDate
,pc.configname           
,pc.configDate
, row_number() over(partition by  productName order by datediff(dd,configDate,configname  )  as row

FROM product as p
left join 
productConfig as pc  

on 
p.productName=pc.createdDate
)as tmp

where row=1

[SRCS000]

select 
 tmp.productName
,tmp.createdDate
,tmp.configname           
,tmp.configDate
(
SELECT  p.productName
,p.createdDate
,pc.configname           
,pc.configDate
, row_number() over(partition by  productName order by datediff(dd,pc.configDate,p.createdDate))  as row

FROM product as p
left join 
productConfig as pc  

on 
p.productName=pc.createdDate
)as tmp

where row=1

[二月十六]

--测试数据
if not object_id(N'Tempdb..#product') is null
	drop table #product
Go
Create table #product([productName] nvarchar(28),[createdDate] Date)
Insert #product
select N'product1','2016-1-2' union all
select N'product1','2016-3-1' union all
select N'product1','2016-3-2' union all
select N'product1','2016-3-15' union all
select N'product2','2016-2-1'
GO
if not object_id(N'Tempdb..#productConfig') is null
	drop table #productConfig
Go
Create table #productConfig([productname] nvarchar(28),[configname] nvarchar(27),[configDate] Date)
Insert #productConfig
select N'product1',N'config1','2015-1-1' union all
select N'product1',N'config2','2016-1-3' union all
select N'product1',N'config3','2016-3-5' union all
select N'product2',N'configm','2016-1-15'
Go
--测试数据结束
SELECT t.productName,
       t.createdDate,
       t.configname
FROM
(
    SELECT a.*,
           b.configname,
           ROW_NUMBER() OVER (PARTITION BY a.productName,
                                           a.createdDate
                              ORDER BY ABS(DATEDIFF(DAY, a.createdDate, b.configDate))
                             ) AS rn
    FROM #product a
        JOIN #productConfig b
            ON a.productName = b.productname
) t
WHERE rn = 1;

[Weaseatiti]

斑竹，不对。结果应该是 productName createdDate configname product1 2016-1-2 config1 product1 2016-3-1 config2 product1 2016-3-2 config2 product1 2016-3-15 config3 product2 2016-2-1 configm

[Weaseatiti]

引用 3 楼 sinat_28984567 的回复:

--测试数据
if not object_id(N'Tempdb..#product') is null
	drop table #product
Go
Create table #product([productName] nvarchar(28),[createdDate] Date)
Insert #product
select N'product1','2016-1-2' union all
select N'product1','2016-3-1' union all
select N'product1','2016-3-2' union all
select N'product1','2016-3-15' union all
select N'product2','2016-2-1'
GO
if not object_id(N'Tempdb..#productConfig') is null
	drop table #productConfig
Go
Create table #productConfig([productname] nvarchar(28),[configname] nvarchar(27),[configDate] Date)
Insert #productConfig
select N'product1',N'config1','2015-1-1' union all
select N'product1',N'config2','2016-1-3' union all
select N'product1',N'config3','2016-3-5' union all
select N'product2',N'configm','2016-1-15'
Go
--测试数据结束
SELECT t.productName,
       t.createdDate,
       t.configname
FROM
(
    SELECT a.*,
           b.configname,
           ROW_NUMBER() OVER (PARTITION BY a.productName,
                                           a.createdDate
                              ORDER BY ABS(DATEDIFF(DAY, a.createdDate, b.configDate))
                             ) AS rn
    FROM #product a
        JOIN #productConfig b
            ON a.productName = b.productname
) t
WHERE rn = 1;

结果不对 反了 斑竹

[二月十六]

离 product1 2016-1-2 这条数据最近的不是 product1 config2 2016-1-3 这条数据吗？ 还是要梨它最远的数据？

[Weaseatiti]

productname configname configDate product1 config1 2015-01-01 是这条数据 createdDate的日期比configDate 要靠后 也就是因为产品配置会变来变去的。 求最近的配置日期

[二月十六]

createdDate的日期比configDate 要靠后

这个条件不说清楚，很重要。

--测试数据
if not object_id(N'Tempdb..#product') is null
    drop table #product
Go
Create table #product([productName] nvarchar(28),[createdDate] Date)
Insert #product
select N'product1','2016-1-2' union all
select N'product1','2016-3-1' union all
select N'product1','2016-3-2' union all
select N'product1','2016-3-15' union all
select N'product2','2016-2-1'
GO
if not object_id(N'Tempdb..#productConfig') is null
    drop table #productConfig
Go
Create table #productConfig([productname] nvarchar(28),[configname] nvarchar(27),[configDate] Date)
Insert #productConfig
select N'product1',N'config1','2015-1-1' union all
select N'product1',N'config2','2016-1-3' union all
select N'product1',N'config3','2016-3-5' union all
select N'product2',N'configm','2016-1-15'
Go
--测试数据结束
SELECT t.productName,
       t.createdDate,
       t.configname
FROM
(
    SELECT a.*,
           b.configname,
           ROW_NUMBER() OVER (PARTITION BY a.productName,
                                           a.createdDate
                              ORDER BY ABS(DATEDIFF(DAY, a.createdDate, b.configDate))
                             ) AS rn
    FROM #product a
        JOIN #productConfig b
            ON a.productName = b.productname
			WHERE a.createdDate>b.configDate
) t
WHERE rn = 1;

[shinger126]

;WITH t AS (SELECT *,ROW_NUMBER() OVER (PARTITION BY productname ORDER BY configDate desc) rn FROM productConfig) SELECT * FROM t WHERE rn=1