Life is Struggle

PostgreSQL Sql 筆記

1. 查看執行中 sql 的方法

1

select * from pg_stat_activity

2. base64 encode 欄位 – encode()

1
2
3

select encode(convert_to(fname,'utf-8'), 'base64') as url
  from media
 where media_type in(1,2)

3. 取 hd – &

1

select * from media where fmt & 2 = 2

4. 取出當日的資料 – now()::date, interval ‘1h’ 是指當天的 01:00

1
2
3
4
5
6

select now()::date, media_id, title
  from media
 where uid = '238574878'
   and from = 'Wretch'
   and ctime >= now()::date + interval '1h'
 order by crt_datetime

5. 依月份列出上傳量, 由 2012-10 開始算 – date_trunc()

1
2
3
4
5

select count(mid), date_trunc('month', ctime) as month
  from media
 where ctime >= '2012-10-01 00:00:00'
 group by month
 order by month


6. 依年份列出有在使用的人數 – date_trunc()

1
2
3
4
5
6

select count(uid), date_trunc('year', last_upload_time) as total
  from member
 where (audio_cnt > 0 or video_cnt > 0)
   and used_quota > 0
 group by total
 order by total

7. 檢查 wretch 來的, 是否有重覆的 media – not in, min(), group by

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

select media_id
  from media
 where uid='236335446'
   and media_type in(1,2)
   and from='Wretch'
   and public <> 100
   and mid not in (
     select min(mid)
       from media
      where uid='236335446'
        and media_type in(1,2)
        and from='Wretch'
        and public <> 100
      group by srcpath
)

8. 字串取代 – —row_number() OVER / 在結果加上序號 – replace()

1
2
3
4
5

select replace(fname, '.flv', '.mp4')||row_number() OVER (ORDER BY title)||'.mp4' as cmd
  from media
 where uid = 'xxx'
   and from = 'cb'
 order by title

9.1 去除重覆的資料 (for 抽獎應用) – distinct on

1

select distinct on (fieldname) * from tablename

9.2 去除重覆的資料 (for 抽獎應用) – mysql, 利用 group by

1
2
3

select name, sex, born_year, born_date, mobile, zone, addr, email
  from receipt_reg
 group by mobile

ref : http://www.jinnsblog.com/2013/09/sql-distinct-group-by-for-mysql-postgresql.html


10. mysql 的 rownum

1
2

select distinct mobile, @rownum:=@rownum+1 AS 'rownum', name, sex, born_year, born_date, mobile, zone, addr, email
  from receipt_reg, (SELECT @rownum:=0) r

ref : http://ithelp.ithome.com.tw/question/10136053?tag=ithome.nq


11. 在 navicat 裡啟用 transaction

1
2

begin;
-- 之後的執行的 DML, 都要下 commit; 或 rollback; 才有作用