mysql - SQL View 中需要额外的行

Question

我有以下 SQL 表，需要一个同时兼容 MySQL 和 Postgresql 的解决方案

create table price_level (
 id serial primary key,
 name varchar(200)
);

create table product (
 id serial primary key,
 name varchar(200),
 base numeric not null,
 vat int not null
);

create table product_price (
 id serial primary key,
 base numeric,
 vat numeric,
 product_id int not null references product(id) on update cascade on delete cascade,
 price_level_id int not null references price_level(id) on update cascade on delete cascade,
 unique(product_id,price_level_id)
);

对于上面的 SQL 结构，我创建了一个 View :

create view view_product as
select
    p.id as product_id,
    coalesce(pp.base, p.base) as base,
    coalesce(pp.vat, p.vat) as vat,
    pp.price_level_id

from
    product as p
left join
    product_price as pp on pp.product_id=p.id
;

这些是示例数据:

表price_level

id  name
1   A
2   B
3   C
4   D
5   E

表格产品

id  name    base    vat
1   Test    100     20

表product_price

id   base   vat  product_id price_level_id
1    NULL   NULL 1          1
2    200    NULL 1          2
3    NULL   10   1          3

View view_product 的输出是:

product_id   base    vat  price_level_id
1            100    20    1
1            200    20    2
1            100    10    3

... 问题是:如何获得这样的输出？:

product_id   base   vat    price_level_id
1            100    20    1
1            200    20    2
1            100    10    3
1            100    20    4
1            100    20    5

如您在上面的示例中所见，我需要获取 D 和 E price_level 作为附加行。我如何创建这样的 View /加入？它还应该具有良好的性能，因为表格会随着价格水平的增加而变大。

感谢您的帮助。

Answer 1

我会使用 union 从 price_level 表中添加那些在特定产品的 product_price 表中没有相应记录的记录:

select
    p.id as product_id,
    coalesce(pp.base, p.base) as base,
    coalesce(pp.vat, p.vat) as vat,
    pp.price_level_id

from
    product as p
left join
    product_price as pp on pp.product_id=p.id
union distinct
select
    p.id as product_id,
    p.base,
    p.vat,
    pl.price_level_id
from
    price_level pl
join
    product as p
where (p.id, pl.id) not in (select product_id, price_level_id from product_price)