http://www.postgresql.org/docs/8.4/static/tutorial-window.html
http://www.postgresql.jp/document/8.4/html/tutorial-window.html
http://hibari.2ch.net/test/read.cgi/db/1294641578/401-497
PostgreSQL。
$ eix postgresql-server -cI [I] dev-db/postgresql-server (9.0.4-r1(9.0)@2011年07月04日): PostgreSQL server
user=# CREATE TABLE test001 (id SERIAL, data TEXT);
NOTICE: CREATE TABLEはシリアル列"test001.id"用に暗黙的なシーケンス"test001_id_seq"を作成します。
CREATE TABLE
user=# INSERT INTO test001 (data) VALUES ('aaa');
INSERT 0 1
user=# INSERT INTO test001 (data) VALUES ('bbb');
INSERT 0 1
user=# INSERT INTO test001 (data) VALUES ('ccc');
INSERT 0 1
user=# SELECT * FROM test001 ORDER BY id DESC;
id | data
----+------
3 | ccc
2 | bbb
1 | aaa
(3 行)
user=# SELECT COUNT(*) AS cntall FROM test001;
cntall
--------
3
(1 行)
user=# SELECT *,COUNT(*) OVER() AS cntall FROM test001 ORDER BY id DESC LIMIT 1;
id | data | cntall
----+------+--------
3 | ccc | 3
(1 行)
user=#MySQL。
$ eix -e dev-db/mysql -cI [I] dev-db/mysql (5.1.56@2011年05月09日): A fast, multi-threaded, multi-user SQL database server.
mysql> CREATE TABLE test001 (id SERIAL, data TEXT);
Query OK, 0 rows affected (0.23 sec)
mysql> INSERT INTO test001 (data) VALUES ('aaa');
Query OK, 1 row affected (0.93 sec)
mysql> INSERT INTO test001 (data) VALUES ('bbb');
Query OK, 1 row affected (0.15 sec)
mysql> INSERT INTO test001 (data) VALUES ('ccc');
Query OK, 1 row affected (0.14 sec)
mysql> SELECT * FROM test001 ORDER BY id DESC;
+----+------+
| id | data |
+----+------+
| 3 | ccc |
| 2 | bbb |
| 1 | aaa |
+----+------+
3 rows in set (0.00 sec)
mysql> SELECT COUNT(*) AS cntall FROM test001;
+--------+
| cntall |
+--------+
| 3 |
+--------+
1 row in set (0.00 sec)
mysql> SELECT SQL_CALC_FOUND_ROWS * FROM test001 ORDER BY id DESC LIMIT 1;
+----+------+
| id | data |
+----+------+
| 3 | ccc |
+----+------+
1 row in set (0.00 sec)
mysql> SELECT FOUND_ROWS() AS cntall;
+--------+
| cntall |
+--------+
| 3 |
+--------+
1 row in set (0.00 sec)
mysql>