sql 按照日期排序,同一日期自动从1开始编号。

发布网友发布时间：2022-04-11 03:39

我来回答

共3个回答

热心网友时间：2022-04-11 05:09

oracle 数据库：

排序使用分析函数： row_number() OVER ()

SELECT

row_number() OVER (partition BY t.trandate ORDER BY t.tranamt) "NO",

t.trandate,

nvl(t.tranamt,'小计'),

DECODE( COUNT(*),'1','', COUNT(*)) "计数",

decode(SUM(to_number(t.tranamt)),to_number(t.tranamt),'',SUM(to_number(t.tranamt)))"小计"

FROM testtable t GROUP BY rollup (trandate,tranamt) ;

热心网友时间：2022-04-11 06:27

环境：Oracle 11g

SELECT CASE

WHEN COUNT=1 THEN trandate

WHEN COUNT > 1 AND trandate IS NULL THEN 'GLOBAL TOTAL '

WHEN COUNT > 1 AND trandate IS NOT NULL THEN 'TOTAL '||COUNT

END AS trandate,tranamt FROM

(

SELECT count(trandate) COUNT,

trandate,SUM(tranamt) tranamt FROM TESTTABLE GROUP BY ROLLUP(trandate,tranamt)

) t

热心网友时间：2022-04-11 08:01

我的数据库,希望能帮到你
id trandate tranamt
1 2012-10-19 100
2 2012-10-16 200
3 2012-10-19 300
4 2012-10-18 120
5 2012-10-18 100
用SQL语句：SELECT * FROM `test` order by `trandate` desc,`tranamt`asc可以查询出这样的结果
id trandate tranamt
1 2012-10-19 100
3 2012-10-19 300
5 2012-10-18 100
4 2012-10-18 120
2 2012-10-16 200
用SQL语句SELECT count(id),`trandate`,sum(tranamt) FROM `test` GROUP BY `trandate`
count( id )trandatesum(tranamt)
1 2012-10-16 200
2 2012-10-18 220
2 2012-10-19 400