作者Spake (史派克)
看板Database
标题Re: [SQL ] PostgreSQL 栏位产生
时间Sat Jan 5 02:38:31 2013
恕删
: 改成
: 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