안녕하세요 c#으로 개발을 하고있는데 쿼리가 막혀서 질문 드립니다.
우선 데이터는 이런 식으로 되어있습니다.
여기서 COMP_DEVICE_TYPE 컬럼에서 CAP_ 과 RES 로 필터를 하고
PIN_NUMBER 컬럼에 1번 데이터가 NET_NAME 컬럼 ADC_ 로 시작하고 PIN_NUMBER 컬럼에 2번 데이터가 NET_NAME컬럼에
GND롤 포함이 되는 데이터를 빼고 나머지를 찾고 싶습니다.
설명 하기가 어려운데
예} PIN_NUMBER ...... NET_NAME
1 Q_6B8
2 DGND
1 ADC_VDDDIG_11/7
2 UNNAME
요런 데이터를 찾을려고 합니다.
설명이 이상하지만 도움좀 주십시요 ㅠㅠ
Comment 5
-
Terry
2016.01.22 13:51
-
항해자™
2016.01.22 17:58
;with tblA(REFDES,PIN_NUMBER,COMP_DEVICE_TYPE,NET_NAME) As ( select 'C9/4',1,'CAP_1005_100N-100N-100N','ADC_VDDING_11/4' union all select 'C9/4',2,'CAP_1005_100N-100N-100N','DGND' union all select 'C9/5',1,'CAP_1005_100N-100N-100N','ADC_VDDING_11/5' union all select 'C9/5',2,'CAP_1005_100N-100N-100N','DGND' union all select 'C9/6',1,'CAP_1005_100N-100N-100N','ADC_VDDING_11/6' union all select 'C9/6',2,'CAP_1005_100N-100N-100N','DGND' union all select 'C9/7',1,'CAP_1005_100N-100N-100N','ADC_VDDING_11/7' union all select 'C9/7',2,'CAP_1005_100N-100N-100N','DGND' union all select 'R1/0',1,'RES_1005-4.7K-4.7K','Q_6B8' union all select 'R1/0',2,'RES_1005-4.7K-4.7K','DGND' union all select 'R1/1',1,'RES_1005-4.7K-4.7K','Q_6B6' union all select 'R1/1',2,'RES_1005-4.7K-4.7K','DGND' ) select * from tblA where (COMP_DEVICE_TYPE like 'CAP%' or COMP_DEVICE_TYPE like 'RES%' ) and not ( (PIN_NUMBER = 1 and NET_NAME like 'ADC%') or (PIN_NUMBER = 2 and NET_NAME like 'GND%') ) go
-
Terry
2016.01.22 18:57
Exists 를 습관적으로 썻네요..
필요도 없는데 왜 썼는지 저도 모르겠네유..ㅠ_ㅠ
---쿼리시작---
;with tblA(REFDES,PIN_NUMBER,COMP_DEVICE_TYPE,NET_NAME) As
(
Select 'C9/4',1,'CAP_1005_100N-100N-100N','ADC_VDDING_11/4' Union All
Select 'C9/4',2,'CAP_1005_100N-100N-100N','DGND' Union All
Select 'C9/5',1,'CAP_1005_100N-100N-100N','ADC_VDDING_11/5' Union All
Select 'C9/5',2,'CAP_1005_100N-100N-100N','DGND' Union All
Select 'C9/6',1,'CAP_1005_100N-100N-100N','ADC_VDDING_11/6' Union All
Select 'C9/6',2,'CAP_1005_100N-100N-100N','DGND' Union All
Select 'C9/7',1,'CAP_1005_100N-100N-100N','ADC_VDDING_11/7' Union All
Select 'C9/7',2,'CAP_1005_100N-100N-100N','DGND' Union All
Select 'R1/0',1,'RES_1005-4.7K-4.7K','Q_6B8' Union All
Select 'R1/0',2,'RES_1005-4.7K-4.7K','DGND' Union All
Select 'R1/1',1,'RES_1005-4.7K-4.7K','Q_6B6' Union All
Select 'R1/1',2,'RES_1005-4.7K-4.7K','DGND'
)
Select a.*
From tblA a
Where (
Charindex('CAP_',a.COMP_DEVICE_TYPE) <> 0
Or Charindex('RES_',a.COMP_DEVICE_TYPE) <> 0
)
And (
(
a.PIN_NUMBER = 1
And CHARINDEX('ADC_',a.NET_NAME) = 0
)
Or
(
a.PIN_NUMBER = 2
And CHARINDEX('GND',a.NET_NAME) = 0
)
)
---쿼리끝--- -
minsouk
2016.01.23 01:04
뭔가 많이 아쉬워요..... -
한태
2016.02.01 20:56
;with tblA(REFDES,PIN_NUMBER,COMP_DEVICE_TYPE,NET_NAME) As(Select 'C9/4',1,'CAP_1005_100N-100N-100N','ADC_VDDING_11/4' Union AllSelect 'C9/4',2,'CAP_1005_100N-100N-100N','DGND' Union AllSelect 'C9/5',1,'CAP_1005_100N-100N-100N','ADC_VDDING_11/5' Union AllSelect 'C9/5',2,'CAP_1005_100N-100N-100N','DGND' Union AllSelect 'C9/6',1,'CAP_1005_100N-100N-100N','ADC_VDDING_11/6' Union AllSelect 'C9/6',2,'CAP_1005_100N-100N-100N','DGND' Union AllSelect 'C9/7',1,'CAP_1005_100N-100N-100N','ADC_VDDING_11/7' Union AllSelect 'C9/7',2,'CAP_1005_100N-100N-100N','DGND' Union AllSelect 'R1/0',1,'RES_1005-4.7K-4.7K','Q_6B8' Union AllSelect 'R1/0',2,'RES_1005-4.7K-4.7K','DGND' Union AllSelect 'R1/1',1,'RES_1005-4.7K-4.7K','Q_6B6' Union AllSelect 'R1/1',2,'RES_1005-4.7K-4.7K','DGND')Select a.*From tblA awhere (COMP_DEVICE_TYPE like 'CAP%' or COMP_DEVICE_TYPE like 'RES%')and not (PIN_NUMBER=1 and NET_NAME like 'ADC%')and not (PIN_NUMBER=2 and NET_NAME ='DGND')설치시 전체텍스트검색을 선택했다면 like대신 contains 함수도 사용가능합니다.
이상하네요..알려주신 조건대로 데이터 뽑아봤는데..
하기 2가지 데이터만 조회되는데요?;;
R1/0 1 RES_1005-4.7K-4.7K Q_6B8
R1/1 1 RES_1005-4.7K-4.7K Q_6B6
하기 쿼리 참고하세요...
---쿼리시작---
;with tblA(REFDES,PIN_NUMBER,COMP_DEVICE_TYPE,NET_NAME) As
(
Select 'C9/4',1,'CAP_1005_100N-100N-100N','ADC_VDDING_11/4' Union All
Select 'C9/4',2,'CAP_1005_100N-100N-100N','DGND' Union All
Select 'C9/5',1,'CAP_1005_100N-100N-100N','ADC_VDDING_11/5' Union All
Select 'C9/5',2,'CAP_1005_100N-100N-100N','DGND' Union All
Select 'C9/6',1,'CAP_1005_100N-100N-100N','ADC_VDDING_11/6' Union All
Select 'C9/6',2,'CAP_1005_100N-100N-100N','DGND' Union All
Select 'C9/7',1,'CAP_1005_100N-100N-100N','ADC_VDDING_11/7' Union All
Select 'C9/7',2,'CAP_1005_100N-100N-100N','DGND' Union All
Select 'R1/0',1,'RES_1005-4.7K-4.7K','Q_6B8' Union All
Select 'R1/0',2,'RES_1005-4.7K-4.7K','DGND' Union All
Select 'R1/1',1,'RES_1005-4.7K-4.7K','Q_6B6' Union All
Select 'R1/1',2,'RES_1005-4.7K-4.7K','DGND'
)
Select a.*
From tblA a
Where (
Charindex('CAP_',a.COMP_DEVICE_TYPE) <> 0
Or Charindex('RES_',a.COMP_DEVICE_TYPE) <> 0
)
And Not Exists (
Select 'x'
From tblA b
Where (
Charindex('CAP_',b.COMP_DEVICE_TYPE) <> 0
Or Charindex('RES_',b.COMP_DEVICE_TYPE) <> 0
)
And
(
( b.PIN_NUMBER = 1
And CHARINDEX('ADC_',b.NET_NAME) <> 0
)
Or
( b.PIN_NUMBER = 2
And CHARINDEX('GND',b.NET_NAME) <> 0
)
)
And a.REFDES = b.REFDES
And a.PIN_NUMBER = b.PIN_NUMBER
And a.COMP_DEVICE_TYPE = b.COMP_DEVICE_TYPE
And a.NET_NAME = b.NET_NAME
)
---쿼리끝---