时间戳

首页 > 解决方案 > 如何以自定义格式检索数据

sql - 如何以自定义格式检索数据

问题描述

  1. 我检索到一些数据如下（图 01）。

我使用了这个查询：

SELECT TOP (3) no, co, cdate, year 
FROM main_backup 
WHERE (no = 41505) 
ORDER BY cdate DESC

图片 01

  1. 但我想要那个，就像下面的这种类型（图片02）

图片 02

标签： sqlsql-server

解决方案

您可以使用条件聚合和窗口函数：

select no,
       max(case when seqnum = 1 then total end) as total_1,
       max(case when seqnum = 1 then cdate end) as date_1,
       max(case when seqnum = 2 then total end) as total_2,
       max(case when seqnum = 2 then cdate end) as date_2,
       max(case when seqnum = 3 then total end) as total_3,
       max(case when seqnum = 3 then cdate end) as date_3
from (select t.*,
             row_number() over (partition by no order by cdate desc) as seqnum
      from t
     ) t
group by no

推荐阅读