关联 2 个表的 SQL 查询 [英] SQL query to relate 2 tables
问题描述
我想在 sqlite3 中关联以下 2 个表.我从其他例子中了解到，每个表之间应该有一些公共字段，所以我添加了 order_ID.
I want to relate following 2 tables in sqlite3. What I understood from other examples is that we should have some common field between each table, so I added order_ID.
1) 如何编写 sqlite 查询来创建这些表之间的关系?
1) How to write sqlite queries for creating the relation between these tables?
2)如何管理表2，同一个订单可以有多个产品，所以订单ID重复.一个订单可以有最少 1 个和最多 10 个产品.所以它的动态范围是1-10.
2) How to manage Table 2, where same order can have multiple products, so order ID is repeated. An order can have min 1 and max 10 products. So it has dynamic range of 1-10.
table 1:
order_ID date buyer ship_chr
001 01/01 abc 15
002 05/01 xyz 10
table 2:
order_ID prod quantity rate
001 pen 50 2
001 paper 25 1
001 pin 50 2
002 paper 25 1
002 pen 100 2
推荐答案
您似乎想要存储订单和有关这些订单的信息.首先，制作一个订单表.
It looks like you want to store orders and information about those orders. First, make an orders table.
create table orders (
id integer primary key autoincrement,
created_at timestamp not null default current_timestamp,
buyer text not null,
ship_chr text not null
)
请注意，表的主键不是 order_id，而是 id.这不是必需的，但这是我喜欢的约定，因为它使主键和外键保持不同.
Note that instead of order_id, the primary key of a table is just id.
It's not required, but it is a convention I like as it keeps primary and foreign keys distinct.
另请注意，我使用 timestamp 类型来存储日期，这将使处理这些日期变得更加容易，因为您可以使用 SQLite 日期函数.
Also note that I'm using the timestamp type to store dates, this will make working with those dates much easier as you can use SQLite date functions.
现在我们需要一个表格来显示每个订单的内容.
Now we need a table for information about what is in each order.
create table order_products (
id integer primary key autoincrement,
order_id integer not null references orders(id),
product text not null,
quantity integer not null,
rate integer not null
)
这在 orders 和 order_products 之间建立了一对多的关系.一个订单可以有多个产品.您可以使用将这些表链接在一起加入.这是你得到的方式每个产品的买家.
This sets up a one-to-many relationship betweeen orders and order_products.
One order can have many products. You can link these tables together using
a join. Here's how you'd get
the buyer for each product.
select o.buyer, op.product, op.quantity
from order_products op
join orders o on o.id = op.order_id
abc|pen|50
abc|paper|25
abc|pin|50
xyz|paper|25
xyz|pen|100
join orders o on o.id = op.order_id 表示对于 order_products 中的每一行，在 orders where order 中找到一个.id 匹配行的 order_id 并将它们都视为一行.
join orders o on o.id = op.order_id says for every row in order_products find one in orders where order.id matches the row's order_id and treat them both as a single row.
从这里你可能想要制作 products 和 buyer 自己的表格以及存储有关买家和产品的任何信息.它还确保产品和买家的存在，避免打字错误.
From here you'll probably want to make products and buyer their own tables
as well to store any information about the buyers and products. It also ensures
that the products and buyers exist avoiding typos.
create table buyers (
id integer primary key autoincrement,
name text not null,
address text not null,
phone text not null
);
create table products (
id integer primary key autoincrement,
name text not null,
stock integer not null default 0
);
create table orders (
id integer primary key autoincrement,
created_at timestamp not null default current_timestamp,
buyer_id integer references buyers(id) not null,
ship_chr text not null
);
create table order_products (
id integer primary key autoincrement,
order_id integer not null references orders(id),
product_id integer not null references products(id),
quantity integer not null,
rate integer not null
);
然后您可以将所有内容连接在一起以获取有关产品和买家的信息.
Then you can join everything together to get information about products and buyers.
select b.name, p.name, op.quantity
from order_products op
join orders o on o.id = op.order_id
join buyers b on b.id = o.buyer_id
join products p on p.id = op.product_id
name|name|quantity
abc|pen|50
abc|paper|25
abc|pin|50
xyz|paper|25
xyz|pen|100
如果你现在不这样做，以后会更难做.
If you don't do this now it will be harder to do later.
这篇关于关联 2 个表的 SQL 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
