sql - head指定行数 - linux取行




SQL-僅選擇前10行? (7)

如何僅選擇查詢的前10個結果?

我想只顯示以下查詢的前10個結果:

SELECT a.names,
         COUNT(b.post_title) AS num
    FROM wp_celebnames a
    JOIN wp_posts b ON INSTR(b.post_title, a.names) > 0
    WHERE b.post_date > DATE_SUB(CURDATE(), INTERVAL 1 DAY)
GROUP BY a.names
ORDER BY num DESC

ANSI SQL答案是FETCH FIRST

SELECT a.names,
         COUNT(b.post_title) AS num
    FROM wp_celebnames a
    JOIN wp_posts b ON INSTR(b.post_title, a.names) > 0
    WHERE b.post_date > DATE_SUB(CURDATE(), INTERVAL 1 DAY)
GROUP BY a.names
ORDER BY num DESC
FETCH FIRST 10 ROWS ONLY

如果你想要包含關係,那麼請先用FETCH FIRST 10 ROWS WITH TIES

要跳過指定的行數,請使用OFFSET ,例如

...
ORDER BY num DESC
OFFSET 20
FETCH FIRST 10 ROWS ONLY

將跳過前20行,然後獲取10行。

由較新版本的Oracle, PostgreSQL ,MS SQL Server,Mimer SQL和DB2等提供支持。


DB2

... FETCH FIRST 10 ROWS ONLY


在MySQL中:

SELECT * FROM `table` LIMIT 0, 10

在SQL Server中,使用:

select top 10 ...

例如

select top 100 * from myTable
select top 100 colA, colB from myTable

在MySQL中,使用:

select ... order by num desc limit 10

火鳥:

SELECT FIRST 10 * FROM MYTABLE

神諭

WHERE ROWNUM <= 10  and whatever_else ;

ROWNUM是一個神奇的變量,它包含每一行的序列號1 .. n


SELECT* from <table name> WHERE rownum <= 10;






sql