如何从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')

sql sql-server Database where-clause select

来源：https://stackoverflow.com/questions/62243935/how-get-previous-data-row-from-sql-query

1条答案

按热度按时间

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

赞(0）回复(0）举报 2021-07-26

我来回答

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

1条答案

相关问题

热门标签

最新问答