多行转多列 - 我的帆软

多行转多列

在sql里写，根据类型选最大最小值的数值，然后根据选出的最大最小值选对应年月，类型不确定有几种

SQL 二向箔 发布于 2023-10-9 15:23 （编辑于 2023-10-9 16:32）

1min目标场景问卷

立即参与

回答问题关注问题

悬赏：3 F币 + 添加悬赏

提示：增加悬赏、完善问题、追问等操作，可使您的问题被置顶，并向所有关注者发送通知

共4回答

CT1448Lv5初级互助
发布于2023-10-9 15:41

B3: B1[!0]{A1 = $A3 && C1 = $C3}

C3: min(C1[!0;!0]{A1 = $A3})

D3: B1[!0]{A1=$A3 && C1=$E3}

E3: MAX(C1[!0;!0]{A1 = $A3})

举报收起评论(2)

二向箔(提问者) 在sql里能实现吗

2023-10-09 16:31

CT1448 回复二向箔(提问者) with a1 as ( select \'a\' as type1,\'2023-04\' as time,5 as num from dual union all select \'a\' as type1,\'2023-05\' as time,40 as num from dual union all select \'a\' as type1,\'2023-07\' as time,10 as num from dual UNION ALL select \'b\' as type1,\'2023-06\' as time,20 as num from dual union all select \'b\' as type1,\'2023-07\' as time,10 as num from dual union all select \'b\' as type1,\'2023-09\' as time,30 as num from dual ) select tmp1.type1,tmp1.num_min,tmp1.num_max,tmp2.time as time_min,tmp3.time as time_max from (select type1,min(num) as num_min,max(num) as num_max from a1 group by type1) tmp1 join a1 tmp2 on tmp1.type1=tmp2.type1 and tmp1.num_min=tmp2.num join a1 tmp3 on tmp1.type1=tmp3.type1 and tmp1.num_max=tmp3.num

2023-10-09 18:05

Z4u3z1Lv6专家互助
发布于2023-10-9 15:42

WorkBook1.rar

举报收起评论(5)

二向箔(提问者) 在sql里写的话能实现吗

2023-10-09 16:31

Z4u3z1 回复二向箔(提问者) SqlServer？

2023-10-09 16:31

二向箔(提问者) 回复 Z4u3z1 mysql

2023-10-09 16:32

Z4u3z1 回复二向箔(提问者) 抱歉不会

2023-10-09 16:32

二向箔(提问者) 回复 Z4u3z1 没关系，我弄出来啦，还是谢谢你的解答

2023-10-10 09:24

JL98Lv6中级互助
发布于2023-10-9 16:40（编辑于 2023-10-10 09:35）

你这个不用行转列啊，就是求最大值最小值

------------------------------------------------------------------------------

WITH T1 AS (

SELECT 'a' AS LX, '2023-04' AS SJ , 5 AS SZ

UNION ALL

SELECT 'a' AS LX, '2023-05' AS SJ , 40 AS SZ

UNION ALL

SELECT 'a' AS LX, '2023-07' AS SJ , 10 AS SZ

UNION ALL

SELECT 'b' AS LX, '2023-06' AS SJ , 20 AS SZ

UNION ALL

SELECT 'b' AS LX, '2023-07' AS SJ , 10 AS SZ

UNION ALL

SELECT 'b' AS LX, '2023-09' AS SJ , 30 AS SZ

)

SELECT

LX,

MIN(SJ) AS MIN_SJ,

MAX(SJ) AS MAX_SJ,

MIN(SZ) AS MIN_SZ,

MAX(SZ) AS MAX_SZ

FROM

GROUP BY LX

---------------------------------------------------------------------------------

WITH T1 AS (

SELECT 'a' AS LX, '2023-04' AS SJ , 5 AS SZ

UNION ALL

SELECT 'a' AS LX, '2023-05' AS SJ , 40 AS SZ

UNION ALL

SELECT 'a' AS LX, '2023-07' AS SJ , 10 AS SZ

UNION ALL

SELECT 'b' AS LX, '2023-06' AS SJ , 20 AS SZ

UNION ALL

SELECT 'b' AS LX, '2023-07' AS SJ , 10 AS SZ

UNION ALL

SELECT 'b' AS LX, '2023-09' AS SJ , 30 AS SZ

),T2 AS (

SELECT

LX,

-- MIN(SJ) AS MIN_SJ,

-- MAX(SJ) AS MAX_SJ,

MIN(SZ) AS MIN_SZ,

MAX(SZ) AS MAX_SZ

FROM

GROUP BY LX

)

SELECT

T2.LX,

MIN(T2.MIN_SZ) AS MIN_SZ,

MAX(T2.MAX_SZ) AS MAX_SZ,

MIN(T1.SJ) AS MIN_SJ,

MAX(T1.SJ) AS MAX_SJ

FROM T1

RIGHT JOIN T2 ON T1.LX = T2.LX AND (T2.MIN_SZ = T1.SZ OR T2.MAX_SZ = T1.SZ)

GROUP BY T2.LX

举报收起评论(2)

二向箔(提问者) 不是的，我是先取数值最大最小，在根据取出的数值来选对应的年月，所以a类选的最大月份不单纯是月份最大，而是数值最大所对应的月份，就是40对应的月份2023-05，而不是2023-07；类似的b类最小月份不要2023-06而是最小数值10对应的2023-07

2023-10-10 09:23

JL98 回复二向箔(提问者) 那就麻烦一点先算最大最小值，在关联出日期，回答已更新

2023-10-10 09:36

标哥制表Lv2见习互助
发布于2023-10-13 15:59（编辑于 2023-10-13 15:59）

SERVER 测试,建议你的数据写成日期格式,你的这个是文本格式

SELECT

类型

,MIN(时间) as 最小时间

,MAX(时间) AS 最大时间

,MIN(数值) AS 最小数值

,MAX(数值) AS 最大数值

FROM

(

SELECT T1.类型,时间,数值

FROM 数据 T1

INNER JOIN

(Select 类型,MIN(数值) AS 最小数值 FROM 数据 GROUP BY 类型) T2

ON T1.类型=T2.类型

AND T1.数值=T2.最小数值

UNION ALL

SELECT T3.类型,时间,数值

FROM 数据 T3

INNER JOIN

(Select 类型,MAX(数值) AS 最大数值 FROM 数据 GROUP BY 类型) T4

ON T3.类型=T4.类型

AND T3.数值=T4.最大数值

) Z1

GROUP BY 类型

这个是数据源

这个是SQL语句啦

4关注人数

580浏览人数

最后回答于：2023-10-13 15:59

活动推荐更多

热门课程更多

请选择关闭问题的原因

确定取消

提问

悬赏者（1）

撤回悬赏（1）

添加悬赏

请选择关闭问题的原因