sql 集合的差 mysql_7 SQL 集合运算

-- ddl ：创建表

create table zaikoshohin (

souko_id char(4) not null,

shohin_id char(4) not null,

zaiko_suryo integer not null,

primary key (souko_id, shohin_id)

);

-- dml ：插入语句

start transaction;

insert into zaikoshohin (souko_id, shohin_id, zaiko_suryo) values ('s001', '0001', 0);

insert into zaikoshohin (souko_id, shohin_id, zaiko_suryo) values ('s001', '0002', 120);

insert into zaikoshohin (souko_id, shohin_id, zaiko_suryo) values ('s001', '0003', 200);

insert into zaikoshohin (souko_id, shohin_id, zaiko_suryo) values ('s001', '0004', 3);

insert into zaikoshohin (souko_id, shohin_id, zaiko_suryo) values ('s001', '0005', 0);

insert into zaikoshohin (souko_id, shohin_id, zaiko_suryo) values ('s001', '0006', 99);

insert into zaikoshohin (souko_id, shohin_id, zaiko_suryo) values ('s001', '0007', 999);

insert into zaikoshohin (souko_id, shohin_id, zaiko_suryo) values ('s001', '0008', 200);

insert into zaikoshohin (souko_id, shohin_id, zaiko_suryo) values ('s002', '0001', 10);

insert into zaikoshohin (souko_id, shohin_id, zaiko_suryo) values ('s002', '0002', 25);

insert into zaikoshohin (souko_id, shohin_id, zaiko_suryo) values ('s002', '0003', 34);

insert into zaikoshohin (souko_id, shohin_id, zaiko_suryo) values ('s002', '0004', 19);

insert into zaikoshohin (souko_id, shohin_id, zaiko_suryo) values ('s002', '0005', 99);

insert into zaikoshohin (souko_id, shohin_id, zaiko_suryo) values ('s002', '0006', 0);

insert into zaikoshohin (souko_id, shohin_id, zaiko_suryo) values ('s002', '0007', 0);

insert into zaikoshohin (souko_id, shohin_id, zaiko_suryo) values ('s002', '0008', 18);

commit;

-- sql server    db2   postgresql    mysql

select ts.tenpo_id, ts.ten_mei, ts.shohin_id, s.shohin_mei, s.hanbai_tanka, zs.zaiko_suryo

from tenposhohin as ts inner join shohin as s on ts.shohin_id = s.shohin_id

inner join zaikoshohin as zs on ts.shohin_id = zs.shohin_id where zs.souko_id = 's001';

-- sql server    db2   postgresql    mysql

select ts.tenpo_id, ts.ten_mei, ts.shohin_id, s.shohin_mei from tenposhohin as ts cross join shohin as s;