데이터베이스 퀴즈 및 튜닝 문제
오랜만에 퀴즈요~^^*
0
12
5,134
by 박종정 [2008.08.04 17:40:35]
아~ 넘 오랜만에 왔네요.ㅎㅎ
간단한 퀴즈 하나 내고 후딱 사라지겠습니다~
10*10의 칸에 랜덤으로 POINT가 생기고 그 POINT를
기점으로 N 만큼의 두께가 나오게 하는 겁니다~
흠냐...음..... CONNECT BY 나 COPY_T는 한번만 사용하세요!
[그림] 첫번째와 두번째는 N=1, POINT는 각각 74, 7 이고
[그림] 세번째와 네번째는 N=2, POINT는 각각 24, 100 입니다.
by 노유성
[2008.08.05 22:01:18]
으흠...아직 초짜인 저는 패스...ㅠ
by 마농
[2008.08.08 13:07:31]
SELECT 'y' || y yx
, MIN(DECODE(x, 1,no)) x1
, MIN(DECODE(x, 2,no)) x2
, MIN(DECODE(x, 3,no)) x3
, MIN(DECODE(x, 4,no)) x4
, MIN(DECODE(x, 5,no)) x5
, MIN(DECODE(x, 6,no)) x6
, MIN(DECODE(x, 7,no)) x7
, MIN(DECODE(x, 8,no)) x8
, MIN(DECODE(x, 9,no)) x9
, MIN(DECODE(x,10,no)) x10
FROM
(
SELECT x, y
, CASE WHEN x BETWEEN x1 AND x2
AND y BETWEEN y1 AND y2
THEN rn ELSE null END no
FROM
(
SELECT rn
, CEIL(rn/10) y
, DECODE(MOD(rn,10),0,10,MOD(rn,10)) x
, CEIL(p/10) - n y1
, CEIL(p/10) + n y2
, DECODE(MOD(p,10),0,10,MOD(p,10)) - n x1
, DECODE(MOD(p,10),0,10,MOD(p,10)) + n x2
FROM (SELECT ROWNUM rn FROM dual CONNECT BY LEVEL <= 100)
, (SELECT 1 n, 74 p FROM dual)
)
)
GROUP BY y
ORDER BY y
, MIN(DECODE(x, 1,no)) x1
, MIN(DECODE(x, 2,no)) x2
, MIN(DECODE(x, 3,no)) x3
, MIN(DECODE(x, 4,no)) x4
, MIN(DECODE(x, 5,no)) x5
, MIN(DECODE(x, 6,no)) x6
, MIN(DECODE(x, 7,no)) x7
, MIN(DECODE(x, 8,no)) x8
, MIN(DECODE(x, 9,no)) x9
, MIN(DECODE(x,10,no)) x10
FROM
(
SELECT x, y
, CASE WHEN x BETWEEN x1 AND x2
AND y BETWEEN y1 AND y2
THEN rn ELSE null END no
FROM
(
SELECT rn
, CEIL(rn/10) y
, DECODE(MOD(rn,10),0,10,MOD(rn,10)) x
, CEIL(p/10) - n y1
, CEIL(p/10) + n y2
, DECODE(MOD(p,10),0,10,MOD(p,10)) - n x1
, DECODE(MOD(p,10),0,10,MOD(p,10)) + n x2
FROM (SELECT ROWNUM rn FROM dual CONNECT BY LEVEL <= 100)
, (SELECT 1 n, 74 p FROM dual)
)
)
GROUP BY y
ORDER BY y
by 뀨
[2008.08.13 11:24:42]
헉...... 문제 이해도 못하겠네요~~
by 김동욱
[2008.08.13 16:56:47]
오랜만에 들어와 보니..잼있는 것이..ㅋㅋ
구석진 곳이 아니라면 n*2+1의 검색 과 시작은 point -(n*11) <n은 9를 넘지 않는다 가정..칸이 없으니..> 아.. 회사라서.. 바빠서..나중에..ㅋㅋ
구석진 곳이 아니라면 n*2+1의 검색 과 시작은 point -(n*11) <n은 9를 넘지 않는다 가정..칸이 없으니..> 아.. 회사라서.. 바빠서..나중에..ㅋㅋ
by 손님
[2008.08.13 18:41:35]
select 'Y' || (col+1) XY,
max(case when num =1 and check_key > 0 then check_key end) x1,
max(case when num =2 and check_key > 0 then check_key end) x2,
max(case when num =3 and check_key > 0 then check_key end) x3,
max(case when num =4 and check_key > 0 then check_key end) x4,
max(case when num =5 and check_key > 0 then check_key end) x5,
max(case when num =6 and check_key > 0 then check_key end) x6,
max(case when num =7 and check_key > 0 then check_key end) x7,
max(case when num =8 and check_key > 0 then check_key end) x8,
max(case when num =9 and check_key > 0 then check_key end) x9,
max(case when num =10 and check_key > 0 then check_key end) x10
from (
select col,num, case when abs(col - floor(74/10)) <= 1 and abs(num - mod(74,10)) <= 1 then col*10 + num end check_key
from
(
select (level-1) col
from dual
connect by level <= 10
) a, copy_table b
where num <= 10
) group by col
max(case when num =1 and check_key > 0 then check_key end) x1,
max(case when num =2 and check_key > 0 then check_key end) x2,
max(case when num =3 and check_key > 0 then check_key end) x3,
max(case when num =4 and check_key > 0 then check_key end) x4,
max(case when num =5 and check_key > 0 then check_key end) x5,
max(case when num =6 and check_key > 0 then check_key end) x6,
max(case when num =7 and check_key > 0 then check_key end) x7,
max(case when num =8 and check_key > 0 then check_key end) x8,
max(case when num =9 and check_key > 0 then check_key end) x9,
max(case when num =10 and check_key > 0 then check_key end) x10
from (
select col,num, case when abs(col - floor(74/10)) <= 1 and abs(num - mod(74,10)) <= 1 then col*10 + num end check_key
from
(
select (level-1) col
from dual
connect by level <= 10
) a, copy_table b
where num <= 10
) group by col
by 손님
[2008.08.13 18:53:54]
and check_key > 0 없어도 됩니다
by 손님
[2008.08.19 12:51:12]
select 'Y' || col XY,
max(case when num =0 then check_key end) x1,
max(case when num =1 then check_key end) x2,
max(case when num =2 then check_key end) x3,
max(case when num =3 then check_key end) x4,
max(case when num =4 then check_key end) x5,
max(case when num =5 then check_key end) x6,
max(case when num =6 then check_key end) x7,
max(case when num =7 then check_key end) x8,
max(case when num =8 then check_key end) x9,
max(case when num =9 then check_key end) x10
from (
select floor(col/10)+1 col,mod(col,10) num, case when abs(floor(col/10) - floor((cen - 1) / 10)) <= ar
and abs(mod(col,10) - mod((cen - 1) ,10)) <= ar then col+1
end check_key , n
from
(
select (level-1) col
from dual
connect by level <= 100
) a, (
select 1 n, 74 cen, 1 ar from dual UNION
select 2 n, 7 cen, 1 ar from dual UNION
select 3 n, 24 cen, 2 ar from dual UNION
select 4 n, 100 cen, 2 ar from dual
) b
) group by n,col
max(case when num =0 then check_key end) x1,
max(case when num =1 then check_key end) x2,
max(case when num =2 then check_key end) x3,
max(case when num =3 then check_key end) x4,
max(case when num =4 then check_key end) x5,
max(case when num =5 then check_key end) x6,
max(case when num =6 then check_key end) x7,
max(case when num =7 then check_key end) x8,
max(case when num =8 then check_key end) x9,
max(case when num =9 then check_key end) x10
from (
select floor(col/10)+1 col,mod(col,10) num, case when abs(floor(col/10) - floor((cen - 1) / 10)) <= ar
and abs(mod(col,10) - mod((cen - 1) ,10)) <= ar then col+1
end check_key , n
from
(
select (level-1) col
from dual
connect by level <= 100
) a, (
select 1 n, 74 cen, 1 ar from dual UNION
select 2 n, 7 cen, 1 ar from dual UNION
select 3 n, 24 cen, 2 ar from dual UNION
select 4 n, 100 cen, 2 ar from dual
) b
) group by n,col
by 미노
[2008.08.25 08:40:24]
헉 이게 뭐랍니까? 초보에 통곡..ㅡㅡㅋ
by 슈르루까
[2008.10.18 16:18:37]
select 'Y1' as yx, '' as x1, '' as x2, '' as x3, '' as x4, '' as x5, '' as x6, '' as x7, '' as x8, '' as x9, '' as x10
from dual
union all
select 'Y2' as yx, '' as x1, '' as x2, '' as x3, '' as x4, '' as x5, '' as x6, '' as x7, '' as x8, '' as x9, '' as x10
from dual
union all
select 'Y3' as yx, '' as x1, '' as x2, '' as x3, '' as x4, '' as x5, '' as x6, '' as x7, '' as x8, '' as x9, '' as x10
from dual
union all
select 'Y4' as yx, '' as x1, '' as x2, '' as x3, '' as x4, '' as x5, '' as x6, '' as x7, '' as x8, '' as x9, '' as x10
from dual
union all
select 'Y5' as yx, '' as x1, '' as x2, '' as x3, '' as x4, '' as x5, '' as x6, '' as x7, '' as x8, '' as x9, '' as x10
from dual
union all
select 'Y6' as yx, '' as x1, '' as x2, '' as x3, '' as x4, '' as x5, '' as x6, '' as x7, '' as x8, '' as x9, '' as x10
from dual
union all
select 'Y7' as yx, '' as x1, '' as x2, '63' as x3, '64' as x4, '65' as x5, '' as x6, '' as x7, '' as x8, '' as x9, '' as x10
from dual
union all
select 'Y8' as yx, '' as x1, '' as x2, '73' as x3, '74' as x4, '75' as x5, '' as x6, '' as x7, '' as x8, '' as x9, '' as x10
from dual
union all
select 'Y9' as yx, '' as x1, '' as x2, '83' as x3, '84' as x4, '85' as x5, '' as x6, '' as x7, '' as x8, '' as x9, '' as x10
from dual
union all
select 'Y10' as yx, '' as x1, '' as x2, '' as x3, '' as x4, '' as x5, '' as x6, '' as x7, '' as x8, '' as x9, '' as x10
from dual
-- ㅈㅅ 학교 다닐때 부터 수학은 이렇게 했어욤 ㅠ
from dual
union all
select 'Y2' as yx, '' as x1, '' as x2, '' as x3, '' as x4, '' as x5, '' as x6, '' as x7, '' as x8, '' as x9, '' as x10
from dual
union all
select 'Y3' as yx, '' as x1, '' as x2, '' as x3, '' as x4, '' as x5, '' as x6, '' as x7, '' as x8, '' as x9, '' as x10
from dual
union all
select 'Y4' as yx, '' as x1, '' as x2, '' as x3, '' as x4, '' as x5, '' as x6, '' as x7, '' as x8, '' as x9, '' as x10
from dual
union all
select 'Y5' as yx, '' as x1, '' as x2, '' as x3, '' as x4, '' as x5, '' as x6, '' as x7, '' as x8, '' as x9, '' as x10
from dual
union all
select 'Y6' as yx, '' as x1, '' as x2, '' as x3, '' as x4, '' as x5, '' as x6, '' as x7, '' as x8, '' as x9, '' as x10
from dual
union all
select 'Y7' as yx, '' as x1, '' as x2, '63' as x3, '64' as x4, '65' as x5, '' as x6, '' as x7, '' as x8, '' as x9, '' as x10
from dual
union all
select 'Y8' as yx, '' as x1, '' as x2, '73' as x3, '74' as x4, '75' as x5, '' as x6, '' as x7, '' as x8, '' as x9, '' as x10
from dual
union all
select 'Y9' as yx, '' as x1, '' as x2, '83' as x3, '84' as x4, '85' as x5, '' as x6, '' as x7, '' as x8, '' as x9, '' as x10
from dual
union all
select 'Y10' as yx, '' as x1, '' as x2, '' as x3, '' as x4, '' as x5, '' as x6, '' as x7, '' as x8, '' as x9, '' as x10
from dual
-- ㅈㅅ 학교 다닐때 부터 수학은 이렇게 했어욤 ㅠ
by 미니
[2008.12.17 11:23:43]
이해도 못했어요..ㅡㅡ 이런...
by 손님
[2009.02.25 18:09:34]
슈르루까님 때문에 사무실서 대폭소.. ㅋㅋㅋㅋ
by 손님
[2013.01.10 17:02:11]
SELECT 'y' || A.height AS xy
,MIN( DECODE( A.width, 1, LABEL ) ) AS x1
,MIN( DECODE( A.width, 2, LABEL ) ) AS x2
,MIN( DECODE( A.width, 3, LABEL ) ) AS x3
,MIN( DECODE( A.width, 4, LABEL ) ) AS x4
,MIN( DECODE( A.width, 5, LABEL ) ) AS x5
,MIN( DECODE( A.width, 6, LABEL ) ) AS x6
,MIN( DECODE( A.width, 7, LABEL ) ) AS x7
,MIN( DECODE( A.width, 8, LABEL ) ) AS x8
,MIN( DECODE( A.width, 9, LABEL ) ) AS x9
,MIN( DECODE( A.width, 10, LABEL ) ) AS x10
FROM (
SELECT CASE WHEN height BETWEEN ran_height - :num AND ran_height + :num
AND width BETWEEN ran_width - :num AND ran_width + :num
THEN LABEL
END LABEL, height, width
FROM (
SELECT LABEL , height, width
,SUM(DECODE( ran_val, 1, height, 0 )) OVER ( ORDER BY ran_val desc ) AS ran_height
,SUM(DECODE( ran_val, 1, width, 0 )) OVER ( ORDER BY ran_val desc ) AS ran_width
FROM (
SELECT rn AS LABEL
,ran_val AS ran_val
,ntile(10) OVER ( ORDER BY x.rn ) AS height
,ROW_NUMBER() OVER (PARTITION BY CEIL(RN/10) ORDER BY RN) WIDTH
) 버튼을 클릭하여 작성 하시면 됩니다.