데이터베이스 질문과 답변
안녕하십니까 선배님들! 이제 막 회사에 들어와서 실무를 하고있는 초보 개발자 입니다...
다름이 아니라 프로젝트 수행중 막히고 있는 부분이 있어서 선배님들의 조언과 도움을 조금 받아보고자 올립니다 ㅠㅠ
첫번째 사진을 보면 표출되는 값이 계단형식으로 되어있지만 두번째 사진을 보면 한줄로 출력이 되는 것을 볼 수 있습니다!
두번째 사진 처럼 표출하고싶어 서브쿼리 써서 유니온으로 묶어도 계단형식처럼 표출이 되는데 쿼리문에 문제가 있나요??
쿼리는 MSSQL 2005 버전 쓰고있습니다!
<select id="historyManagerDAO.selectHistoryManagerList" parameterClass="historyManagerVO" resultClass="egovMap">
/*historyManagerDAO.selectHistoryManagerList*/
SELECT TOP 10 * FROM (
SELECT device_plot AS deviceplot
,temperature_value AS temperaturevalueT
,null AS temperaturevalueM
,null AS temperaturevalueB
,humidity_value AS humidityvalueT
,null AS humidityvalueM
,convert(char(10),convert(DATETIME,measurement_date),111) as measurementdate
,convert(char(10),stuff(stuff(measurement_time,3,0,':'),6,0,':'),120) as measurementtime
FROM smarcle_data_log
WHERE device_plot_tmb = '1' AND measurement_date between Convert(varchar(10),Getdate(),112)-111 and Convert(varchar(10),Getdate(),112)+1
UNION
SELECT device_plot AS deviceplot
,null AS temperaturevalueT
,temperature_value AS temperaturevalueM
,null AS temperaturevalueB
,null AS humidityvalueT
,humidity_value AS humidityvalueM
,convert(char(10),convert(DATETIME,measurement_date),111) as measurementdate
,convert(char(10),stuff(stuff(measurement_time,3,0,':'),6,0,':'),120) as measurementtime
FROM smarcle_data_log
WHERE device_plot_tmb = '2' AND measurement_date between Convert(varchar(10),Getdate(),112)-111 and Convert(varchar(10),Getdate(),112)+1
UNION
SELECT device_plot AS deviceplot
,null AS temperaturevalueT
,null AS temperaturevalueM
,temperature_value AS temperaturevalueB
,humidity_value AS humidityvalueT
,humidity_value AS humidityvalueM
,convert(char(10),convert(DATETIME,measurement_date),111) as measurementdate
,convert(char(10),stuff(stuff(measurement_time,3,0,':'),6,0,':'),120) as measurementtime
FROM smarcle_data_log
WHERE device_plot_tmb = '3' AND measurement_date between Convert(varchar(10),Getdate(),112)-111 and Convert(varchar(10),Getdate(),112)+1)AS UN
WHERE 1=1
<isNotEmpty prepend="AND" property="searchCondition">
deviceplot = #searchCondition#
</isNotEmpty>
<isNotEmpty property="schFrom">
<isNotEmpty prepend="AND" property="schTo">
measurementdate between CONVERT (DATE, #schFrom#) and CONVERT (DATE, #schTo#)
</isNotEmpty>
</isNotEmpty>
GROUP BY deviceplot
,temperaturevalueT
,temperaturevalueM
,temperaturevalueB
,humidityvalueT
,humidityvalueM
,measurementdate
,measurementtime
order by measurementdate desc, measurementtime desc
</select>
아니면 지금 제가 작성한 쿼리문의 방법이 잘못 된 것 인가요?? 선배님들 조언 부탁드립니다!
문제가 있다면 도움 주시면 감사하겠습니다!
쿼리에 문제가 많네요.
1. 조회 조건 중 날짜 조건 이상함.
- AND measurement_date between Convert(varchar(10),Getdate(),112)-111 and Convert(varchar(10),Getdate(),112)+1
- 오늘 날짜를 문자로 바꾼뒤 거기서 111 을 빼면 날짜가 아닌 이상한 값이 되어 버립니다.
- 문자로 바꾼뒤 111 을 뺄게 아니라 111을 뺀 뒤 문자로 바꿔야 함
- 오류 : Convert(varchar(10),Getdate(),112)-111
- 수정 : Convert(varchar(10),Getdate()-111,112)
2. measurement_date 가 문자형 인가요?
- 어떤 포멧으로 저장되어 있나요?
- #schFrom# AND #schTo# 는 어떤 포멧으로 되어 있나요?
- 문자 타입 컬럼에 날짜 타입 조건을 주는 것은 문제가 있습니다.
3. 불필요한 함수 사용 제거
- 문자를 다시 문자로 변환하는 불필요한 과정
- 변경전 : CONVERT(CHAR(10),STUFF(STUFF()),120)
- 변경후 : STUFF(STUFF())
4. UNION 은
- UNION 대신 UNION ALL 로 사용하는게 맞고
- UNION ALL 조차도 불필요함.
- 변경전 : 1,2,3 를 개별로 가져와 Union
- 변경후 : 1,2,3 를 한번에 가져와 Case 문으로 제어
5. Group By 기준 항목
- 전체 컬럼으로 그룹바이 하네요?
- 기준이 되는 항목으로만 그룹바이 해야 합니다.
6. Top 10 사용
- 이 구문이 사용되는 이유가 뭘까요?
SELECT device_plot 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 measurement_date BETWEEN CONVERT(VARCHAR(10), Getdate() - 111, 112)
AND CONVERT(VARCHAR(10), Getdate() + 1, 112)
AND device_plot_tmb IN ('1','2','3')
-- 선택 조건 --
AND device_plot = #searchCondition#
AND measurement_date BETWEEN REPLACE(#schFrom#, '-', '') AND REPLACE(#schTo#, '-', '')
-- 선택 조건 --
GROUP BY device_plot, measurement_date, measurement_time
ORDER BY measurement_date DESC, measurement_time DESC, device_plot
;
정말 감사합니다 해결 되었습니다 ㅠㅠㅠ
top 10 쓴 이유는 페이징 처리가 되어있지 않아서 데이터를 10만개 이상 불러들여야 하면 렉이 좀 걸려서 일시적으로 써둔 것 이었습니다 ㅠㅠ 감사합니다 덕분에 해결했습니다,.,..!
) 버튼을 클릭하여 작성 하시면 됩니다.