vb.net MS Access SQL 从两个表中选择语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19145229/
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
MS Access SQL Select Statement from Two Tables
提问by gromit1
I have two Microsoft Access database tables. They are named Historical_Stock_Pricesand Balance_Sheets. I need to combine data from each of these tables to create a table called Daily. I need to take the fields Ticker, [Date], and [Close]from Historical_Stock_Pricesand I need to take the field Common_Stocksfrom Balance_Sheets.
我有两个 Microsoft Access 数据库表。它们被命名为Historical_Stock_Prices和Balance_Sheets。我需要组合来自这些表中的每一个的数据来创建一个名为Daily. 我需要取字段Ticker,[Date]和[Close]fromHistorical_Stock_Prices并且我需要取字段Common_Stocksfrom Balance_Sheets。
I will not be taking every row from the Historical_Stock_Pricesand Balance_Sheetsthough. I will only be taking the rows that are on or before a date selected in a DateTimePickernamed dtpDateSelection.
我不会从Historical_Stock_Pricesand 中取出每一行Balance_Sheets。我将只采用在DateTimePicker命名的dtpDateSelection.
Now the main problem that I have is that Historical_Stock_Pricescontains a row for each day. While Balance_Sheetscontains a row for each quarter. So for each day in a quarter the figure Common_Stocksfigure that comes from Balance_Sheetwill be the same.
现在我Historical_Stock_Prices遇到的主要问题是每天都包含一行。虽然Balance_Sheets每个季度包含一行。因此，对于一个季度中的每一天Common_Stocks，来自的数字数字Balance_Sheet将是相同的。
How do I do this?
我该怎么做呢？
Here is the code that I have so far:
这是我到目前为止的代码：
Dim Date1 As Date = dtpDateSelection.Value
Dim cmd As OleDbCommand = New OleDbCommand("CREATE PROC Daily AS SELECT Ticker, [Date], [Close] From Historical_Stock_Prices WHERE [Date] = " & Date1 & "", con)
cmd.ExecuteNonQuery()
This obviously does not incorporate the table Balance_Sheetat all. Also, currently my where statement is throwing an error.
这显然根本不包含表格Balance_Sheet。另外，目前我的 where 语句抛出错误。
Additional Information: Table Schema
附加信息：表架构
回答by Jeremy Cook
Inner join on the quarter that Historical_Stock_Prices and Balance_Sheets have in common. That is, every date falls in a quarter. If Balance_Sheets has a field named Period that is the number 1, 2, 3 or 4, corresponding to each quarter in a year, and you have a Year field that is a 4 digit number then this select query should work and can be easily converted into a make table query.
在Historical_Stock_Prices 和Balance_Sheets 共同的季度内连接。也就是说，每个日期都在一个季度内。如果 Balance_Sheets 有一个名为 Period 的字段，它是数字 1、2、3 或 4，对应于一年中的每个季度，并且您有一个 4 位数字的 Year 字段，那么此选择查询应该可以工作并且可以轻松转换进入制作表查询。
select Ticker, Date, Close, Common_Stocks
from Historical_Stock_Prices, Balance_Sheets
where Format(Historical_Stock_Prices.Date, "q") = Balance_Sheets.Period
and Year(Historical_Stock_Prices.Date) = Balance_Sheets.Year
回答by APrough
Create query off of Historical_Stock_Prices that includes 2 new fields, Quarter and Year, with the appropriate functions to pull those values from the date of the row. Then join your two tables using those fields. Your final statement in VB should go against that query.
创建包含 2 个新字段 Quarter 和 Year 的 Historical_Stock_Prices 的查询，并使用适当的函数从行的日期中提取这些值。然后使用这些字段连接您的两个表。您在 VB 中的最后一条语句应该与该查询背道而驰。
As for why you are getting an error on the SQL statement, you do not have # around the parameter.
至于为什么在 SQL 语句上出现错误，参数周围没有 #。
Dim cmd As OleDbCommand = New OleDbCommand("CREATE PROC Daily AS SELECT Ticker, [Date], [Close] From Historical_Stock_Prices WHERE [Date] = #" & Date1 & "#", con)
Note that this is still bad form, and is just waiting for SQL Injection attacks. BEst to look up how to use parameterized queries.
请注意，这仍然是糟糕的形式，只是在等待 SQL 注入攻击。最好查找如何使用参数化查询。
回答by Linger
"SELECT [H].[Ticker], [H].[Date], [H].[Close], [B].[Common_Stocks]
FROM [Historical_Stock_Prices] AS [H], [Balance_Sheets] AS [B]
WHERE [H].[Ticker] = [B].[Ticker]
AND Int(Format([H].[Date],'Q')) = Int([B].[Period])
AND Int(Year([H].[Date])) = Int([B].[Year])
AND [H].[Date] <= #" & Date1.value & "#"
