안녕 하세요 메아리 입니다.
select 두개 문을 하나로 합쳐서 출력 할려고 합니다.
SELECT CONVERT(VARCHAR, timestamp, 23) , sensor_id
FROM surveydb.dbo.survey_body s
WHERE site_code = '001007003001'
AND CONVERT(VARCHAR, timestamp, 23) between '2015-08-29' AND '2015-08-29'
AND sensor_id IN ('255')
위 구문은 잘 됩니다.
SELECT CONVERT(NVARCHAR, timestamp, 120) as date_time
FROM surveydb.dbo.survey_body
WHERE site_code = '001007003001'
AND sensor_id = '255'
AND CONVERT(VARCHAR, timestamp, 23) between '2015-08-29' AND '2015-08-29'
위 구문도 잘 됩니다.
그런데 두개를 합치기 하면
아래처럼
select *
FROM
(
SELECT CONVERT(VARCHAR, timestamp, 23) , sensor_id
FROM surveydb.dbo.survey_body s
WHERE site_code = '001007003001'
AND CONVERT(VARCHAR, timestamp, 23) between '2015-08-29' AND '2015-08-29'
AND sensor_id IN ('255')
) union all
select *
FROM
(
SELECT CONVERT(NVARCHAR, timestamp, 120) sensor_id
FROM surveydb.dbo.survey_body
WHERE site_code = '001007003001'
AND sensor_id = '255'
AND CONVERT(VARCHAR, timestamp, 23) between '2015-08-29' AND '2015-08-29'
)
하면 동작이 안됩니다.
합치는 조건은 상관없습니다.
그러니까 윗쪽 테이블 칼럼이나, 아랫쪽 테이블 칼럼이 동일합니다.
그냥 모두 표시 되게끔 합치면 되는 겁니다.
한수 부탁 드립니다.
이상입니다.
감사 합니다.
mssql 사용하는거 맞으시죠?
From 절
From ( )
에 Alias 가 지정 안되어있네요..
오류 내뱉을 겁니다..
1. 질문자님이 작성하신 쿼리 작동되게끔만 수정
---쿼리시작---
select a.*
FROM
(
SELECT CONVERT(VARCHAR, timestamp, 23) , sensor_id
FROM surveydb.dbo.survey_body s
WHERE site_code = '001007003001'
AND CONVERT(VARCHAR, timestamp, 23) between '2015-08-29' AND '2015-08-29'
AND sensor_id IN ('255')
) a union all
select b.*
FROM
(
SELECT CONVERT(NVARCHAR, timestamp, 120) sensor_id
FROM surveydb.dbo.survey_body
WHERE site_code = '001007003001'
AND sensor_id = '255'
AND CONVERT(VARCHAR, timestamp, 23) between '2015-08-29' AND '2015-08-29'
) b
SELECT CONVERT(VARCHAR, timestamp, 23) , sensor_id
FROM surveydb.dbo.survey_body s
WHERE site_code = '001007003001'
AND CONVERT(VARCHAR, timestamp, 23) between '2015-08-29' AND '2015-08-29'
AND sensor_id IN ('255')
SELECT CONVERT(NVARCHAR, timestamp, 120) sensor_id
FROM surveydb.dbo.survey_body
WHERE site_code = '001007003001'
AND sensor_id = '255'
AND CONVERT(VARCHAR, timestamp, 23) between '2015-08-29' AND '2015-08-29'