데이터베이스 질문과 답변

SQL- 조건에 따른 자리수 틀린 값들 표현 0 3 1,096

by 오지현 [2018.02.14 13:31:37]

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
WITH t as (
select '2000152001' chitemseq, '326' chitem1, '284' chitem2, '203' chitem3
union all
select '2000152002' chitemseq, '40' chitem1, '70' chitem2, '20' chitem3
union all
select '2000152003' chitemseq, '5.44E+07' chitem1, '3.66E+07' chitem2, '3.24E+07' chitem3
 
)
 select t.*,
 case     
        WHEN ChItemseq = 2000152001 THEN
              CAST((CAST(ChItem1 AS FLOAT)+CAST(ChItem2 AS FLOAT)+CAST(ChItem3 AS FLOAT) / 3 ) AS numeric(12,1))
        WHEN ChItemseq = 2000152002 THEN
              CAST((CAST(ChItem1 AS FLOAT)+CAST(ChItem2 AS FLOAT)+CAST(ChItem3 AS FLOAT) / 3 ) AS  INT )
       WHEN ChItemseq = 2000152003 THEN                      
                 UPPER(CAST(CAST( ROUND( (CAST(ChItem1 AS FLOAT)+ CAST(ChItem2 AS FLOAT)+ CAST(ChItem3 AS FLOAT)) / 3                             
                ,FLOOR(LOG10((CAST(ChItem1 AS FLOAT)+ CAST(ChItem2 AS FLOAT)+ CAST(ChItem3 AS FLOAT)) / 3)) * -1 + 1)AS FLOAT)                             
              AS VARCHAR))
                       END                     
             FROM  T

 

by 마농 [2018.02.14 14:15:51]
?
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
-- MSSQL --
WITH t AS
(
SELECT '2000152001' chitemseq, '326' chitem1, '284' chitem2, '202' chitem3
UNION ALL SELECT '2000152002',       '40',       '70',       '20'
UNION ALL SELECT '2000152003', '5.44E+07', '3.66E+07', '3.24E+07'
)
SELECT chitemseq
     , chitem1, chitem2, chitem3
     , x
     , y
     , REPLACE(ROUND(x, y), 'e+0', 'E+') z
  FROM (SELECT chitemseq
             , chitem1, chitem2, chitem3
             , ( CAST(chitem1 AS FLOAT)
               + CAST(chitem2 AS FLOAT)
               + CAST(chitem3 AS FLOAT)
               ) / 3 AS x
             , CASE chitemseq
               WHEN '2000152001' THEN  1
               WHEN '2000152002' THEN  0
               WHEN '2000152003' THEN -5
                END AS y
          FROM t
        ) a
;

 

by 오지현 [2018.02.19 17:30:59]

마농님,,, 한가지 질문좀...

첫번째 부분...

SELECT '2000152001' chitemseq, '330' chitem1, '330' chitem2, '330' chitem3

여기부분을 이렇게 바꾸면,, 330이 나옵니다.   이것을 기본 330.0 이렇게 나오게 하려면 어떻게 해야할까요?

딱 정수로 나누어져도 자릿수가 뒤에 한자리가 나오게요..

by 마농 [2018.02.19 17:55:07]
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
WITH t AS
(
SELECT '2000152001' chitemseq, '326' chitem1, '284' chitem2, '203' chitem3
UNION ALL SELECT '2000152002',       '40',       '70',       '20'
UNION ALL SELECT '2000152003', '5.44E+07', '3.66E+07', '3.24E+07'
)
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
;

 

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