Re: [SQL ] PostgreSQL 欄位產生
恕刪
: 改成
: select
: to_char
: (
: (select min(date) from YourTable) + (n || ' day')::interval,
: 'yyyy-MM-dd'
: ) as short_mname
: from
: generate_series
: (
: 0,
: (select DATE_PART('day', now() - (select min(date) from YourTable))::int)
: ) n;
: 簡單說就是把固定整數改掉。
如果我理解無誤的話，以上是把我table的日期欄位選出來吧？
但是要做crosstab的話，我是否需要自己寫每一天的日期在select裡呢？
像下面的範例，他也是手寫每個月的欄位進去crosstab的selection
SELECT mthreport.*
FROM
crosstab('SELECT i.item_name::text As row_name, to_char(if.action_date, ''mon'')::text As bucket,
SUM(if.num_used)::integer As bucketvalue
FROM inventory As i INNER JOIN inventory_flow As if
ON i.item_id = if.item_id
AND action_date BETWEEN date ''2007-01-01'' and date ''2007-12-31 23:59''
GROUP BY i.item_name, to_char(if.action_date, ''mon''), date_part(''month'', if.action_date)
ORDER BY i.item_name',
'SELECT to_char(date ''2007-01-01'' + (n || '' month'')::interval, ''mon'') As short_mname
FROM generate_series(0,11) n')
As mthreport(item_name text, jan integer, feb integer, mar integer,
apr integer, may integer, jun integer, jul integer,
aug integer, sep integer, oct integer, nov integer,
dec integer)
範例出自http://tinyurl.com/7vtfqk
但我需要的是每天的結果，這樣的話我是否要手動key in天數
還是有其他方法可以讓我把generate出來的日期轉呈欄位
然後用這些欄位來作selection ?
感謝回文
--
※ 發信站: 批踢踢實業坊(ptt.cc)
◆ From: 24.90.213.192
討論串 (同標題文章)