select * from tbl WHERE A like '%***%' or B like '%***%' or C like '%***%'
동일한 검색어로 A,B,C 칼럼 like 검색 결과를
A칼럼에 like 검색된 결과 (결과물은 A asc순), B칼럼에 like 검색된 결과 (결과물은 B asc순), C칼럼에 like 검색된 결과(결과물은 C asc순) 순으로 정렬을 할 수 있는 방법이 있을까요?
Comment 2
-
Terry
2015.10.06 12:07
-
피오니
2015.10.06 15:11
의도하는 정렬의 형태를 정확히 이해하지 못하겠네요..DECLARE @vSearch varchar(10)SET @vSearch = '#';WITH tblA (a,b,c) AS(SELECT '12345','zpkde','!@#%$' UNION ALLSELECT 'ERss$$','ha5#2',')!@%&4zh' UNION ALLSELECT 'as$#3','%a5#2','1!%24h' UNION ALLSELECT 'h1^AZ','^a5#2','~!3~#8' UNION ALLSELECT 'hhh13','!a5#2','#$T4%!@' UNION ALLSELECT 'hh^^h13','ha##5#2','?g24zh' UNION ALLSELECT 'hh%%h13','A~%ha5#2','1S~24zh' UNION ALLSELECT 'z}|!+_%','%#ha5#2','}12%#h' UNION ALLSELECT 'hh!@h13',')(ha5#2','{1h%%!!!')SELECT *,CASE WHEN a LIKE '%'+Rtrim(@vSearch)+'%' THEN 'A'WHEN b LIKE '%'+Rtrim(@vSearch)+'%' THEN 'B'WHEN c LIKE '%'+Rtrim(@vSearch)+'%' THEN 'C' END as ordFROM tblAWHERE a LIKE '%'+Rtrim(@vSearch)+'%'OR b LIKE '%'+Rtrim(@vSearch)+'%'OR c LIKE '%'+Rtrim(@vSearch)+'%'ORDER BY ord, a, b, c
참고만 하세요.
실력이 부족해서 쿼리문이 좀 복잡합니다..
----쿼리시작----
Declare @ls_a char(10)
Declare @ls_b char(10)
Declare @ls_c char(10)
Select @ls_a = 'E'
Select @ls_b = '2'
Select @ls_c = '?'
with tblA (a,b,c) As
(
Select '12345','zpkde','!@#%$' Union All
Select 'ERss$$','ha5#2',')!@%&4zh' Union All
Select 'as$#3','%a5#2','1!%24h' Union All
Select 'h1^AZ','^a5#2','~!3~#8' Union All
Select 'hhh13','!a5#2','#$T4%!@' Union All
Select 'hh^^h13','ha##5#2','?g24zh' Union All
Select 'hh%%h13','A~%ha5#2','1S~24zh' Union All
Select 'z}|!+_%','%#ha5#2','}12%#h' Union All
Select 'hh!@h13',')(ha5#2','{1h%%!!!'
)
,tblB (a,b,c,rn) As
(
Select a.a
,''
,''
,ROW_NUMBER() Over(order by a asc)
From tblA a
Where a.a Like '%'+Rtrim(@ls_a)+'%'
Union All
Select ''
,a.b
,''
,ROW_NUMBER() Over(order by b asc)
From tblA a
Where a.b Like '%'+Rtrim(@ls_b)+'%'
Union All
Select ''
,''
,a.c
,ROW_NUMBER() Over(order by c asc)
From tblA a
Where a.c Like '%'+Rtrim(@ls_c)+'%'
)
Select
(
Select
Top 1 b.a
From tblB b
Where b.rn = a.rn
) As a
,(
Select
Top 1 b.b
From tblB b
Where b.rn = a.rn
) As a
,(
Select
Top 1 b.c
From tblB b
Where b.rn = a.rn
) As a
,a.rn
From tblB a
Group By a.rn
Order By a.rn
----쿼리끝----