查找顾客购买过的所有商店

Question

我有一个包含所有事务信息的表

(交易日期、客户名称、交易金额、单位金额、店铺ID)。

如何创建一个能够正确显示客户访问过的商店的表?客户可以从多个商店购买，因此客户和商店之间的关系应该是一对多的。我正在核对每位顾客都去过哪些商店。

例子:事务表

• store_ID
• transaction_date
• customer_name
• transaction_amount
• transaction_unit

预期输出

• customer_name
• store_list

这只是一个假设问题。也许单独列出所有商店会更好?(但我想，如果我们想要查看在这些商店购物的顾客，可能会造成混乱)。提前感谢:)

Answer 1

既然您已经有了一个包含所有所需信息的表，也许您需要一个视图，以避免非规范化/重复的数据。

示例如下所示。这里我将使用sqlite语法(text而不是varchar，等等)。我们正在规范化数据以避免问题-例如，您已经显示了一个包含客户名称的表，但不同的客户可以具有相同的名称，因此我们使用customer_id代替。

第一个表:

create table if not exists store (id integer primary key, name text, address text);
create table if not exists customer (id integer, name text, document integer);
create table if not exists unit (symbol text primary key);
insert into unit values ('USD'),('EUR'),('GBP'); -- static data
create table if not exists transact (id integer primary key, store_id integer references store(id), customer_id integer references customer(id), transaction_date date, amount integer, unit text references unit(symbol));
-- transaction is a reserved word, so transact was used instead

现在视图:

-- store names concatenated with ',' using sqlite's group_concat
create view if not exists stores_visited as select cust.name, group_concat(distinct store.name order by store.id) from transact trx join customer cust on trx.customer_id=cust.id join store on trx.store_id=store.id group by cust.name order by cust.id;
-- second version, regular select with multiple lines
-- create view if not exists stores_visited as select cust.id, cust.name, store.id, store.name from transact trx join customer cust on trx.customer_id=cust.id join store on trx.store_id=store.id;

样本数据:

insert into store values
(1,'store one','address one'),
(2,'store two','address two')
(3,'store three','address three');
insert into customer values
(1,'customer one','custdoc one'),
(2,'customer two','custdoc two'),
(3,'customer three','custdoc three');
insert into transact values
(1,1,1,date('2021-01-01'),1,'USD'),
(2,1,1,date('2021-01-02'),1,'USD'),
(3,1,2,date('2021-01-03'),1,'USD'),
(5,1,3,date('2021-01-04'),1,'USD'),
(6,2,1,date('2021-01-05'),1,'USD'),
(7,2,3,date('2021-01-06'),1,'USD'),
(8,3,2,date('2021-01-07'),1,'USD');

测试:

select * from stores_visited;
-- customer one|store one,store one,store two
-- customer three|store one,store two
-- customer two|store one