Запрос SQL для связывания 2 таблиц

Я хочу связать следующие 2 таблицы в sqlite3. То, что я понял из других примеров, состоит в том, что у нас должно быть какое-то общее поле между каждой таблицей, поэтому я добавил order_ID.

1) Как написать sqlite-запросы для создания связи между этими таблицами?

2) Как управлять таблицей 2, где тот же порядок может иметь несколько продуктов, поэтому идентификатор заказа повторяется. Заказ может иметь мин 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

Всего 1 ответ

Похоже, вы хотите хранить заказы и информацию об этих заказах. Сначала создайте таблицу заказов. 

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 Fiddle

Если вы этого не сделаете, это будет сложнее сделать позже.

Есть идеи?

10000