販売管理システムの商品マスタ 単価について SQL

概要

試した忘備録として記録に残します。
内容は以下３点になります。
・単価について説明
・DDLでサンプルデータを定義
・DMLで想定される処理を再現

用語

販売単価

商品の定価。
得意先ごとに販売単価が変化する場合がある。
例えば大口のお客様に対しては販売価格が安くなるなど
別称は「標準販売価格」、「上代」とも

仕入単価

仕入先から商品を仕入れる際の標準的な単価。
別称は「標準仕入れ価格」、「下代」とも

例

実現したいこと

商品名「牛ヒレ」に対して、３種類の得意先の販売単価を取得したい。

販売単価は1500

得意先コード	得意先名	顧客別販売単価
00001	初見	1700
00002	常連	1000
00003	グループ会社	900

※顧客別単価表方式は「得意先×商品」を採用。
他の顧客別単価表方式に
「得意先グループ×商品」
「得意先グループ×商品グループ」
などが挙げられる。

動作環境

名称	version	説明
SQLite	-	軽量型RDBMS
Docker	-	コンテナ型仮想化技術
Github Codespaces	-	クラウドIDE

DDLとInsert文

-- docker-compose exec -it webserver bash
-- sqlite3 03_hanbai/ch3-4_master/test.sqlite3  < 03_hanbai/ch3-4_master/product/m_products.sql

create table m_products(
    code char(3) PRIMARY KEY,
    official_name varchar(50),
    kana varchar(50),
    serial_number varchar(20),
    purchase_price int,
    sale_price int,
    tax_classification char(1),
    classification_code char(9) not null,
    updated_at timestamp not null,
    updated_by varchar(10) not null,
    remark varchar(100)
);

insert into m_products values
-- 販売単価と仕入単価
('001','牛ひれ','ギュウヒレ','1129',1000,1500,'1','010101','2020-01-01 00:00:00','SYSTEM',"");

drop table if exists sales_price_by_customer;

create table sales_price_by_customer(
    product_code char(3) not null,
    customer_code char(5) not null,
    sale_price int not null,
    primary key(product_code,customer_code)
);

insert into sales_price_by_customer values
('00001','00001',1700),
('00001','00002',1000),
('00001','00003',900);

SQL文

-- docker-compose exec -it webserver bash
-- sqlite3 03_hanbai/ch3-4_master/test.sqlite3 < 03_hanbai/ch3-4_master/product/getSalePriceByCustomer.sql
select  a.code,
        CASE b.customer_code
            WHEN '00001' THEN '初見'
            WHEN '00002' THEN '常連'
            WHEN '00003' THEN 'グループ会社'
        END,
        b.sale_price
from m_products as A,sales_price_by_customer as B
where A.official_name = '牛ひれ'
and A.code = B.product_code
;

結果

A.code	B.customer_code	B.sale_price
001	初見	1700
001	常連	1000
001	グループ会社	900

最後に

「得意先グループ×商品」
「得意先グループ×商品グループ」
のパターンも時間があったら確認しときたいですね。

参考

グラス片手にデータベース設計　販売管理システム編　著者：梅田弘之 p53

github　履歴