데이터베이스 질문과 답변
맨밑에 계산값을 만들려고 하는데.. 12이하버전 이여서 lag 함수가 되질 않네요 ㅠ
이럴경우 어떻게 해야할까요?
WITH t AS
(
SELECT '길이' chitemseq, '19.7' chitem1, '19.8' chitem2, '20.2' chitem3
UNION ALL SELECT '폭', '15.0', '15.0', '15.2'
UNION ALL SELECT '무게', '3.82', '3.89', '4.04'
)
SELECT chitemseq
, chitem1, chitem2, chitem3
from t
;
계산은 무게/길이/폭*10000 입니다.
| 길이 | 19.7 | 19.8 | 20.2 |
| 폭 | 15 | 15 | 15.2 |
| 무게 | 3.82 | 3.89 | 4.04 |
| 계산 | 129.27 | 130.98 |
131.58 |
by 우리집아찌
[2018.02.26 14:24:14]
-- MS-SQL 이시면 적당히 바꿔주세요.
WITH t AS
(
SELECT '길이' chitemseq, '19.7' chitem1, '19.8' chitem2, '20.2' chitem3 FROM DUAL
UNION ALL SELECT '폭', '15.0', '15.0', '15.2' FROM DUAL
UNION ALL SELECT '무게', '3.82', '3.89', '4.04' FROM DUAL
)
SELECT chitemseq
, chitem1, chitem2, chitem3
FROM t
UNION ALL
SELECT '계산'
, TO_CHAR(MIN(CASE WHEN chitemseq = '무게' THEN chitem1 END)
/ MIN(CASE WHEN chitemseq = '길이' THEN chitem1 END)
/ MIN(CASE WHEN chitemseq = '폭' THEN chitem1 END) * 10000 ) AS chitem1
, TO_CHAR(MIN(CASE WHEN chitemseq = '무게' THEN chitem2 END)
/ MIN(CASE WHEN chitemseq = '길이' THEN chitem2 END)
/ MIN(CASE WHEN chitemseq = '폭' THEN chitem2 END) * 10000 ) AS chitem2
, TO_CHAR(MIN(CASE WHEN chitemseq = '무게' THEN chitem3 END)
/ MIN(CASE WHEN chitemseq = '길이' THEN chitem3 END)
/ MIN(CASE WHEN chitemseq = '폭' THEN chitem3 END) * 10000 ) AS chitem3
FROM t
by 마농
[2018.03.02 08:14:25]
WITH t AS
(
SELECT '길이' chitemseq, '19.7' chitem1, '19.8' chitem2, '20.2' chitem3
UNION ALL SELECT '폭' , '15.0', '15.0', '15.2'
UNION ALL SELECT '무게', '3.82', '3.89', '4.04'
)
SELECT *
FROM (SELECT gb
, CAST(길이 AS VARCHAR) 길이
, CAST(폭 AS VARCHAR) 폭
, CAST(무게 AS VARCHAR) 무게
, CAST(ROUND(CAST(무게 AS FLOAT) / 길이 / 폭 * 10000, 2) AS VARCHAR) 계산
FROM t
UNPIVOT (x FOR gb IN (chitem1, chitem2, chitem3)) a
PIVOT (MIN(x) FOR chitemseq IN (길이, 폭, 무게)) a
) a
UNPIVOT (x FOR chitemseq IN (길이, 폭, 무게, 계산)) a
PIVOT (MIN(x) FOR gb IN (chitem1, chitem2, chitem3)) a
ORDER BY CHARINDEX(chitemseq, '길이, 폭, 무게, 계산')
;
) 버튼을 클릭하여 작성 하시면 됩니다.