---- psql 7.4.2, Unix(HP)
CREATE TABLE TEST001 ( trs_gubn character(20) );
insert into TEST001 values('E001A0000');
insert into TEST001 values('E001B0000');
insert into TEST001 values('E001C0000');
insert into TEST001 values('E002A0000');
insert into TEST001 values('E002B0000');
insert into TEST001 values('E002C0000');
insert into TEST001 values('E003A0000');
insert into TEST001 values('E003B0000');
insert into TEST001 values('E003C0000');
insert into TEST001 values('A001A0000');
insert into TEST001 values('B001A0000');
insert into TEST001 values('E001 ');
insert into TEST001 values('E002 ');
insert into TEST001 values('E003 ');
-- 앞 4자리로 그룹핑한다.
Select substr(trs_gubn, 1, 4) as trs_gubn from TEST001
group by substr(trs_gubn, 1, 4) order by substr(trs_gubn, 1,
4);
trs_gubn
----------
A001
B001
E001
E002
E003
(5 rows)
-- 그룹핑한 데이터의 특정조건의 데이터를 조회한다.
select substr(trs_gubn, 1, 4) from TEST001 where substr(trs_gubn,
1, 1) = 'E'
group by substr(trs_gubn, 1, 4) order by substr(trs_gubn, 1,
4);
또는
select aa.trs_gubn from
(
select substr(trs_gubn, 1, 4) as trs_gubn from TEST001
group by substr(trs_gubn, 1, 4) order by substr(trs_gubn, 1,
4)
) aa
where substr(aa.trs_gubn, 1, 1) = 'E';
trs_gubn
----------
E001
E001
E002
E003
(4 rows)
※ 질문 :: 위 결과에서 'E001' 이 왜 2번 나오는지 이해 할 수가
없네요. 고수님들 답변 부탁드립니다.
|