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;