SQL SQLite - 如何连接来自不同数据库的表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6824717/
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
SQLite - How do you join tables from different databases?
提问by Adam Smith
I have an application that uses a SQLite database and everything works the way it should. I'm now in the process of adding new functionalities that require a second SQLite database, but I'm having a hard time figuring out how to join tables from the different databases.
我有一个使用 SQLite 数据库的应用程序，一切正常。我现在正在添加需要第二个 SQLite 数据库的新功能，但我很难弄清楚如何连接来自不同数据库的表。
If someone can help me out with this one, I'd really appreciate it!
如果有人能帮我解决这个问题，我将不胜感激！
Edit: See this questionfor an example case you can adapt to your language when you attach databases as mentioned in the accepted answer.
编辑：请参阅此问题的示例案例，当您附加已接受的答案中提到的数据库时，您可以适应您的语言。
回答by Brian Gideon
If ATTACHis activatedin your build of Sqlite (it should be in mostbuilds), you can attach another database file to the current connection using the ATTACHkeyword. The limit on the number of db's that can be attachedis a compile time setting(SQLITE_MAX_ATTACHED), currently defaults to 10, but this too may vary by the build you have. The global limit is 125.
如果连网被激活你的SQLite版本（应该是在大多数版本），你可以附加其他数据库文件使用当前连接ATTACH关键字。可以附加的数据库数量的限制是编译时设置（SQLITE_MAX_ATTACHED），当前默认为 10，但这也可能因您的构建而异。全局限制为 125。
attach 'database1.db' as db1;
attach 'database2.db' as db2;
You can see all connected databases with keyword
您可以看到所有连接的数据库与关键字
.databases
Then you should be able to do the following.
那么你应该能够做到以下几点。
select
*
from
db1.SomeTable a
inner join
db2.SomeTable b on b.SomeColumn = a.SomeColumn;
Note that "[t]he database names mainand tempare reserved for the primary database and database to hold temporary tables and other temporary data objects. Both of these database names exist for every database connection and should not be used for attachment".
请注意，“数据库名称main和temp保留给主数据库和数据库以保存临时表和其他临时数据对象。这两个数据库名称都存在于每个数据库连接中，不应用于附件”。
回答by Dr.Sai
Here is a C# example to complete this Question
这是完成此问题的 C# 示例
/// <summary>
/// attachSQL = attach 'C:\WOI\Daily SQL\Attak.sqlite' as db1 */
/// path = "Path of the sqlite database file
/// sqlQuery = @"Select A.SNo,A.MsgDate,A.ErrName,B.SNo as BSNo,B.Err as ErrAtB from Table1 as A
/// inner join db1.Labamba as B on
/// A.ErrName = B.Err";
/// </summary>
/// <param name="attachSQL"></param>
/// <param name="sqlQuery"></param>
public static DataTable GetDataTableFrom2DBFiles(string attachSQL, string sqlQuery)
{
try
{
string conArtistName = "data source=" + path + ";";
using (SQLiteConnection singleConnectionFor2DBFiles = new SQLiteConnection(conArtistName))
{
singleConnectionFor2DBFiles.Open();
using (SQLiteCommand AttachCommand = new SQLiteCommand(attachSQL, singleConnectionFor2DBFiles))
{
AttachCommand.ExecuteNonQuery();
using (SQLiteCommand SelectQueryCommand = new SQLiteCommand(sqlQuery, singleConnectionFor2DBFiles))
{
using (DataTable dt = new DataTable())
{
using (SQLiteDataAdapter adapter = new SQLiteDataAdapter(SelectQueryCommand))
{
adapter.AcceptChangesDuringFill = true;
adapter.Fill(dt);
return dt;
}
}
}
}
}
}
catch (Exception ex)
{
MessageBox.Show("Use Process Exception method An error occurred");
return null;
}
}
回答by Yugal Jindle
Well, I don't have much experience with SQLite you have to access both databases in a single query.
好吧，我对 SQLite 没有太多经验，您必须在单个查询中访问两个数据库。
You can have something like :
你可以有类似的东西：
select name from DB1.table1 as a join DB2.table2 as b where a.age = b.age;
In databases like SQLServer you can access other databases in this hierarchical fashion, this should also work for SQLite.
在像 SQLServer 这样的数据库中，您可以以这种分层方式访问其他数据库，这也适用于 SQLite。
I think you can initiate an instance of sqlite with more than 1 databases !
我认为您可以使用 1 个以上的数据库启动一个 sqlite 实例！
