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

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



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]
-- 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]
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() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입