[SQL與PHP] SQL語法大全與蒐集

gswdkimo

以下列出我工作上常用到的sql語法

==== postgre sql ====

===  將name一樣的集合起來，再依照 count欄位 取前60筆資料  ===
select name, sum(totalcount) as sumtotal from userinfo group by name order by sumtotal DESC limit 60 offset 0

limit 就是show出前60筆   而offset 可以濾掉你設定的前面幾筆

===   用psql抓現在unix_timestamp   ===
select floor(extract(epoch FROM now()));



===   用psql 抓現在欄位是空白的  ===
select * from book where bookname is null;



===  把這五個欄位 合併起來  看有無重覆  ===
select count(*) as abc from userinfo group by sn, id, edge, name, birth having count(*) >1



===  針對某一欄位, 找次數超過100次的  ===
select name from userinfo group by name having count(*) >100