不写连接条件,出来多少行

👁️ 0 人浏览 💬 0 人评论 ❤️ 添加收藏

把两张表放一起但不写连接条件,再和正确的内连接比。输出三个数:不写条件几行、正确内连接几行、商品×供应商是多少(用 / 隔开)。

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 COUNT(*) FROM goods, supplier").fetchone()[0]
b = db.execute("SELECT COUNT(*) FROM goods g "
               "JOIN supplier s ON g.sup_id = s.id").fetchone()[0]
c = (db.execute("SELECT COUNT(*) FROM goods").fetchone()[0]
     * db.execute("SELECT COUNT(*) FROM supplier").fetchone()[0])
print(str(a) + "/" + str(b) + "/" + str(c))
提交你的答案
请登录后提交答案。
去登录
代码编辑器
Ctrl + Enter 运行
本次输入:
输出:

                        
👩‍🏫
AI
💬 题目评论

全部评论