谁没有对应的
两个方向各找一次"没有对应的"。输出:没有供应商的商品名、没供过货的供应商名(用 / 隔开)。
goods supplier id name cat price stock sup_id id name city parent_id 1 绿茶 饮料 6 12 1 1 佳农 杭州 (空) 2 红茶 饮料 6 0 1 2 康泉 杭州 1 3 苏打水 饮料 5 (空) 2 3 文兴 苏州 (空) 4 薯片 零食 8 7 2 4 远丰 南京 3 5 海苔 零食 12 3 (空) 6 巧克力 零食 15 (空) 3 tag goods_tag 7 铅笔 文具 2 40 3 1 热销 1-1 1-2 8 笔记本 文具 9 5 3 2 新品 4-1 6-3 9 橡皮 文具 3 0 (空) 3 特价 7-1 8-3 10 冰红茶 饮料 4 20 1
import sqlite3
db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE supplier (id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE,
city TEXT NOT NULL, parent_id INTEGER);
CREATE TABLE goods (id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE,
cat TEXT NOT NULL, price INTEGER NOT NULL,
stock INTEGER, sup_id INTEGER);
CREATE TABLE tag (id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE);
CREATE TABLE goods_tag (goods_id INTEGER, tag_id INTEGER);
""")
db.executemany("INSERT INTO supplier VALUES (?,?,?,?)", [
(1, "佳农", "杭州", None), (2, "康泉", "杭州", 1),
(3, "文兴", "苏州", None), (4, "远丰", "南京", 3)])
db.executemany("INSERT INTO goods VALUES (?,?,?,?,?,?)", [
(1, "绿茶", "饮料", 6, 12, 1), (2, "红茶", "饮料", 6, 0, 1),
(3, "苏打水", "饮料", 5, None, 2), (4, "薯片", "零食", 8, 7, 2),
(5, "海苔", "零食", 12, 3, None), (6, "巧克力", "零食", 15, None, 3),
(7, "铅笔", "文具", 2, 40, 3), (8, "笔记本", "文具", 9, 5, 3),
(9, "橡皮", "文具", 3, 0, None), (10, "冰红茶", "饮料", 4, 20, 1)])
db.executemany("INSERT INTO tag VALUES (?,?)", [(1, "热销"), (2, "新品"), (3, "特价")])
db.executemany("INSERT INTO goods_tag VALUES (?,?)", [
(1, 1), (1, 2), (4, 1), (6, 3), (7, 1), (8, 3)])
a = db.execute("SELECT g.name FROM goods g "
"LEFT JOIN supplier s ON g.sup_id = s.id "
"WHERE s.id IS NULL ORDER BY g.id").fetchall()
b = db.execute("SELECT s.name FROM supplier s "
"LEFT JOIN goods g ON g.sup_id = s.id "
"WHERE g.id IS NULL ORDER BY s.id").fetchall()
print("".join(r[0] for r in a) + "/" + "".join(r[0] for r in b))
全部评论