如何连接来自不同数据库的表?

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 question for an example case you can adapt to your language when you attach databases as mentioned in the accepted answer.

编辑:请参阅这个问题，以了解当您附加数据库(如所接受的答案中所提到的)时，您可以适应您的语言。

3 个解决方案

#1

If ATTACH is activated in your build of Sqlite (it should be in most builds), you can attach another database file to the current connection using the ATTACH keyword. The limit on the number of db's that can be attached is 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(在大多数构建中应该是)，您可以使用ATTACH关键字将另一个数据库文件附加到当前连接。可以附加的db数量的限制是一个编译时设置(sqlite_max_attach)，目前默认为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 main and temp are 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".

注意，“[t]he数据库名main和temp是为主数据库和数据库保留的，用于保存临时表和其他临时数据对象。这两个数据库名称对于每个数据库连接都存在，不应该用于附件”。

#2

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实例!

#3

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;
    }

}

#1

attach 'database1.db' as db1;
attach 'database2.db' as db2;