如何在可变列数据上设置SQLite Join [英] How to setup SQLite Join on variable column data

问题描述

我有2个表。一个表包含一个人的姓名和该人的唯一ID。另一个表有多个列，但特别有一列存储人的唯一ID。

到目前为止，第二个表始终存储了一个ID，因此我能够轻松地对这两个表执行JOIN，并获得我需要的数据：

  SELECT DISTINCT personTable._id， personTable.name FROM dataTable LEFT OUTER JOIN personTable ON dataTable.person_id = personTable._id 
  

但是，要求开始起作用，现在将允许第二个表在同一列中引用多个ID。换句话说，该表严格如下：

  2ND TABLE COLUMN PERSON'S ID = 5 
  
 
 
 
但现在的存储可以像这样
  2ND TABLE COLUMN PERSON'S ID = 5-6 
 2ND TABLE COLUMN PERSON'S ID = 5-3-8-2 
 2ND TABLE COLUMN PERSON'S ID = 5（仍然有效）
  
使用此新格式，我无法每次成功查询数据。如何更新此查询以正常工作？我在Android中使用SQLite。非常感谢您的帮助。
解决方案
如果你使用新格式，你不会远远。
 
 
 
要提供新规格，您应该更改您的模式，以便再增加一个表作为连接表。
 
 
 
总共有3个表格，如下所示：
  CREATE TABLE persontable（
 person_id INTEGER PRIMARY KEY，
 name VARCHAR 32），
  - 更多的人字段... 
）
 
 CREATE TABLE datatable（
 data_id INTEGER PRIMARY KEY，
 join_id INTEGER，
  - 更多的数据字段... 
）
 
 CREATE TABLE可连接（
 join_id INTEGER PRIMARY KEY，
 person_id INTEGER 
）
  
可联结将根据您的需要为每个用户添加多行，例如
 
 
 
 2ND TABLE COLUMN PERSON'S ID = 5-3-8-2 
 
 
 
它将有4行。
 
 
 
现在，您可以有如下数据：
  SELECT d。*，
 p。名称，
 ... 
从数据库d 
 JOIN人员p USING（person_id）
 JOIN可连接j使用（join_id）
 WHERE d.some_field ='blah' 
  
 
I have 2 tables. One table contains a person's name and that person's unique ID. The other table has multiple columns, but there is one column in particular that stores the person's unique ID.

Until now, the 2nd table has always stored a single ID, so I was able to easily perform JOINs on these two tables and get the data I needed like so:
SELECT DISTINCT personTable._id, personTable.name FROM dataTable LEFT OUTER JOIN personTable ON dataTable.person_id = personTable._id
However, a new requirement came into play that now will allow the 2nd table to reference more than one ID in the same column. In other words, the table used to be strictly like this:
2ND TABLE COLUMN PERSON'S ID = 5
But now the storage can be like this
2ND TABLE COLUMN PERSON'S ID = 5-6
2ND TABLE COLUMN PERSON'S ID = 5-3-8-2
2ND TABLE COLUMN PERSON'S ID = 5 (still valid)
With this new format, I can not query data successfully each time. How can I update this query to work properly? I am using SQLite in Android. Thanks for any help.
解决方案
You will not get far if you use new format.

To serve new spec, you should change your schema to have one more table which will serve as join table.

In total, you will have 3 tables, something like this:
CREATE TABLE persontable (
    person_id INTEGER PRIMARY KEY,
    name VARCHAR(32),
    -- more person fields...
)

CREATE TABLE datatable (
    data_id INTEGER PRIMARY KEY,
    join_id INTEGER,
    -- more datatable fields...
)

CREATE TABLE jointable (
    join_id INTEGER PRIMARY KEY,
    person_id INTEGER
)
Jointable will have as many rows per user as you need, for example for

2ND TABLE COLUMN PERSON'S ID = 5-3-8-2

it will have 4 rows.

Now, you can have your data as follows:
SELECT d.*,
    p.name,
    ...
FROM datatable d
    JOIN persons p USING (person_id)
    JOIN jointable j USING (join_id)
WHERE d.some_field = 'blah'


                        
这篇关于如何在可变列数据上设置SQLite Join的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！