나무정원

-- row_number()로 row별 1번~순서 매겨서 tmp1 테이블에 저장

create table tmp1  as

select (row_number() over()) as rn, col3

from

(

     select distinct(b.col3) as col3

     from table1 a

             left join 

             table2 b

     on a.col1 = b.col2

      where a.col2='Y'

      and b.col3 is not null

);

result 
select * from tmp1;

• input_text – This defines the input text string, which will be decoded.
• type_text – This defines the in which the input text we expect to be decoded.

---- 연령대별 집계후 더하기 ( age10 / age 20 ~ )

select sum(decode(result,'00',1)) as age00,

   sum(decode(result,'10',1)) as age10,

   sum(decode(result,'20',1)) as age20,

   sum(decode(result,'30',1)) as age30,

   sum(decode(result,'40',1)) as age40,

   sum(decode(result,'50',1)) as age50,

   sum(decode(result,'60',1)) as age60,

   sum(decode(result,'70',1)) as age70,

   sum(decode(result,'80',1)) as age80,

   sum(decode(result,'90',1)) as age90,

   sum(decode(result,'100',1)) as age100,

   sum(decode(result,'NULL','1')) as ageNull    

from 

(

select case when (p.age >=1) and  (p.age<10) then '00'

     when (p.age >=10) and (p.age<20) then '10'

     when (p.age >=20) and (p.age<30) then '20'

     when (p.age >=30) and (p.age<40) then '30'

     when (p.age >=40) and (p.age<50) then '40'

     when (p.age >=50) and (p.age<60) then '50'

     when (p.age >=60) and (p.age<70) then '60'

     when (p.age >=70) and (p.age<80) then '70'

     when (p.age >=80) and (p.age<90) then '80'

     when (p.age >=99) and (p.age<100) then '90'

     when (p.age <=100) then '100'

     else 'NULL' end as result

from table1 u

left join

table2 p

on u.c1=p.c1

where u.c3 > 0

);

result

-- 특정 버전별 (OS 버전별 구해서 sum)

select   sum(decode(b.os_type||'|'||substring(b.os_version,1,2),'ios|10', 1)) as iOs10

         ,sum(decode(b.os_type||'|'||substring(b.os_version,1,2),'ios|11', 1)) as iOs11

         ,sum(decode(b.os_type||'|'||substring(b.os_version,1,2),'ios|12', 1)) as iOs12

         ,sum(decode(b.os_type||'|'||substring(b.os_version,1,2),'ios|13', 1)) as iOs13

         ,sum(decode(b.os_type||'|'||substring(b.os_version,1,2),'ios|14', 1)) as iOs14

         ,sum(decode(b.os_type||'|'||substring(b.os_version,1,2),'ios|15', 1)) as iOs15

  from table1 a

          left join 

          table2 b

          on a.c1 = b.c1

where -- b.c2_date  >= now() - interval '1 months' 

     a.c3 > 0

   limit 1;

result

--이벤트 (코드별 type1~6,기타로 구분하기)

select a.id, a.title, decode(b.ingrs,'INGRS-MATO','type1','INGRS-MAMI','type2','INGRS-LNB1','type3','INGRS-FAML','type4','INGRS-STOR','type5','INGRS-PUSH','type6','기타') as gubun, a.reg_date, count(1)  

from familybox.table1 a, familybox.table2 b

where a.col1 = 'Y'

and a.col2 >= '2021-01-01 00:00:00'

and a.col3=b.col3

group by a.ed, a.title, b.ingrs, a.reg_date

order by a.reg_date, gubun asc;

result

https://programmers.co.kr/learn/courses/30/lessons/77487

이 서비스에서는 공간을 둘 이상 등록한 사람을 "헤비 유저"라고 부릅니다. 헤비 유저가 등록한 공간의 정보를 아이디 순으로 조회하는 SQL문을 작성해주세요.

-- 코드를 입력하세요
select p.id, p.name, p.host_id
from places p
     ,(SELECT host_id
         from places
     group by host_id
     having count(host_id)>1
     order by 1)sub1
where p.host_id=sub1.host_id


id name host_id
4431977 BOUTIQUE STAYS - Somerset Terrace, Pet Friendly 760849
5194998 BOUTIQUE STAYS - Elwood Beaches 3, Pet Friendly 760849
16045624 Urban Jungle in the Heart of Melbourne 30900122
17810814 Stylish Bayside Retreat with a Luscious Garden 760849
22740286 FREE PARKING - The Velvet Lux in Melbourne CBD 30900122