如何根据多列值返回前 2 个值

debugcn 发表于 Dev

泰米尔语巴布语

我有像给定的列--->

"id"| "name"| "hour8"| "hour9"| "hour10"| "hour11"| "hour12"|
 1     a        3.6      4.2      1.3       5.6       2.4
 2     b        0.2      7.6      2.3       4.0       0.5

我想从小时 8、9、10、11、12 的小时数据中提取前 2 个值作为 id 名称。

我是初学者，所以任何帮助或想法表示赞赏。

我的查询只能得到一列。但我需要前 2 列值。

SELECT ID, MAX(maxhour) AS maxhour
FROM ##TestTabletb
UNPIVOT (maxhour FOR Val IN ( hour8, hour9, hour10,hour11, hour12 ) ) AS u
GROUP BY ID, Name

“我希望输出是

"id"| "name"| "hour9"| "hour11"| 
 1     a         4.2     5.6

像这样的东西。

开始

我认为这可能会给你你需要的东西......

    SELECT r.id,
 y.val column1,
 r.maxhour1,
 z.val column2,
 r.maxhour2
 FROM (
SELECT  u.ID, 
MAX(u.maxhour) AS maxhour1, 
MAX(x.maxhour) as maxhour2
FROM ##TestTabletb 
UNPIVOT (maxhour FOR Val IN ( hour8, hour9, hour10,hour11, hour12 ) ) AS u,
     ##TestTabletb 
UNPIVOT (maxhour FOR Val IN ( hour8, hour9, hour10,hour11, hour12 ) ) AS x               
    WHERE u.id = x.id and u.maxhour > x.maxhour 
group by u.ID ) r,
##TestTabletb 
UNPIVOT (maxhour FOR Val IN ( hour8, hour9, hour10,hour11, hour12 ) ) AS y,
##TestTabletb 
UNPIVOT (maxhour FOR Val IN ( hour8, hour9, hour10,hour11, hour12 ) ) AS z
  WHERE r.id = y.id and r.maxhour1 = y.maxhour and
  r.id = z.id and r.maxhour2 = z.maxhour
order by r.id

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。