vb.net 如何从多条记录中获取每条记录的最小日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14971794/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to get minimum date by each records from multiple records
提问by Juan Filipe
I would like to get the minimum date of each record in my table having multiple entry of date with one primary key. Take a look at my table:
我想获得我的表中每条记录的最小日期，其中有多个日期条目和一个主键。看看我的表：
CaseNo Entry_date
ABC-001 2/12/13
ABC-002 2/09/13
ABC-001 1/01/13
ABC-001 1/31/13
ABC-002 1/01/13
ABC-003 2/01/12
ABC-003 2/18/13
I want to have this result:
我想要这样的结果：
CaseNo Entry_date Min_date
ABC-001 2/12/13 1/01/13
ABC-002 2/09/13 1/09/13
ABC-001 1/01/13 1/01/13
ABC-001 1/31/13 1/01/13
ABC-002 1/09/13 1/09/13
ABC-003 2/01/12 2/01/13
ABC-003 2/18/13 2/01/13
I want to get the minimum date of each CaseNo recorded on my table.
我想获得记录在我的桌子上的每个 CaseNo 的最小日期。
I tried this code:
我试过这个代码：
Select CaseNo,Entry_date, Min(Entry_date) as Min_date
from mytable group by CaseNo
Result is this:
结果是这样的：
CaseNo Entry_date Min_date
ABC-001 1/01/13 1/01/13
ABC-002 1/09/13 1/09/13
ABC-003 2/01/12 2/01/13
The code remove the row not having the minimum date. I want to display all records with their minimum date as Min_date.
该代码删除没有最小日期的行。我想将所有记录的最小日期显示为 Min_date。
回答by pilcrow
Unsure what RDBMS you're using, but two approaches come to mind.
不确定您使用的是什么 RDBMS，但想到了两种方法。
JOIN against derived table
JOIN 对派生表
Create a derived table of (CaseNo, MIN(Entry_date)) rows, and join that against your primary table:
创建 ( CaseNo, MIN(Entry_date)) 行的派生表，并将其连接到主表：
SELECT CaseNo, Entry_date, d.Min_date AS "Min_date"
FROM tbl
INNER JOIN ( SELECT CaseNo, MIN(Entry_date) AS "Min_date"
FROM tbl
GROUP BY 1) d
ON tbl.CaseNo = d.CaseNo;
Use analytic (window) functions
使用分析（窗口）函数
If your RDBMS supports it, you can skip the derived table and ask for your records with the minimum Entry_dateby CaseNoprovided by the system:
如果您的RDBMS支持的话，你可以跳过派生表，并要求与最低的纪录Entry_date由CaseNo系统提供的：
SELECT CaseNo, Entry_date, MIN(Entry_date) OVER (PARTITION BY CaseNo) AS "Min_date"
FROM tbl;
回答by E-r Gabriel Doronila
try this
尝试这个
SELECT
CaseNo,
Entry_date,
(SELECT MIN(Entry_date) FROM Cases subc WHERE subc.CaseNo=c.CaseNo GROUP BY CaseNo) AS MinEntryDate
FROM Cases c
