叮~您有一封国庆SQL闯关邀请函,请查收
-
第一关：
select concat(right(attr, 2), substring(attr from 3 for 2)) from level1;
第二关：
with tmp as (select num, string_agg(content, ' ') str from (select num, array_to_string(array_agg(concat(guo, qing, kuai, le)), '') as content from (select row_number() over (PARTITION BY attr) num, CASE attr WHEN '国' THEN '国' ELSE '' END guo, CASE attr WHEN '庆' THEN '庆' ELSE '' END qing, CASE attr WHEN '快' THEN '快' ELSE '' END kuai, CASE attr WHEN '乐' THEN '乐' ELSE '' END le from level2) as rn group by num, guo, qing, kuai, le order by num, guo desc, qing desc, kuai desc, le desc) as nc group by num order by num) select ' chart ' chart union all select '-------------------' chart union all select chart from (select format('%2s | %s |', lpad(concat(tmp.num), 2, '0'), concat(CASE WHEN position('国' in str) = 0 THEN ' ' ELSE (CASE WHEN position('庆' in str) <> 0 or position('快' in str) <> 0 or position('乐' in str) <> 0 THEN '' ELSE str END) END, CASE WHEN position('庆' in str) = 0 THEN ' ' ELSE (CASE WHEN position('快' in str) <> 0 or position('乐' in str) <> 0 THEN '' ELSE str END) END, CASE WHEN position('快' in str) = 0 THEN ' ' ELSE (CASE WHEN position('乐' in str) <> 0 THEN '' ELSE str END) END, CASE WHEN position('乐' in str) = 0 THEN ' ' ELSE str END)) chart from tmp order by num desc) as tc union all select '-------------------' chart union all select ' 2022-10-01 ' chart;
-
@aaaa果
1.SELECT overlay(attr placing '' from 1 for 7)||substring(attr from 3 for 2) from level1;SELECT ' chart ' AS chart UNION ALL SELECT '--------------------' AS chart UNION ALL SELECT chart FROM ( SELECT concat (LPAD(js :: TEXT,2,'0'),' ','|',' ',COALESCE (MIN (guo),' '),' ',COALESCE (MIN (qing),' '),' ',COALESCE (MIN (kuai),' '),' ',COALESCE (MIN (lei),' '),' ') AS chart,LPAD(js :: TEXT,2,'0') seNo FROM ( SELECT ROW_NUMBER () OVER (PARTITION BY attr ORDER BY attr) js,CASE WHEN attr='国' THEN '国' END guo,CASE WHEN attr='庆' THEN '庆' END qing,CASE WHEN attr='快' THEN '快' END kuai,CASE WHEN attr='乐' THEN '乐' END lei FROM level2) tmp GROUP BY js ORDER BY seNo DESC) tmp UNION ALL SELECT '--------------------' UNION ALL SELECT ' 2022-10-01 ' -
-
*第一关
SELECT concat(substr(attr,8,2),substr(attr,3,2)) str FROM LEVEL1-
-
第二关
SELECT ' chart ' AS chart UNION ALL SELECT '--------------------' AS chart UNION ALL SELECT chart FROM ( SELECT concat (LPAD(js :: TEXT,2,'0'),' ','|',' ',COALESCE (MIN (gou),' '),' ',COALESCE (MIN (qing),' '),' ',COALESCE (MIN (kuai),' '),' ',COALESCE (MIN (lei),' '),' ') AS chart,LPAD(js :: TEXT,2,'0') seNo FROM ( SELECT ROW_NUMBER () OVER (PARTITION BY attr ORDER BY attr) js,CASE WHEN attr='国' THEN '国' END gou,CASE WHEN attr='庆' THEN '庆' END qing,CASE WHEN attr='快' THEN '快' END kuai,CASE WHEN attr='乐' THEN '乐' END lei FROM level2) tmp GROUP BY js ORDER BY seNo DESC) tmp UNION ALL SELECT '--------------------' UNION ALL SELECT ' 2022-10-01 '
-
-
第二关
SQL 脚本：
WITH zuguowansui AS (
SELECT concat(LPAD(rn::text, 2, '0'),' ','|',' ',COALESCE(min(guo),' '),' ',COALESCE(min(qing),' '),' ',COALESCE(min(kuai),' '),' ',COALESCE(min(le),' '),' ') as chart,
LPAD(rn::text, 2, '0'),
'|',
min(guo) "国",
min(qing) "庆",
min(kuai) "快",
min(le) "乐"
FROM (SELECT row_number() OVER (PARTITION BY attr ORDER BY attr) rn,
CASE WHEN attr = '国' THEN '国' END guo,
CASE WHEN attr = '庆' THEN '庆' END qing,
CASE WHEN attr = '快' THEN '快' END kuai,
CASE WHEN attr = '乐' THEN '乐' END le
FROM level2) zuguowansui
GROUP BY rn
ORDER BY 1 DESC, 2 DESC, 3 DESC, 4 DESC)select ' chart ' as biaotou
union all
select '--------------------' as fengefuqi
union all
SELECT chart
from zuguowansui
union all
select '--------------------' fengefuzhi
union all
select ' 2022-10-01 ' guoqingjie效果图：
-
-- 第一关
SQL 脚本：
select concat(right(attr,2),SUBSTRING(attr,3,2)) guoqingkuaile from level1
效果图
-
This post is deleted! -
@pekin 确实
-
@rui 哈哈哈哈，这出题人绝对领悟到了羊的精髓了
-
@admin 在 叮~您有一封国庆SQL闯关邀请函,请查收 中说：
2022-10-01第一关：
-- 闯关第一关 select concat(right(attr,2),SUBSTRING(attr,3,2)) from level1效果：
第二关：
-- 闯关第二关 -- 闯关第二关 WITH d AS ( SELECT row_number() OVER (PARTITION BY attr ORDER BY attr) rn, CASE WHEN attr='国' THEN '国'END gou, CASE WHEN attr='庆' THEN '庆' END qing, CASE WHEN attr='快' THEN '快' END kuai, CASE WHEN attr='乐' THEN '乐' END lei FROM level2 ) select concat(lpad(ss."标号",2,'0') ,' | ',ss."国",' ',ss."庆",' ',ss."快",' ',ss."乐")chart from( select cast(row_number() over(order by t."庆" desc) as varchar) "标号",t.* from( SELECT case when min(gou)='国' then '国' else ' ' end "国", case when min(qing)='庆' then '庆' else ' ' end "庆", case when min(kuai)='快' then '快' else ' ' end "快", case when min(lei)='乐' then '乐' else ' ' end "乐" FROM d GROUP BY rn ORDER BY 国 ASC, 庆 ASC, 快 ASC, 乐 ASC)t)ss union all select '-------------------' union all select ' 2022-10-01'效果：
-
@memfiredb-robot 加上了
更新 新要求
验证
code
WITH tmp AS ( SELECT concat(LPAD(rn::text, 2, '0'),' ','|',' ',COALESCE(min(gou),' '),' ',COALESCE(min(qing),' '),' ',COALESCE(min(kuai),' '),' ',COALESCE(min(lei),' '),' ') as chart, LPAD(rn::text, 2, '0'), '|', min(gou) "国", min(qing) "庆", min(kuai) "快", min(lei) "乐" FROM (SELECT row_number() OVER (PARTITION BY attr ORDER BY attr) rn, CASE WHEN attr = '国' THEN '国' END gou, CASE WHEN attr = '庆' THEN '庆' END qing, CASE WHEN attr = '快' THEN '快' END kuai, CASE WHEN attr = '乐' THEN '乐' END lei FROM level2) tmp GROUP BY rn ORDER BY 1 DESC, 2 DESC, 3 DESC, 4 DESC) select ' chart ' as chart union all select '--------------------' as chart union all SELECT chart from tmp union all select '--------------------' union all select ' 2022-10-01 '第一题第二题差这么多 我愿称为关了个关
-
@rui 亲～第二关答案格式详参文章中的“命令行下的效果”以及“MemFire Cloud SQL编辑器中的效果”哦～需要包含 表头+柱状图+左侧标尺+时间 哦～
-
根据生成的数据，构造一条SQL，输出结果为：国庆快乐
select concat(right(attr,2),right(left(attr,4),2)) from level1 ;验证:
根据生成的数据，用一条SQL语句，生成国庆快乐的柱状图，柱状图所表示的“国庆快乐”的字数要与实际生成的数据保持一致：
WITH d AS ( SELECT row_number() OVER (PARTITION BY attr ORDER BY attr) rn, CASE WHEN attr='国' THEN '国' END gou, CASE WHEN attr='庆' THEN '庆' END qing, CASE WHEN attr='快' THEN '快' END kuai, CASE WHEN attr='乐' THEN '乐' END lei FROM level2 ) SELECT min(gou) "国", min(qing) "庆", min(kuai) "快", min(lei) "乐" FROM d GROUP BY rn ORDER BY 1 DESC, 2 DESC, 3 DESC, 4 DESC;验证:
