데이터베이스 질문과 답변
SQL- 조건에 따른 자리수 틀린 값들 표현
3
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
;
) 버튼을 클릭하여 작성 하시면 됩니다.