一次查询得出汇总

机器翻译

本页由英文原文机器翻译而来，尚未经过母语审校，欢迎在 GitHub 上提出修改。内容如有出入，以英文原文为准。

任务： 按地区显示销售总额，而不必询问数据库两次。

yaml

# Recipe: one query to the database, and totals computed from its result.
paths:
  components: ./components
  migrations: ./migrations

datasources:
  db:
    driver: sqlite
    database: ./data/sales.db

sql

CREATE TABLE sales (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    region TEXT NOT NULL,
    amount INTEGER NOT NULL
);

INSERT INTO sales (region, amount) VALUES
    ('North', 120), ('South', 80), ('North', 200), ('East', 50), ('South', 40);

q:query source="sales" 在名为 sales 的查询结果上运行它的 SQL，这个结果以同名数据表的形式出现。测试中的 queries="1" 检查数据库只被询问了一次。

xml

<q:component name="Sales">
  <!-- The database is asked once. -->
  <q:query name="sales" datasource="db">
    SELECT region, amount FROM sales ORDER BY id
  </q:query>

  <!-- source="sales": SQL over that result, in memory — the table is
       named after the query. -->
  <q:query name="totals" source="sales">
    SELECT region, SUM(amount) AS total, COUNT(*) AS n
    FROM sales GROUP BY region ORDER BY total DESC
  </q:query>

  <ui:window title="Sales">
    <ui:table source="{totals}">
      <ui:column key="region" label="Region" />
      <ui:column key="total" label="Total" />
      <ui:column key="n" label="Sales" />
    </ui:table>
    <ui:text>{sales_result.recordCount} sales</ui:text>
  </ui:window>
</q:component>

xml

<q:test name="totals by region, from one query" page="/">
  <test:visit />
  <test:expect text="North 320 2 South 120 2 East 50 1" />
  <test:expect text="5 sales" />
  <test:expect queries="1" />
</q:test>

text

tests/totals.test.q
  PASS  totals by region, from one query
1 passed, 0 failed

参见 DB-3。

一次查询得出汇总 ​

一次查询得出汇总