1. ORACLE
SELECT * FROM TABLE1 WHERE ROWNUM<=N
2. INFORMIX
SELECT FIRST N * FROM TABLE1
3. DB2
SELECT * ROW_NUMBER() OVER(ORDER BY COL1 DESC) AS ROWNUM WHERE ROWNUM<=N
或者
SELECT COLUMN FROM TABLE FETCH FIRST N ROWS ONLY
4. SQL SERVER
SELECT TOP N * FROM TABLE1
5. SYBASE
SET ROWCOUNT N GOSELECT * FROM TABLE1
6. MYSQL
SELECT * FROM TABLE1 LIMIT N
7. FOXPRO
SELECT * TOP N FROM TABLE ORDER BY COLUMN
having 和 where 的区别：
- where 子句中不能有聚组函数(sum,count,avg,max)
- 一般group by 之前用where ， 之后用having。
插入特殊符号，如单引号，单引号会 断掉sql 语句
sql = insert into yourTable(f1,f2) values(100,'abc')
这时一般用两个单引号
replace(sql,"'","''")
& 是 chr(38)
union and union all usage:
SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2
it will show the two tables ** column_name values ** (column_name should be the same) without
duplicate.
SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2
union all will show the duplicate data
http://coolshell.cn/articles/1846.html
2017.2.21 --- left join 优化思考
processing order of the select statement
(Select statement 执行的顺序)：
1.FROM
2.ON
3.JOIN
4.WHERE
5.GROUP BY
6.WITH CUBE or WITH ROLLUP
7.HAVING
8.SELECT
9.DISTINCT
10.ORDER BY
11.TOP
on 在 where 之后执行，说明join 的时候在 on 过滤比在where过滤效率高
select * from a left join b on a.name = b.name and a.group = 1
select * from a left join b on a.name = b.name where a.group = 1
