如何从sql查询中获取上一个数据行?

z9gpfhce  于 2021-07-26  发布在  Java
关注(0)|答案(1)|浏览(682)

现在,我为get-specific数据定制查询,从该数据中我想获取前一行数据
我尝试了这个查询,但是它出错了

SELECT  Data_date -(SELECT * FROM [x].[dbo].[JSON_SOURCE] WHERE Data_date <= (SELECT TOP(1) Data_Date
  FROM [x].[dbo].[JSON_SOURCE] 
  where ([Actual_cumulative] != null OR LTRIM(RTRIM([Actual_cumulative])) !='') 
  AND ([Early_Variance_monthly] != null OR LTRIM(RTRIM([Early_Variance_monthly])) !='' ) 
  AND ([Early_Variance_cumulative] != null OR LTRIM(RTRIM([Early_Variance_cumulative])) !='' ) 
  AND ([Late_Variance_monthly] != null OR LTRIM(RTRIM([Late_Variance_monthly])) !='' ) 
  AND ([Late_Variance_cumulative] != null OR LTRIM(RTRIM([Late_Variance_cumulative])) !='' ) 
  AND lower([Slug_Type]) IN('lg') ORDER BY Data_Date DESC) AND lower([Slug_Type]) IN('lg'))FROM [x].[dbo].[JSON_SOURCE]

这个查询是选择我想要的数据,从这个查询结果中我想要取以前的数据是不是可行?

SELECT * FROM [x].[dbo].[JSON_SOURCE] WHERE Data_date <= (SELECT TOP(1) Data_Date
  FROM [x].[dbo].[JSON_SOURCE] 
  where ([Actual_cumulative] != null OR LTRIM(RTRIM([Actual_cumulative])) !='') 
  AND ([Early_Variance_monthly] != null OR LTRIM(RTRIM([Early_Variance_monthly])) !='' ) 
  AND ([Early_Variance_cumulative] != null OR LTRIM(RTRIM([Early_Variance_cumulative])) !='' ) 
  AND ([Late_Variance_monthly] != null OR LTRIM(RTRIM([Late_Variance_monthly])) !='' ) 
  AND ([Late_Variance_cumulative] != null OR LTRIM(RTRIM([Late_Variance_cumulative])) !='' ) 
  AND lower([Slug_Type]) IN('lg') ORDER BY Data_Date DESC) AND lower([Slug_Type]) IN('lg')
lfapxunr

lfapxunr1#

因此,您希望将表连接到自身,并在表中选择一个相关行,以便与原始数据放在一起。
要做到这一点,您可以对查询进行排序和分区,以便在每个记录旁边放置一些符合where子句的相关记录,然后使用分区的顺序知道要选择哪一行。
我引入isnull来消除where中的一些复杂性。
要了解这是如何实现的,您可能需要更改一些顺序或>==运算符。

Select *
FROM
(
    SELECT *, ROW_Number() over (partition by a.Id order by isnull(b.Data_Date,'1900-01-01') Desc) as RowOrder
    FROM [x].[dbo].[JSON_SOURCE] a
    left join [x].[dbo].[JSON_SOURCE] b
    ON a.Id != b.Id --Use whatever your Primary Key is to prevent selecting the same row as a secondary row.
    AND a.Data_date <= b.Data_date
    AND a.Slug_Type = b.Slug_Type
    WHERE
    LTRIM(RTRIM(ISNULL(b.[Actual_cumulative],''))) !=''
    AND LTRIM(RTRIM(ISNULL(b.[Early_Variance_monthly],''))) !='' 
    AND LTRIM(RTRIM(ISNULL(b.[Early_Variance_cumulative],''))) !='' 
    AND LTRIM(RTRIM(ISNULL(b.[Late_Variance_monthly],''))) !='' 
    AND LTRIM(RTRIM(ISNULL(b.[Late_Variance_cumulative],''))) !='' 
    AND lower(a.[Slug_Type]) IN('lg')
) cte
WHERE cte.RowOrder = 1

相关问题