如何根据日期范围

问题描述：

我有2个表加入这些表：Saleorder和材料在底部，如何根据日期范围

表Saleorder：

Saleorder Datecreated Material 
A    2016-01-01  M1 
B    2016-01-21  M1 
C    2016-03-01  M1

和材料：

Material Changedon Materialresponsible 
M1   2016-01-01  E 
M1   2016-01-20  F 
M1   2016-02-26  G

现在我想连接表格Saleorder与表材料基于材料和Datecreated和changeondate，我想这样的结果：

Saleorder Datecreated Material materialresponsible 
A    2016-01-01  M1    E 
B    2016-01-21  M1    F 
C    2016-03-01  M1    G

为dateCreated会和Changedon日之间的关系的逻辑是：

按订单一个是在2016年1月1日创建的，在这一天的metarial M1与materialresponsible E组有效。

按订单B在上21.01创建，但metarial M1具有改变on20.01，所以Materialresponsible F为有效

阶下于01.03创建，但材料M1已经改变了26.02，所以材料负责G是有效的

你能帮我怎么做吗？

感谢

CREATE TABLE [dbo].[Saleorder1](
    [Saleorder] nvarchar (20) NULL, 
    [Datecreated] date NULL, 
    [Material] nvarchar (20) NULL, ) 
INSERT INTO Saleorder1 (Saleorder,Datecreated,Material) 
VALUES ('A','2016-01-01','M1'), 
('B','2016.01.21','M1'), 
('C','2016.03.01','M1') 


    CREATE TABLE [dbo].[Material2](
    [Material] nvarchar(20) NULL, 
    [Changedon] date NULL, 
    [Materialresponsible] nvarchar(20) NULL,) 
INSERT INTO Material2 
VALUES ('M1','2016-01-01','E'), 
('M1','2016-01-20','F'), 
('M1','2016-02-26','G')

为什么MySQL标记？ – Strawberry

哪个DBMS？ MySQL或SQL Server？不可能都是......你到目前为止尝试过什么？例如，有多个条件的连接？ – ADyson

是否有销售创建的数据和材料更改数据的任何标准。 – Wintergreen

答

如果您正在使用SQL Server 2012+，您可以使用LEAD函数来获得上述结果

模式：

CREATE TABLE #Saleorder1(
[Saleorder] nvarchar (20) NULL, 
[Datecreated] date NULL, 
[Material] nvarchar (20) NULL, ) 


INSERT INTO #Saleorder1 (Saleorder,Datecreated,Material) 
VALUES ('A','2016-01-01','M1'), 
('B','2016.01.21','M1'), 
('C','2016.03.01','M1') 


CREATE TABLE #Material2(
[Material] nvarchar(20) NULL, 
[Changedon] date NULL, 
[Materialresponsible] nvarchar(20) NULL,) 

INSERT INTO #Material2 
VALUES ('M1','2016-01-01','E'), 
('M1','2016-01-20','F'), 
('M1','2016-02-26','G')

现在做用LEAD功能选择如下

SELECT S.Saleorder, S.Datecreated, M.Material,M.Materialresponsible 
FROM #Saleorder1 S 
INNER JOIN (

SELECT Material ,Changedon ,Materialresponsible 
,ISNULL(LEAD(Changedon) OVER(ORDER BY (SELECT 1)),GETDATE()) Changedon_To_Dte 
FROM #Material2 

)M ON S.Material = M.Material 
AND S.Datecreated BETWEEN M.Changedon AND M.Changedon_To_Dte

而其结果将是

+-----------+-------------+----------+---------------------+ 
| Saleorder | Datecreated | Material | Materialresponsible | 
+-----------+-------------+----------+---------------------+ 
| A   | 2016-01-01 | M1  | E     | 
| B   | 2016-01-21 | M1  | F     | 
| C   | 2016-03-01 | M1  | G     | 
+-----------+-------------+----------+---------------------+

，如果你使用SQL Server的下版本。

--CTE for SNO Generation 
;WITH CTE AS(
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 1)) SNO 
,Material,Changedon,Materialresponsible 
FROM #Material2 
) 

--CTE2 for generating Changedon_To_Dte by SELF Joining 
,CTE2 AS (
SELECT C1.Material,C1.Changedon,C1.Materialresponsible 
, ISNULL(C2.Changedon, GETDATE()) AS Changedon_To_Dte 
FROM CTE C1 
LEFT JOIN CTE C2 ON C1.SNO+1 = C2.SNO 
) 

SELECT S.Saleorder, S.Datecreated, C2.Material,C2.Materialresponsible 
FROM #Saleorder1 S 
INNER JOIN CTE2 C2 ON S.Material = C2.Material 
AND S.Datecreated BETWEEN C2.Changedon AND C2.Changedon_To_Dte

如何根据日期范围

相关推荐