从表中获得每人一个值(“截止日期前每组的最新n个") [英] Obtaining one value per person from table ("latest-n-per-group before cutoff date")

问题描述

在我的数据库中，有一个名为预算"的表，定义如下:

  CREATE TABLE BUDGET(ID INTEGER NOT NULL，THERAPIST INTEGER，CURDATE DATE，ILLENSS SMALLINT，假日SMALLINT);更改表预算添加主键(ID)； 

该表的目的是存储治疗师每月有权休多少假和定期假期.

该表具有以下值

  ID:1，THERAPIST:36，CURDATE:01/01/2012，ILLNESS:8，HOLIDAY:8ID:2，THERAPIST:36，CURDATE:01/01/2012，ILLNESS:8，HOLIDAY:10ID:3，THERAPIST:74，CURDATE:01/01/2012，ILLNESS:8，HOLIDAY:8ID:4，THERAPIST:74，CURDATE:01/03/2012，ILLNESS:8，HOLIDAY:10 

我想编写一个查询，该查询将返回每个治疗师一行并包含最新数据(以上数据显示，治疗师36在2012年1-6月的每个月有8个小时的假期，而从7/2012年，她有权每月获得10个小时的访问时间.换句话说，如果我发布日期为2012年1月31日的查询，则希望返回第1行和第3行；否则，我将返回第1行和第3行.如果我发布查询日期为2012年5月31日，我希望返回第1行和第4行，如果我发布查询日期为31/08/2012，我希望返回第2行和第4行.

以下查询给我每个治疗师一行，但它给了我最大的疾病和休假值，这不一定是我想要的.

 选择治疗师，最高(疾病)，最高(假日)来自预算其中curdate< ='2012-08-31'治疗师分组 

表格中是否应该有第五行

  ID:5，THERAPIST:74，CURDATE:01/07/2012，ILLNESS:6，HOLIDAY:6 

在12年8月31日进行查询将返回疾病= 8和假日= 10，即使它们都应为6.

我尝试了以下查询，但这仅返回一行

 选择治疗师，疾病，假期来自预算其中curdate =(从预算b中选择最高(更新)其中b.治疗师=预算治疗师和b.curdate< ='2012-08-31') 

关于SO也有类似的问题，但是似乎没有一个适合我的情况.当然，如果我可以在没有聚合函数的情况下使用'group by' ，但Firebird(和大多数DBMS)不支持此功能，生活将会更加轻松.

解决方案

如果我正确理解了您的问题，我认为您想要这样做:

 选择b.*从预算b加入 (选择治疗师，max(curdate)作为maxdate从预算在哪里curdate< ='2012-08-31'通过...分组治疗师)分组在grouped.therapist = b.therapist和grouped.maxdate = b.curdate上 

In a database of mine there is a table called 'Budget' defined as follows

 CREATE TABLE BUDGET (
  ID INTEGER NOT NULL,
  THERAPIST INTEGER,
  CURDATE DATE,
  ILLNESS SMALLINT,
  HOLIDAY SMALLINT);

  ALTER TABLE BUDGET ADD PRIMARY KEY (ID);

The purpose of the table is to store how many hours a month a therapist is entitled to sick leave and regular holiday.

The table has the following values

ID: 1, THERAPIST: 36, CURDATE: 01/01/2012, ILLNESS:8, HOLIDAY: 8
ID: 2, THERAPIST: 36, CURDATE: 01/07/2012, ILLNESS:8, HOLIDAY: 10
ID: 3, THERAPIST: 74, CURDATE: 01/01/2012, ILLNESS:8, HOLIDAY: 8
ID: 4, THERAPIST: 74, CURDATE: 01/03/2012, ILLNESS:8, HOLIDAY: 10

I want to write a query which returns one row per therapist, with the most recent data (the above data shows that therapist 36 was entitled to 8 hours holiday per month in the months 1-6/2012, and from 7/2012 she is entitled to 10 hours per month). In other words, if I issue the query for the date 31/01/2012, I expect to get back rows 1 and 3; if I issue the query for the date 31/05/2012, I expect to get back rows 1 and 4 and if I issue the query for the date 31/08/2012, I expect to get back rows 2 and 4.

The following query gives me one row per therapist, but it gives me the maximum values of illness and holiday, which is not necessarily what I want.

select therapist, max (illness), max (holiday)  
from budget
where curdate <= '2012-08-31'
group by therapist

Should there be a fifth row in the table

ID: 5, THERAPIST: 74, CURDATE: 01/07/2012, ILLNESS: 6, HOLIDAY: 6

querying on 31/08/12 would return illness=8 and holiday=10, even though they should both be 6.

I tried the following query but this returns only one row

select therapist, illness, holiday
from budget
where curdate =
(select max (curdate) from budget b
 where b.therapist = budget.therapist
 and b.curdate <= '2012-08-31')

There are similar questions on SO, but none of them seem applicable to my situation. Of course, life would be easier if I could use 'group by' without an aggregate function but Firebird (and most DBMS) doesn't support this.

解决方案

If I understood your problem correctly, I think you want:

select
    b.*
from
    budget b
    join (
        select
            therapist,
            max(curdate) as maxdate
        from
            budget
        where 
            curdate <= '2012-08-31'
        group by
            therapist
    ) grouped on grouped.therapist = b.therapist and grouped.maxdate = b.curdate

这篇关于从表中获得每人一个值(“截止日期前每组的最新n个")的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！