CREATE?TABLE?table1?(
??id???INT,
??name?VARCHAR(8),
??createdate??DATE
);
INSERT?INTO?table1
SELECT??1,?'name1',??TO_DATE('2013-12-12',?'YYYY-MM-DD')??UNION??ALL
SELECT??2,?'name2',??TO_DATE('2013-12-13',?'YYYY-MM-DD')??UNION??ALL
SELECT??3,?'name2',??TO_DATE('2013-12-14',?'YYYY-MM-DD')??UNION??ALL
SELECT??4,?'name2',??TO_DATE('2013-12-15',?'YYYY-MM-DD')??UNION??ALL
SELECT??5,?'name2',??TO_DATE('2013-12-15',?'YYYY-MM-DD')??UNION??ALL
SELECT??6,?'name2',??TO_DATE('2013-12-12',?'YYYY-MM-DD');
SELECT?DISTINCT
??(SELECT?COUNT(1)?FROM??table1?t2?WHERE?t1.createdate?>=?t2.createdate)?AS?count,
??createdate??
FROM
??table1??t1
ORDER?BY
??createdate;
?count?|?createdate
-------+------------
?????2?|?2013-12-12
?????3?|?2013-12-13
?????4?|?2013-12-14
?????6?|?2013-12-15
(4?行记录)
postgreqsql中日期值可以和interval直接做加减运算。所以,只要把年龄转换成按年的interval值,就可以运算出结果了。示例:
select?dv,?age,?dv?-?(age?||?'?year')::interval?as?nd??
from?(
??select?'2014-10-21'::date?as?dv,?2?as?age
)?s;
用户登录
还没有账号?立即注册
用户注册
投稿取消
文章分类: |
|
还能输入300字
上传中....