叮~您有一封国庆SQL闯关邀请函,请查收
-
欢度国庆～SQL挑战邀请活动开始啦！
即日起至 10 月 8 日，闯关成功就有机会获得定制礼品～
第一关
根据生成的数据，构造一条SQL，输出结果为：国庆快乐
-- 生成数据的SQL语句 CREATE TABLE level1(attr text); insert into level1 values('节日快乐，欢度国庆');第二关
根据生成的数据，用一条SQL语句，生成国庆快乐的柱状图，柱状图所表示的“国庆快乐”的字数要与实际生成的数据保持一致：
-- 生成数据的SQL语句 CREATE TABLE level2(attr text); insert into level2 SELECT attr from (select generate_series(1, 40), substring('国庆快乐' from (random()*3)::int+1 for 1) attr) as t;
命令行下的效果：
chart --------------------- 14 | 快 13 | 快 12 | 庆 快 11 | 庆 快 10 | 庆 快 09 | 庆 快 08 | 国 庆 快 07 | 国 庆 快 06 | 国 庆 快 05 | 国 庆 快 乐 04 | 国 庆 快 乐 03 | 国 庆 快 乐 02 | 国 庆 快 乐 01 | 国 庆 快 乐 ------------------- 2022-10-01MemFire Cloud SQL编辑器中的效果：
参与方式
活动期间，只要在论坛活动帖下方评论区贴出答案就算参加活动。注意，截图需为在MemFire Cloud SQL编辑器中的效果。
条件 奖品 闯过一关 读写礼包一份 闯过两关 数据线或超大容量马克杯一个 点赞数最高者 文化衫一件 管理员届时将核对账号信息，重复答案以优先提交者为首选
活动结束后 3 个工作日内公布获奖名单部分周边展示
*活动最终解释权归「MemFireDB」所有
-
第一关
select concat(right(attr,2),right(left(attr,4),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 union all 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;
-
第一关
select concat(substring(attr, 8, 2), substring(attr, 3, 2)) from level1;
-
第一关：
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;验证:
