منطق استعلام SQL والمقامات الصريحة

import sqlite3
from contextlib import closing

with closing(sqlite3.connect(":memory:")) as db:
    db.execute("CREATE TABLE orders(region TEXT, status TEXT, amount REAL)")
    db.executemany("INSERT INTO orders VALUES (?, ?, ?)", [
        ("North", "open", 20.0), ("North", "open", 40.0),
        ("North", "closed", 200.0), ("South", "open", 25.0),
        ("South", "open", None),
    ])
    query = """SELECT region, COUNT(*), COUNT(amount), SUM(amount)
               FROM orders WHERE status = ? GROUP BY region
               HAVING SUM(amount) >= ?
               ORDER BY SUM(amount) DESC, region ASC"""
    print(db.execute(query, ("open", 50)).fetchall())
    print(db.execute("SELECT COUNT(*), COUNT(amount) FROM orders WHERE status = ?",
                     ("open",)).fetchone())

[('North', 2, 2, 60.0)]
(4, 3)

تجمع North المبلغين20 و40 ولا يشارك200 المغلق. ولـSouth صفان مفتوحان لكن مبلغاً معروفاً واحداً.

import sqlite3
from contextlib import closing

with closing(sqlite3.connect(":memory:")) as db:
    db.execute("CREATE TABLE readings(id INTEGER PRIMARY KEY, amount REAL)")
    db.executemany("INSERT INTO readings VALUES (?, ?)",
                   [(1, None), (2, 0.0), (3, 10.0), (4, 10.0)])
    def ids(query):
        return [row[0] for row in db.execute(query)]
    print(ids("SELECT id FROM readings WHERE amount <> 0 ORDER BY id"))
    print(ids("SELECT id FROM readings WHERE amount IS NULL ORDER BY id"))
    print(ids("SELECT id FROM readings ORDER BY amount DESC, id ASC LIMIT 2"))

[3, 4]
[1]
[3, 4]

لا تنجح NULL في شرط عدم المساواة ويجدها فحص الغياب الصريح. يختار الترتيب النهائي3 و4 بحتمية.