时间戳

首页 > 解决方案 > 关联 2 个表的 SQL 查询

sqlite - 关联 2 个表的 SQL 查询

问题描述

我想在 sqlite3 中关联以下 2 个表。我从其他示例中了解到，我们应该在每个表之间有一些公共字段，所以我添加了 order_ID。

1）如何编写sqlite查询来创建这些表之间的关系？

2）如何管理表2，同一个订单可以有多个产品，所以订单ID重复。一个订单可以有最少 1 个和最多 10 个产品。所以它的动态范围是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

标签： sqlite

解决方案

看起来您想要存储订单和有关这些订单的信息。首先，制作一个订单表。

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。这不是必需的，但这是我喜欢的约定，因为它使主键和外键不同。

另请注意，我使用timestamp类型来存储日期，这将使处理这些日期变得更加容易，因为您可以使用SQLite 日期函数。

现在我们需要一个表格来了解每个订单的内容。

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。一个订单可以有很多products。您可以使用连接将这些表链接在一起。以下是您如何为每种产品吸引买家。

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行order.id匹配的行order_id，并将它们都视为单行。

从这里您可能想要制作products和buyer他们自己的表格以及存储有关买家和产品的任何信息。它还确保产品和买家的存在避免拼写错误。

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

然后，您可以将所有内容连接在一起以获取有关产品和买家的信息。

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

SQL小提琴

如果你现在不这样做，以后会更难做到。

推荐阅读