limit.out

postgresql8.3.4源码,开源数据库

--
-- LIMIT
-- Check the LIMIT/OFFSET feature of SELECT
--
SELECT ''::text AS two, unique1, unique2, stringu1 
		FROM onek WHERE unique1 > 50 
		ORDER BY unique1 LIMIT 2;
 two | unique1 | unique2 | stringu1 
-----+---------+---------+----------
     |      51 |      76 | ZBAAAA
     |      52 |     985 | ACAAAA
(2 rows)

SELECT ''::text AS five, unique1, unique2, stringu1 
		FROM onek WHERE unique1 > 60 
		ORDER BY unique1 LIMIT 5;
 five | unique1 | unique2 | stringu1 
------+---------+---------+----------
      |      61 |     560 | JCAAAA
      |      62 |     633 | KCAAAA
      |      63 |     296 | LCAAAA
      |      64 |     479 | MCAAAA
      |      65 |      64 | NCAAAA
(5 rows)

SELECT ''::text AS two, unique1, unique2, stringu1 
		FROM onek WHERE unique1 > 60 AND unique1 < 63
		ORDER BY unique1 LIMIT 5;
 two | unique1 | unique2 | stringu1 
-----+---------+---------+----------
     |      61 |     560 | JCAAAA
     |      62 |     633 | KCAAAA
(2 rows)

SELECT ''::text AS three, unique1, unique2, stringu1 
		FROM onek WHERE unique1 > 100 
		ORDER BY unique1 LIMIT 3 OFFSET 20;
 three | unique1 | unique2 | stringu1 
-------+---------+---------+----------
       |     121 |     700 | REAAAA
       |     122 |     519 | SEAAAA
       |     123 |     777 | TEAAAA
(3 rows)

SELECT ''::text AS zero, unique1, unique2, stringu1 
		FROM onek WHERE unique1 < 50 
		ORDER BY unique1 DESC LIMIT 8 OFFSET 99;
 zero | unique1 | unique2 | stringu1 
------+---------+---------+----------
(0 rows)

SELECT ''::text AS eleven, unique1, unique2, stringu1 
		FROM onek WHERE unique1 < 50 
		ORDER BY unique1 DESC LIMIT 20 OFFSET 39;
 eleven | unique1 | unique2 | stringu1 
--------+---------+---------+----------
        |      10 |     520 | KAAAAA
        |       9 |      49 | JAAAAA
        |       8 |     653 | IAAAAA
        |       7 |     647 | HAAAAA
        |       6 |     978 | GAAAAA
        |       5 |     541 | FAAAAA
        |       4 |     833 | EAAAAA
        |       3 |     431 | DAAAAA
        |       2 |     326 | CAAAAA
        |       1 |     214 | BAAAAA
        |       0 |     998 | AAAAAA
(11 rows)

SELECT ''::text AS ten, unique1, unique2, stringu1 
		FROM onek
		ORDER BY unique1 OFFSET 990;
 ten | unique1 | unique2 | stringu1 
-----+---------+---------+----------
     |     990 |     369 | CMAAAA
     |     991 |     426 | DMAAAA
     |     992 |     363 | EMAAAA
     |     993 |     661 | FMAAAA
     |     994 |     695 | GMAAAA
     |     995 |     144 | HMAAAA
     |     996 |     258 | IMAAAA
     |     997 |      21 | JMAAAA
     |     998 |     549 | KMAAAA
     |     999 |     152 | LMAAAA
(10 rows)

SELECT ''::text AS five, unique1, unique2, stringu1 
		FROM onek
		ORDER BY unique1 OFFSET 990 LIMIT 5;
 five | unique1 | unique2 | stringu1 
------+---------+---------+----------
      |     990 |     369 | CMAAAA
      |     991 |     426 | DMAAAA
      |     992 |     363 | EMAAAA
      |     993 |     661 | FMAAAA
      |     994 |     695 | GMAAAA
(5 rows)

SELECT ''::text AS five, unique1, unique2, stringu1 
		FROM onek
		ORDER BY unique1 LIMIT 5 OFFSET 900;
 five | unique1 | unique2 | stringu1 
------+---------+---------+----------
      |     900 |     913 | QIAAAA
      |     901 |     931 | RIAAAA
      |     902 |     702 | SIAAAA
      |     903 |     641 | TIAAAA
      |     904 |     793 | UIAAAA
(5 rows)

-- Stress test for variable LIMIT in conjunction with bounded-heap sorting
SELECT
  (SELECT n
     FROM (VALUES (1)) AS x,
          (SELECT n FROM generate_series(1,10) AS n
             ORDER BY n LIMIT 1 OFFSET s-1) AS y) AS z
  FROM generate_series(1,10) AS s;
 z  
----
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
(10 rows)