叮~您有一封国庆SQL闯关邀请函,请查收

[wsy]

*第一关

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     '

[joy]

第二关
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

效果图：

[joy]

-- 第一关
SQL 脚本：
select concat(right(attr,2),SUBSTRING(attr,3,2)) guoqingkuaile from level1
效果图

[花殇]

此回复已被删除！

[rui]

@pekin 确实

[pekin]

@rui 哈哈哈哈，这出题人绝对领悟到了羊的精髓了

[caffeine]

@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'

效果：

[rui]

@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     '

第一题第二题差这么多 我愿称为关了个关

[MemFireDB Robot]

@rui 亲～第二关答案格式详参文章中的“命令行下的效果”以及“MemFire Cloud SQL编辑器中的效果”哦～需要包含 表头+柱状图+左侧标尺+时间 哦～

[rui]

根据生成的数据，构造一条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;

验证: