mysql 5.6

第一題

select
  sum(if(t.p=1,t.cnt,0)) '1'
  ,sum(if(t.p=2,t.cnt,0)) '2'
  ,sum(if(t.p=3,t.cnt,0)) '3'
  ,sum(if(t.p=4,t.cnt,0)) '4'
  ,sum(if(t.p=5,t.cnt,0)) '5'
  ,sum(if(t.p=6,t.cnt,0)) '6'
  ,sum(if(t.p=7,t.cnt,0)) '7'
  ,sum(if(t.p=8,t.cnt,0)) '8'
  ,sum(if(t.p=9,t.cnt,0)) '9'
  ,sum(if(t.p=0,t.cnt,0)) '0'
from(
  select p,count(1) cnt
  from(
    select p1 p from testdata
    union all
    select p2 p from testdata
    union all
    select p3 p from testdata
  ) _p
  group by p
) t

第二題

select
  pp
  ,sum(if(p=1,1,0)) '1'
  ,sum(if(p=2,1,0)) '2'
  ,sum(if(p=3,1,0)) '3'
  ,sum(if(p=4,1,0)) '4'
  ,sum(if(p=5,1,0)) '5'
  ,sum(if(p=6,1,0)) '6'
  ,sum(if(p=7,1,0)) '7'
  ,sum(if(p=8,1,0)) '8'
  ,sum(if(p=9,1,0)) '9'
  ,sum(if(p=0,1,0)) '0'
from (
  select 'p1'pp, p1 p from testdata
  union all
  select 'p2'pp, p2 p from testdata
  union all
  select 'p3'pp, p3 p from testdata
) t
group by pp

第三題

  -- set @maxRowNum = 20; -- 偷吃步XD
  set @maxRowNum = (select count(1) from testdata limit 1);
  select 
  @maxRowNum-(select max(id) from testdata where p1=1 or p2=1 or p3=1) '1'
  ,@maxRowNum-(select max(id) from testdata where p1=2 or p2=2 or p3=2) '2'
  ,@maxRowNum-(select max(id) from testdata where p1=3 or p2=3 or p3=3) '3'
  ,@maxRowNum-(select max(id) from testdata where p1=4 or p2=4 or p3=4) '4'
  ,@maxRowNum-(select max(id) from testdata where p1=5 or p2=5 or p3=5) '5'
  ,@maxRowNum-(select max(id) from testdata where p1=6 or p2=6 or p3=6) '6'
  ,@maxRowNum-(select max(id) from testdata where p1=7 or p2=7 or p3=7) '7'
  ,@maxRowNum-(select max(id) from testdata where p1=8 or p2=8 or p3=8) '8'
  ,@maxRowNum-(select max(id) from testdata where p1=9 or p2=9 or p3=9) '9'
  ,@maxRowNum-(select max(id) from testdata where p1=0 or p2=0 or p3=0) '0';

太難了啦～
我是新手第 3 級耶，
有沒有幼幼班等級的題目？

話說,
前兩題如果90度轉過來簡單很多...

通常這種題目我會用後端語言跑迴圈,
這直接寫SQL語法太痛苦了 XD

只做第三題

create table it191213 (
  id smallint not null primary key
, p1 smallint not null
, p2 smallint not null
, p3 smallint not null
);

insert into it191213 values
(1, 0, 7, 8),
(2, 1, 5, 3),
(3, 7, 3, 4),
(4, 4, 5, 2),
(5, 0, 1, 6),
(6, 3, 5, 2),
(7, 5, 6, 1),
(8, 0, 5, 4),
(9, 3, 4, 7),
(10, 6, 5, 2);

-----------
-- 使用 lateral join 來循環產生

select n
     , maxid
  from generate_series(0, 9) g(n)
  join lateral 
       (select max(id) as maxid
          from it191213
         where p1 = n
            or p2 = n
            or p3 = n
       ) c on true;

+---+-------+
| n | maxid |
+---+-------+
| 0 |     8 |
| 1 |     7 |
| 2 |    10 |
| 3 |     9 |
| 4 |     9 |
| 5 |    10 |
| 6 |    10 |
| 7 |     9 |
| 8 |     1 |
| 9 |     ¤ |
+---+-------+
(10 rows)

-- 接著繼續加工, 做轉向.還要注意到 null 變為 0, 還有取得最大id

with t1 as (
select n
     , coalesce(maxid, 0) as newid
  from generate_series(0, 9) g(n)
  join lateral 
       (select max(id) as maxid
          from it191213
         where p1 = n
            or p2 = n
            or p3 = n
       ) c on true
), t2 as (
select max(id) as realmax
  from it191213
)
select realmax - sum(newid) filter (where n = 1) as "1"
     , realmax - sum(newid) filter (where n = 2) as "2"
     , realmax - sum(newid) filter (where n = 3) as "3"
     , realmax - sum(newid) filter (where n = 4) as "4"
     , realmax - sum(newid) filter (where n = 5) as "5"
     , realmax - sum(newid) filter (where n = 6) as "6"
     , realmax - sum(newid) filter (where n = 7) as "7"
     , realmax - sum(newid) filter (where n = 8) as "8"
     , realmax - sum(newid) filter (where n = 9) as "9"
     , realmax - sum(newid) filter (where n = 0) as "0"
  from t1
     , t2
 group by realmax;

+---+---+---+---+---+---+---+---+----+---+
| 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9  | 0 |
+---+---+---+---+---+---+---+---+----+---+
| 3 | 0 | 1 | 1 | 0 | 0 | 1 | 9 | 10 | 2 |
+---+---+---+---+---+---+---+---+----+---+