选择值等于给定值或低于给定值且最接近给定值的行。

Question

对于标题不够清晰的问题我感到抱歉。请问，是否可以通过数据库请求来实现？假设我们有以下表格：

   ind_id      name                 value       date
----------- -------------------- ----------- ----------
1           a                    10          2010-01-01
1           a                    20          2010-01-02
1           a                    30          2010-01-03
2           b                    10          2010-01-01
2           b                    20          2010-01-02
2           b                    30          2010-01-03
2           b                    40          2010-01-04
3           c                    10          2010-01-01
3           c                    20          2010-01-02
3           c                    30          2010-01-03
3           c                    40          2010-01-04
3           c                    50          2010-01-05
4           d                    10          2010-01-05

我需要查询所有行以包含给定日期中的每个ind_id一次，如果没有给定日期的ind_id，则取最近较低的日期，如果没有任何较低的日期，则返回带有空值的ind_id+name(名称/ind_id对相等)。 例如，日期是2010-01-04，我期望以下结果:

ind_id      name                 value       date
----------- -------------------- ----------- ----------
1           a                    30          2010-01-03
2           b                    40          2010-01-04
3           c                    40          2010-01-04
4           d                    NULL        NULL

如果可能的话，我将非常感激有人帮助我构建查询。我正在使用SQL Server 2008。

- Timofei Davydik

---

“name/ind_id pairs are equal” - 这是否意味着对于给定的id，名称将始终相同？如果是这样，它代表了DB模式中的非规范化，除非底层数据来自多个表 - 所示的数据来自单个表还是多个表？ - user359040

@Mark Bannister 是的，我知道，这个表是另一个请求的结果 :) - Timofei Davydik

好的，所以这是来自单个表格的数据 - 谢谢。 - user359040

谢谢你们！如果我能接受所有的回答:) - Timofei Davydik

Answer 1

Check this SQL FIDDLE DEMO

with CTE_test
as 
(
    select int_id,
       max(date) MaxDate
    from test 
    where date<='2010-01-04 00:00:00:000'
    group by int_id
)
select A.int_id, A.[Value], A.[Date]
from test A
    inner join CTE_test B
       on a.int_id=b.int_id 
          and a.date = b.Maxdate
union all
select int_id, null, null 
from test 
where int_id not in (select int_id from CTE_test)

- ljh

---

感谢您的深入洞察，是的，之前的答案是错误的。Timofei并没有要求max(value)。我已经更新了答案和相应的演示。 - ljh

Answer 2

（更新）尝试：

with cte as
(select m.*,
        max(date) over (partition by ind_id) max_date,
        max(case when date <= @date then date end) over 
           (partition by ind_id) max_acc_date
 from myTable m)
select ind_id,
       name,
       case when max_acc_date is null then null else value end value, 
       max_acc_date date
from cte c
where date = coalesce(max_acc_date, max_date)

(SQLFiddle here)

- user359040

Answer 3

你可以使用类似以下的内容：

你可以使用类似以下的内容：

declare @date date = '2010-01-04'

;with ids as
(
  select distinct ind_id
  from myTable
)
,ranks as
(
  select *
    , ranking = row_number() over (partition by ind_id order by date desc)
  from myTable
  where date <= @date
)
select ids.ind_id
  , ranks.value
  , ranks.date
from ids
  left join ranks on ids.ind_id = ranks.ind_id and ranks.ranking = 1

SQL Fiddle演示。

理想情况下，您不应该使用DISTINCT语句来获取要包含的ind_id值，但在这种情况下，我已经使用它来获得您所需的结果。

此外，在这些查询中有一个标准的免责声明; 如果您有重复数据，则应考虑在ORDER BY中使用绑定器列或改用RANK而不是ROW_NUMBER。

在OP更新后编辑

只需将新列添加到现有查询中：

with ids as
(
  select distinct ind_id, name
  from myTable
)
,ranks as
(
  select *
    , ranking = row_number() over (partition by ind_id order by date desc)
  from myTable
  where date <= @date
)
select ids.ind_id
  , ids.name
  , ranks.value
  , ranks.date
from ids
  left join ranks on ids.ind_id = ranks.ind_id and ranks.ranking = 1

SQL Fiddle演示。

与前一个例子一样，如果有可用的静态数据表，则最好通过连接来获取ind_id/name信息。

- Ian Preston

Answer 4

这里有一个查询，可以返回你所寻找的结果：

SELECT
    t1.ind_id
,   CASE WHEN t1.date <= '2010-01-04' THEN t1.value ELSE null END
FROM test t1
WHERE t1.date=COALESCE(
    (SELECT MAX(DATE)
    FROM test t2
    WHERE t2.ind_id=t1.ind_id AND t2.date <= '2010-01-04')
,   t1.date)

这个想法是在相关查询中选择一行，使其ID与当前行的ID匹配，并且日期是目标日期'2010-01-04'之前最高的日期。

当不存在这样的行时，将返回当前行的日期。这个日期需要用null替换；这就是顶部的CASE语句所做的。

这里有一个关于sqlfiddle的演示。

- Sergey Kalinichenko

Answer 5

使用EXISTS运算符的选项。

DECLARE @date date = '20100104'
SELECT ind_id,
       CASE WHEN date <= @date THEN value END AS value,
       CASE WHEN date <= @date THEN date END AS date
FROM dbo.test57 t
WHERE EXISTS (
              SELECT 1
              FROM dbo.test57 t2
              WHERE t.ind_id = t2.ind_id AND t2.date <= @date
              HAVING ISNULL(MAX(t2.date), t.date) = t.date
              )

在SQLFiddle上的演示

- Aleksandr Fedorenko

Answer 6

尝试

DECLARE @date DATETIME;

SET @date = '2010-01-04';

WITH temp1 AS
(
SELECT t.ind_id
, t.name
, CASE WHEN t.date <= @date THEN t.value ELSE NULL END AS value
, CASE WHEN t.date <= @date THEN t.date ELSE NULL END AS date
FROM test1 AS t
),

temp AS
(
SELECT *, ROW_NUMBER() OVER (PARTITION BY ind_id ORDER BY t.date DESC) AS rn
FROM temp1 AS t
WHERE t.date <= @date OR t.date IS NULL
)

SELECT *
FROM temp AS t
WHERE rn = 1

- enapi

Answer 7

这不是确切的答案，但会给你概念，因为我很快地写下了它而没有进行任何测试。

use
go 
if
(Select value from table where col=@col1) is not null
--you code to get the match value
else if 
(Select LOWER(Date) from table ) is not null
-- your query to get the nerst dtae record
else
--you query withh null value
end

- I kiet