从表中获得每人一个值(“截止日期前每组的最新n个")

以下查询给我每个治疗师一行，但它给了我最大的疾病和休假值，这不一定是我想要的. 选择治疗师，最高(疾病)，最高(假日)来自预算其中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: 8ID: 2, THERAPIST: 36, CURDATE: 01/07/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: 10I 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 budgetwhere curdate <= '2012-08-31'group by therapistShould there be a fifth row in the tableID: 5, THERAPIST: 74, CURDATE: 01/07/2012, ILLNESS: 6, HOLIDAY: 6querying 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 rowselect therapist, illness, holidayfrom budgetwhere 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个")的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！