PSSdev/WebDB

概要

メモ

集計クエリー

select q.qid, q.question, sum(l.nc) as snc, sum(l.ni) as sni
 from question as q LEFT JOIN question_log as l on q.qid=l.qid
 where q.level>320
 group by q.qid, q.question
 limit 10

問題テーブル

question

CREATE TABLE question (
  qid INTEGER PRIMARY KEY,
  question TEXT NOT NULL,
  answer TEXT NOT NULL UNIQUE,
  hint TEXT NOT NULL,
  description TEXT NOT NULL,
  pronounce TEXT NOT NULL,
  mode INTEGER NOT NULL,
  ctime timestamp default 'now',
  utime timestamp default 'now',
  level INTEGER NOT NULL
);

履歴テーブル

user_info

CREATE TABLE user_info (
  uid INTEGER PRIMARY KEY,
  name TEXT NOT NULL UNIQUE
);

question_log

nc/ni は nc1/ni1 に相当.

CREATE TABLE question_log (
  qid INTEGER NOT NULL REFERENCES question(qid),
  uid INTEGER NOT NULL REFERENCES user_info(uid),
  mid INTEGER NOT NULL,
  nc INTEGER NOT NULL,
  ni INTEGER NOT NULL,
  PRIMARY KEY(qid,uid,mid)
);
潟若若菴遵

トップ   編集 凍結 差分 バックアップ 添付 複製 名前変更 リロード   新規 一覧 検索 最終更新   ヘルプ   最終更新のRSS
Last-modified: 2005-04-06 (水) 01:38:42