데이터베이스 질문과 답변
데이터가 나오면 poi를 이용하여 엑셀파일로 저장을 하게 해둔 상황입니다
지금의 쿼리문 입니다
SELECT
*
FROM
(
SELECT
ROW_NUMBER() OVER (
ORDER BY measurement_date DESC, measurement_time DESC ) AS rownum ,
(CASE
WHEN device_plot = '11' THEN 'Plot1'
WHEN device_plot = '22' THEN 'Plot2'
WHEN device_plot = '33' THEN 'Plot3'
WHEN device_plot = '44' THEN 'Plot4'
END)as deviceplot , CONVERT(CHAR(10), CONVERT(DATETIME, measurement_date), 111) measurementdate
, STUFF(STUFF(measurement_time, 3, 0, ':'), 6, 0, ':') measurementtime
, MAX(CASE device_plot_tmb WHEN '1' THEN temperature_value END) temperaturevalueT
, MAX(CASE device_plot_tmb WHEN '2' THEN temperature_value END) temperaturevalueM
, MAX(CASE device_plot_tmb WHEN '3' THEN temperature_value END) temperaturevalueB
, MAX(CASE device_plot_tmb WHEN '1' THEN humidity_value END) humidityvalueT
, MAX(CASE device_plot_tmb WHEN '2' THEN humidity_value END) humidityvalueM
FROM
smarcle_data_log
WHERE
1 = 1
AND device_plot_tmb IN ('1', '2', '3')
AND(SUBSTRING(measurement_time, 3, 2) % 10)= 0
AND measurement_date BETWEEN REPLACE(20210115, '-', '') AND REPLACE(20210115, '-', '')
GROUP BY
device_plot, measurement_date, measurement_time)a
쿼리문 입니다..
이상태로 엑셀 다운로드를 하게 되면 값은 잘 나오나 빈칸이 나오는곳이 null 값이 표출되어서 도움을 주시면 감사하겠습니다
!
CASE 문을 써서 똑같이
SELECT
*
FROM
(
SELECT
ROW_NUMBER() OVER (
ORDER BY measurement_date DESC, measurement_time DESC ) AS rownum ,
(CASE
WHEN device_plot = '11' THEN 'Plot1'
WHEN device_plot = '22' THEN 'Plot2'
WHEN device_plot = '33' THEN 'Plot3'
WHEN device_plot = '44' THEN 'Plot4'
WHEN temperature_value = 'NULL' THEN ''
END)as deviceplot , CONVERT(CHAR(10), CONVERT(DATETIME, measurement_date), 111) measurementdate
, STUFF(STUFF(measurement_time, 3, 0, ':'), 6, 0, ':') measurementtime
, MAX(CASE device_plot_tmb WHEN '1' THEN temperature_value END) temperaturevalueT
, MAX(CASE device_plot_tmb WHEN '2' THEN temperature_value END) temperaturevalueM
, MAX(CASE device_plot_tmb WHEN '3' THEN temperature_value END) temperaturevalueB
, MAX(CASE device_plot_tmb WHEN '1' THEN humidity_value END) humidityvalueT
, MAX(CASE device_plot_tmb WHEN '2' THEN humidity_value END) humidityvalueM
FROM
smarcle_data_log
WHERE
1 = 1
AND device_plot_tmb IN ('1', '2', '3')
AND(SUBSTRING(measurement_time, 3, 2) % 10)= 0
AND measurement_date BETWEEN REPLACE(20210115, '-', '') AND REPLACE(20210115, '-', '')
GROUP BY
device_plot, measurement_date, measurement_time, temperature_value)a
처리를 해보았는데 값이 제대로 나오지 않아서 도움을 구합니다 ㅠㅠ
SELECT *
FROM (SELECT ROW_NUMBER() OVER(
ORDER BY measurement_date DESC, measurement_time DESC) AS rownum
, CASE device_plot
WHEN '11' THEN 'Plot1'
WHEN '22' THEN 'Plot2'
WHEN '33' THEN 'Plot3'
WHEN '44' THEN 'Plot4'
ELSE '' END AS deviceplot
, CONVERT(CHAR(10), CONVERT(DATETIME, measurement_date), 111) measurementdate
, STUFF(STUFF(measurement_time, 3, 0, ':'), 6, 0, ':') measurementtime
, CONCAT(MAX(CASE device_plot_tmb WHEN '1' THEN temperature_value END), '') temperaturevalueT
, CONCAT(MAX(CASE device_plot_tmb WHEN '2' THEN temperature_value END), '') temperaturevalueM
, CONCAT(MAX(CASE device_plot_tmb WHEN '3' THEN temperature_value END), '') temperaturevalueB
, CONCAT(MAX(CASE device_plot_tmb WHEN '1' THEN humidity_value END), '') humidityvalueT
, CONCAT(MAX(CASE device_plot_tmb WHEN '2' THEN humidity_value END), '') humidityvalueM
FROM smarcle_data_log
WHERE 1=1
AND device_plot_tmb IN ('1', '2', '3')
AND SUBSTRING(measurement_time, 3, 2) % 10 = 0
AND measurement_date BETWEEN '20210115' AND '20210115'
GROUP BY device_plot, measurement_date, measurement_time
) a
;
감사합니다!
지금 쓰고있는 mssql 버전이 2008 버전으로 알고있는데 concat 는 14버전 이후에 생긴 함수라 사용을 못한다고 하는데 혹시 다른 방법이 있는지 좀 알 수 있을까요?? 감사합니다
SELECT *
FROM (SELECT ROW_NUMBER() OVER(
ORDER BY measurement_date DESC, measurement_time DESC) AS rownum
, CASE device_plot
WHEN '11' THEN 'Plot1'
WHEN '22' THEN 'Plot2'
WHEN '33' THEN 'Plot3'
WHEN '44' THEN 'Plot4'
ELSE '' END AS deviceplot
, CONVERT(CHAR(10), CONVERT(DATETIME, measurement_date), 111) measurementdate
, STUFF(STUFF(measurement_time, 3, 0, ':'), 6, 0, ':') measurementtime
, ISNULL(CAST(MAX(CASE device_plot_tmb WHEN '1' THEN temperature_value END) AS VARCHAR), '') temperaturevalueT
, ISNULL(CAST(MAX(CASE device_plot_tmb WHEN '2' THEN temperature_value END) AS VARCHAR), '') temperaturevalueM
, ISNULL(CAST(MAX(CASE device_plot_tmb WHEN '3' THEN temperature_value END) AS VARCHAR), '') temperaturevalueB
, ISNULL(CAST(MAX(CASE device_plot_tmb WHEN '1' THEN humidity_value END) AS VARCHAR), '') humidityvalueT
, ISNULL(CAST(MAX(CASE device_plot_tmb WHEN '2' THEN humidity_value END) AS VARCHAR), '') humidityvalueM
FROM smarcle_data_log
WHERE 1=1
AND device_plot_tmb IN ('1', '2', '3')
AND SUBSTRING(measurement_time, 4, 1) = '0'
AND measurement_date BETWEEN '20210115' AND '20210115'
GROUP BY device_plot, measurement_date, measurement_time
) a
;
역시 god 마농님 감사합니다 ㅠㅠ 덕분에 잘 해결된 것 같습니다! 감사합니다!
) 버튼을 클릭하여 작성 하시면 됩니다.