如何使用SQL查询从多个表中获取最早的日期? [英] How to use SQL query to get earliest Date from multiple Tables?
问题描述
如何使用SQL查询从多个表中获取最早的日期?
How to use SQL query to get earliest Date from multiple Tables?
摘要(2016年1月10日修订): 我需要3个表(A，B，C)的信息，TableC没有主键，因此我将为每个[B_ID]获得多个[C_Date]记录.
Summary (Revised on 10 Jan 2016): I need info from 3 Tables (A, B, C), TableC has no primary key, hence I will get multiple [C_Date] record for each [B_ID].
' Description of Database:
' A Ms Access database file: "c:\DB\Data.mdb"
' It has 3 Tables: [TableA], [TableB], [TableC]
' TableA with Fields [A_ID], [A_Design]
' TableB with Fields [B_ID], [A_ID], [B_InventoryNum], [B_BlankNum]
' TableC with Fields [B_ID], [C_Name], [C_Value], [C_Date]
' TableA Primary Key is [A_ID]
' TableB Primary Key is [B_ID]
' TableC has no Primary Key, hence, for each [B_ID], there are multiple [C_Date]
以下是来自TableC的数据的一个示例 B_ID名称值C_Date 73 Diamter 35.375-98年1月27日 73 Diamter 35.376 98年1月27日 73 Diamter 35.375-98年4月12日 73 Diamter 35.374 98年7月19日 73 Diamter 35.374-2013年9月23日 73 Diamter 35.374 99年3月30日 73 Diamter 35.375 99年10月24日 73 Diamter 35.374 99年10月24日 73身高22.491 98年1月27日 73身高21.908 98年4月12日 73身高21.908 98年7月19日 73身高21.915 98年9月23日 73身高21.901 99-三月30 73身高21.909 99年10月24日 73身高22.041 98年1月27日Below is one example of the data from TableC B_ID Name Value C_Date 73 Diamter 35.375 27-Jan-98 73 Diamter 35.376 27-Jan-98 73 Diamter 35.375 12-Apr-98 73 Diamter 35.374 19-Jul-98 73 Diamter 35.374 23-Sep-98 73 Diamter 35.374 30-Mar-99 73 Diamter 35.375 24-Oct-99 73 Diamter 35.374 24-Oct-99 73 Height 22.491 27-Jan-98 73 Height 21.908 12-Apr-98 73 Height 21.908 19-Jul-98 73 Height 21.915 23-Sep-98 73 Height 21.901 30-Mar-99 73 Height 21.909 24-Oct-99 73 Height 22.041 27-Jan-98
查询顺序如下:
1. Use where [A_Design] LIKE '%99%' to Get [A_ID] From TableA 2. Use 'Left Join' to link to TableB 3. Use 'Left Join' again to link to TableC左连接"只是一个示例，可以用内部连接"代替.
'Left Join' is just an example, it can be replaced by 'Inner Join'.
我当前的Sql查询将为B_ID返回所有15行(73)，但是我只需要一个日期-最早的日期(98年1月27日)，我对其他字段也不感兴趣，例如[名称]，[值].
My current Sql query will return all the 15 rows for a B_ID (73), but I just need a single date - the earliest date (27-Jan-98), I am also not interested in other fields, such as [Name], [Value].
VB.NET中的以下代码将检索多个[C_Date]. 如果我只想要最早的[C_Date]，该如何修改我的SQL脚本?任何建议和反馈将不胜感激！
The following codes in VB.NET will retrieve multiple [C_Date]. If I only want the earliest [C_Date], how shall I modify my SQL script? Any suggestion and feedback would be greatly appreciated!
如果您对VB.NET不感兴趣，请直接跳转到Sql字符串，问题是SQL语法，它独立于任何特定的编程语言.
If you are not interested in VB.NET, please jump directly to Sql string, the question is for SQL syntax, which is independent from any specific programming language.
非常感谢@PhilipXY和@Rory提出的仅在Access环境中进行测试的建议，示例代码和参考性指南，终于解决了我的问题.
Many thanks to @PhilipXY and @Rory for the suggestion to test only within the Access environment, example codes and informative guides, finally my problem got solved.
SQL下面是查询的MS Access SQL视图中的工作副本.
Below SQL is a working copy from the MS Access SQL View of a Query.
<代码> SELECT不同的[TableA].[A_Design]，[TableA] .A_ID，[TableB] .B_ID，[TableB] .B_InventoryNum，[TableB].[B_BlankNum]，[TableC] .C_Date，[TableC ].出价 FROM([TableA]内部联接[TableB]在[TableA]上.A_ID= [TableB] .B_ID)内部联接[TableC]在[TableB] .B_ID = [TableC] .B_ID GROUP BY [TableA].[A_Design]，[TableA] .A_ID，[TableB] .B_ID，[TableB] .B_InventoryNum，[TableB].[B_BlankNum]，[TableC] .C_Date，[TableC] .B_ID 具有(([[[[TableA].[A_Design])'' 99 ')和(([[TableC] .C_Date)=(从[TableC]选择顶部1分钟([TableC] .C_Date)其中[TableC] .B_ID = [TableB] .B_ID)));
SELECT Distinct [TableA].[A_Design], [TableA].A_ID, [TableB].B_ID, [TableB].B_InventoryNum, [TableB].[B_BlankNum], [TableC].C_Date, [TableC].B_ID FROM ([TableA] INNER JOIN [TableB] ON [TableA].A_ID = [TableB].B_ID) INNER JOIN [TableC] ON [TableB].B_ID = [TableC].B_ID GROUP BY [TableA].[A_Design], [TableA].A_ID, [TableB].B_ID, [TableB].B_InventoryNum, [TableB].[B_BlankNum], [TableC].C_Date, [TableC].B_ID HAVING ((([TableA].[A_Design]) Like '99') AND (([TableC].C_Date)=(SELECT TOP 1 Min([TableC].C_Date) FROM [TableC] Where [TableC].B_ID=[TableB].B_ID)));使用此经过修订的SQL，现在我可以从TableC检索以下信息:
With this revised SQL, now I am able to retrieve the following info from TableC:
B_ID C_Date 73 27-Jan-98问题解决了！我想对@PhilipXY和@Rory都投5票. (我认为5是我可以投票的最高分.)
Problem solved! I would like to vote 5 for both @PhilipXY and @Rory. ( I assume that 5 is the highest point I can vote.)
VB.NET Codes:
================================================================ Imports System.Data.OleDb Imports System.Windows.Forms ' 1. Just add a DataGridView1 control on your winform, ' 2. then add a button (Named: 'btnTest') ' 3. Below is the button click handler, it will retrieve info from database and display it on the "DataGridView1" control.
Private Sub btnTest_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnTest.Click Dim MDBConnString_ As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\DB\Data.mdb;" Dim cnn As OleDbConnection = New OleDbConnection(MDBConnString_) cnn.Open() Dim Sql As String Sql = "SELECT " & "([A_Design]+Format([B_InventoryNum],""0000"")) AS DesignCodePK, [TableA].[A_ID], [A_Design], [TableB].[B_ID], [B_InventoryNum], [B_BlankNum], [C_Date] FROM ([TableA] LEFT JOIN [TableB] On [TableA].[A_ID] = [TableB].[A_ID]) " & " LEFT JOIN [TableC] On [TableB].[B_ID] = [TableC].[B_ID] Where [A_Design] LIKE '%99%' Order by [A_Design], [B_InventoryNum], [C_Date] " Dim cmd As New OleDbCommand(Sql, cnn) Dim DataAdapter As New OleDbDataAdapter(cmd) Dim ds As System.Data.DataSet ds = New System.Data.DataSet DataAdapter = New OleDbDataAdapter(cmd) DataAdapter.Fill(ds, "joined") DataGridView1.DataSource = ds.Tables("joined") cnn.Close() End Sub
推荐答案
要获取B_ID的最小日期，请使用
To get the min date for a B_ID use
select min(c_date) from TableC where b_id = XXXX获取所有最小日期
select b_id, min(c_date) as MinDate from TableC group by b_id然后您可以将该查询加入其他表:
You can then join that query on to other tables:
select * from TableB left join ( select b_id, min(c_date) as MinDate from TableC group by b_id ) as minimumDates on minimumDates.b_id = TableB.b_id如果查询语法有问题，请尝试直接针对MS Access运行SQL，而不是通过VB运行SQL.这样可以更轻松地检查哪些内容无效/无效.如果您无法让GROUP BY正常工作，那么就从一个尽可能简单的新查询开始，就像上面的第一个
select一样.忘记了您真正想要的列，只需运行一个简单的查询，然后一次添加JOINs和WHERE以及一列即可.If you're having trouble with query syntax try to run your SQL against MS Access directly rather than via VB. That'll make it easier to check what does/doesn't work. If you can't get the GROUP BY to work then start with a new query as simple as possible, like with my first
selectabove. Forget which columns you really want, just get a simple query working and then addJOINs andWHEREand columns one at a time.这篇关于如何使用SQL查询从多个表中获取最早的日期?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
