데이터베이스 질문과 답변

특정값들은 변경이 되지 않는사항.. 0 1 842

by 오지현 [SQL Query] [2018.03.08 14:19:47]

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
WITH t AS
(
SELECT '2000152001' chitemseq, '326' chitem1, '284' chitem2, '203' chitem3
UNION ALL SELECT '2000152002',       '40',       '70',       '20'
UNION ALL SELECT '2000152003', '5.39E+07', '1.85E+08', '1.17E+08'
UNION ALL SELECT '2000152003', '4.00E+05', '8.01E+05', '4.82E+05'
UNION ALL SELECT '2000152003', '6.95E+05', '8.01E+05', '4.82E+05'
)
SELECT chitemseq
     , chitem1, chitem2, chitem3
     , x
     , CASE chitemseq
       WHEN '2000152001' THEN CAST(CAST(x AS NUMERIC(12, 1)) AS VARCHAR)
       WHEN '2000152002' THEN CAST(CAST(x AS NUMERIC(12, 0)) AS VARCHAR)
       WHEN '2000152003' THEN REPLACE(ROUND(x, -5), 'e+0', 'E+')
        END z
  FROM (SELECT chitemseq
             , chitem1, chitem2, chitem3
             , ( CAST(chitem1 AS FLOAT)
               + CAST(chitem2 AS FLOAT)
               + CAST(chitem3 AS FLOAT)
               ) / 3 AS x
          FROM t
        ) a
;

 

by 마농 [2018.03.08 15:36:05]
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
WITH t AS
(
SELECT '2000152001' chitemseq, '326' chitem1, '284' chitem2, '203' chitem3
UNION ALL SELECT '2000152002',       '40',       '70',       '20'
UNION ALL SELECT '2000152003', '5.39E+07', '1.85E+08', '1.17E+08'
UNION ALL SELECT '2000152003', '4.00E+06', '8.01E+05', '4.82E+05'
UNION ALL SELECT '2000152003', '6.95E+05', '8.01E+05', '4.82E+05'
)
SELECT chitemseq
     , chitem1, chitem2, chitem3
     , x
     , y
     , CASE chitemseq
       WHEN '2000152001' THEN CAST(CAST(x AS NUMERIC(12, 1)) AS VARCHAR)
       WHEN '2000152002' THEN CAST(CAST(x AS NUMERIC(12, 0)) AS VARCHAR)
       WHEN '2000152003' THEN CONCAT( CAST(x / POWER(10, y) AS NUMERIC(3,2))
                                    , 'E+'
                                    , RIGHT(CONCAT('0', y), 2)
                                    )
        END z
  FROM (SELECT chitemseq
             , chitem1, chitem2, chitem3
             , ( CAST(chitem1 AS FLOAT)
               + CAST(chitem2 AS FLOAT)
               + CAST(chitem3 AS FLOAT)
               ) / 3 AS x
             , FLOOR(LOG10(
               ( CAST(chitem1 AS FLOAT)
               + CAST(chitem2 AS FLOAT)
               + CAST(chitem3 AS FLOAT)
               ) / 3)) AS y
          FROM t
        ) a
;

 

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입