말없제이™'

출처 : http://blog.naver.com/kyumi0705/20112791363

원본출처에 정리가 잘 되어있습니다.

======

GROUP BY 해서 기준으로 MAX,MIN으로 구한 키값을 서브쿼리로 하니 속도가 엄청 느려져. 아.. 이건아니다 싶어서,

조회를 해보니 나오네요~~

응용참조

SELECT
CSM.UnitNo
, CSM.StrYmd
, CASE
 WHEN ROW_NUMBER() OVER(PARTITION BY CSM.UnitNo ORDER BY CSM.UnitNo, CSM.StrYmd) = 1
 THEN 'Y' ELSE '' END AS ViewYN
, CSM.TermKey
FROM #T_PD AS CSM

한번더 감싸서 ViewYN = 'Y'인것만 가져오면 됨. 순간 bool이 속도가 빠르지 않을까하는 생각이 문득--

=======

★ ROW_NUMBER() OVER- 특정 컬럼 기준으로 순위정하기(행번호 부여하기)

결과

MAIN_CODE     GOODS_SEQ    OFFER_MASTER_SEQ     MODEL_CD    RANK

--------------------------------------------------------------

1                  1                 1                             AAA            1

1                  1                 2                             AAA            2

1                  2                 4                             BBB            3

1                  2                 5                             BBB            4

2                  1                 1                             AAA            1

2                  1                 4                             AAA            2

2                  2                 5                             BBB            3

2                  2                 6                             BBB            4

2                  3                 7                             CCC            5

2                  3                 9                             CCC            6

★ 특정 column의 값을 기준으로 레코드의 순서정하여 정렬하기.

TEAM_CD     SCORE     PALY_DATE

------------------------------

AAA            90          2010/08/01

AAA            50          2010/08/02

AAA            60          2010/08/03

AAA            50          2010/08/04

BBB            50          2010/08/01

BBB            90          2010/08/02

BBB            95          2010/08/03

BBB            100         2010/08/04

방법 1. RANK() OVER

결과

TEAM_CD     SCORE     RANK     PALY_DATE

------------------------------

BBB            100        1          2010/08/04

BBB            95          2          2010/08/03

AAA            90          3          2010/08/01

BBB            90          3          2010/08/02

AAA            60          5          2010/08/03

AAA            50          6          2010/08/02

AAA            50          6          2010/08/04

BBB            50          6          2010/08/01

방법 2. ROW_NUMBER() OVER

결과

TEAM_CD     SCORE     RANK     PALY_DATE

------------------------------

BBB            100        1          2010/08/04

BBB            95          2          2010/08/03

AAA            90          3          2010/08/01

BBB            90          4          2010/08/02

AAA            60          5          2010/08/03

AAA            50          6          2010/08/02

AAA            50          7          2010/08/04

BBB            50          8          2010/08/01

방법 3. DENSE_RANK() OVER

결과

TEAM_CD     SCORE     RANK     PALY_DATE

------------------------------

BBB            100        1          2010/08/04

BBB            95          2          2010/08/03

AAA            90          3          2010/08/01

BBB            90          3          2010/08/02

AAA            60          4          2010/08/03

AAA            50          5          2010/08/02

AAA            50          5          2010/08/04

BBB            50          5          2010/08/01

★ 분석용 함수

RANK - 해당값에 대한 우선순위를 결정 (중복 우선순위 허용)

DENSE_RANK - 해당값에 대한 우선순위를 결정 (중복 우선순위 허용 안함)

ROW_NUMBER - 조건을 만족하는 모든 행의 번호를 제공

CUME_DIST - 분산값

PERCENT_RANK - 백분율

NTILE(n) - 전체 데이터 분포를 n-Buckets으로 나누어 표시

FIRST_VALUE - 정렬된 값중에서 첫번째 값을 반환.

LAST_VALUE - 정렬된 값중에서 마지막 값을 반환.

★ OVER() 에 사용되는 OPTION

1. PARTITION BY
2. ORDER BY DESC
3. NULLS FIRST : NULL 데이터를 먼저 출력.
4. NULLS LAST : NULL 데이터를 나중에 출력.

링크드서버도 연결안되면,

간략히 ~~~..

SELECT *
 FROM OPENROWSET(
 'SQLNCLI',
 'Server=***;UID=**;PWD=***',
 'SELECT * FROM TABLE ')

SQL구문에서 여러줄결과값을 한줄로 보여주기

함수... 함수를 사용하면됨

그러나 함수사용 제약이 있을경우

ComCd ComNm                ComSubCd ComSubNm
----- -------------------- -------- --------------------

A001  모니터                  17       17인치모니터
A001  모니터                  19       19인치모니터
A001  모니터                  22       22인치모니터
A002  책상                   22       22형책상
A002  책상                   27       27형책상

항목을 구지 아래와 같이 보여야 되는 사항이라면

ComSubCd ComNm
-------- ----------------------------------------------------
17       17인치모니터
19       19인치모니터
22       모니터-22인치모니터; 책상-22형책상;
27       27형책상

(4개 행이 영향을 받음)

 FOR XML PATH('')) 사용하심~~.

--- 예제

DECLARE @D_Code VARCHAR(50)
SELECT @D_Code = '';

SELECT ComCd, ComNm, ComSubCd, ComSubNm
FROM ComCdMain
WHERE CONVERT(VARCHAR, GETDATE(),112) BETWEEN FrYmd AND ToYmd
AND ( ComCd LIKE @D_Code+'%' OR ComNm LIKE @D_Code+'%')
GROUP BY ComCd, ComNm,ComSubCd, ComSubNm
;

--유형서브코드
WITH #T_INSU2 AS
(
SELECT *
FROM ComCdMain
WHERE CONVERT(VARCHAR, GETDATE(),112) BETWEEN FrYmd AND ToYmd
)
, #T_INCD AS
(
SELECT ComCd, ComNm
FROM #T_INSU2
GROUP BY ComCd, ComNm
)
, #T_SUBCD AS
(
SELECT ComCd, ComNm, ComSubCd, ComSubNm
FROM #T_INSU2
GROUP BY ComCd, ComNm, ComSubCd, ComSubNm
)
, #T_SUBDIST AS
(
SELECT ComSubCd, ComSubNm, COUNT(1) AS SubCnt
FROM #T_SUBCD
GROUP BY ComSubCd, ComSubNm
)
, #T_SUBCNT AS
(
SELECT ComSubCd, COUNT(1) AS SubCnt
FROM #T_SUBDIST
GROUP BY ComSubCd
)
SELECT
 SB.ComSubCd
 , CASE SB.SubCnt
 WHEN '0' THEN ''
 WHEN '1' THEN ( SELECT TOP 1 ComSubNm FROM #T_INSU2 WHERE ComSubCd = SB.ComSubCd ) 
 ELSE (
 SELECT ComNm +'-'+ ComSubNm +'; ' FROM #T_INSU2 WHERE ComSubCd = SB.ComSubCd FOR XML PATH(''))
 END AS 'ComNm'
FROM #T_SUBCNT AS SB
WHERE ComSubCd LIKE @D_Code+'%'
OR CASE SB.SubCnt
 WHEN '0' THEN ''
 WHEN '1' THEN ( SELECT TOP 1 ComSubNm FROM #T_INSU2 WHERE ComSubCd = SB.ComSubCd ) 
 ELSE (
 SELECT ComNm +'-'+ ComSubNm +'; ' FROM #T_INSU2 WHERE ComSubCd = SB.ComSubCd FOR XML PATH(''))
 END LIKE '%'+@D_Code+'%'
;

이전날자와 비교하여 날짜차이 찾기

빠진이빨찾기II
참조 : http://www.gooper.com/ss/index.php?mid=tnt_db&sort_index=regdate&order_type=desc&comment_srl=3990&document_srl=1764

. 자 ~~ 응용

WITH T_EPA AS
(
SELECT
ROW_NUMBER() OVER (ORDER BY ErPatCalcYmd) AS RowNum
, ErPatCalcYmd
FROM MP_ErPatDailyCalc WITH(NOLOCK)
GROUP BY ErPatCalcYmd
)
, T_EPB AS
(
SELECT
A.RowNum
, A.ErPatCalcYmd
, B.ErPatCalcYmd AS BeErPatCalcYmd
, DATEDIFF(DD,CONVERT(DATETIME, B.ErPatCalcYmd), CONVERT(DATETIME, A.ErPatCalcYmd) ) AS DaysCout
FROM T_EPA AS A
LEFT OUTER JOIN T_EPA AS B
ON A.RowNum = B.RowNum+1
)
SELECT * FROM T_EPB
WHERE DaysCout > 1

SET IDENTITY_INSERT TTTABLE ON --자동증가하는 항목도 포함하여 추가

INSERT INTO TTTABLE
(AA,BB)
Select
AA,BB
FROM OPENROWSET('SQLOLEDB','SEVER_IP';'ID';'PW'
,'select * from TEST..TTTABLE'
) AS a  

SET IDENTITY_INSERT TTTABLE OFF

--SSIS 에서 테이블 > 종속성 속성을 보면 해당 테이블 연계된 SP, FN 등나옴
--그런다 같은 DB가 이라면... 인내심 좋으신 분은 수동으로 작업하시면 됩니다.
--그러다 찾아보니--

--항상 참조링크를 먼저 보세요~~ ^^.

--특정 테이블 SP찾는 쿼리
http://aruesoft.tistory.com/61

Select object_name(id) From syscomments Where text like '%ZZ_ComCD%'
Group by object_name(id)
Order by object_name(id)

--text nvarchar no 8000
--그러나 간략조회~~~ 라는 말 !! 8000자 넘어가면 안된다는.. 허겅~~

--다시 구글링

--SQLServer에서 변경하거나 추가한 sp, uf 및 table등을 확인하기
http://dialup.egloos.com/1066296

--SQLServer를 사용하는 시스템 운영시 가끔 작성한 혹은 변경한 sp, uf 및 table들에 대해서
--알고 싶을때가 있다. (많은 변경이 되는 경우에 작업중 documentation을 제대로 하지 않으면
--실서버 적용시 몇가지 변경사항이 적용이 누락되어 낭패를 보는 경우가 종종 있다.) 이 때
--변경된 혹은 추가된 사항들에 대해서 알고 싶은 상황이 발생하는데, sqlserver에는 모든 object에
--대해서 관리하는 카탈로그 뷰가 있는데 이를 사용하면 편리하다.
--------------------------------------------------------------------
--in sql2005
select *
from   [database명].sys.objects
order by modify_date desc

--in sql2000
select *
from   [database명].sys.sysobjects
order by refdate desc
--------------------------------------------------------------------

--*위의 query는 가장 최근에 변경 혹은 생성된 내역들 순으로 확인이 가능하게 해 준다.
-- sys.objects의 경우 2005에서 추가된 view인데 2000에서는 sysobjects가 그것이다.
-- 그런데 sysobjects table의 경우 다른 무언가가 있는지 모르겠는데, 변경사항을 위의
-- query로 정확하게 알 수는 없다. 요즘은 2000쓰는 곳이 별로 없을것이라고 생각하기 때문에
-- 그냥 pass~
 --> added @ 2008-11-07 sysobjects view의 refdate는 수정될 때는 update가 안된다고 합니다. ^^

--*추가로 가끔 sp나 uf내의 특정 문자값을 찾고 싶을때가 있는데 이때는 아래와 같이...
--------------------------------------------------------------------
-in sql2005
select *
from   [database명].sys.sql_modules
where  definition like '%ZZ_ComCd%'

--in sql2000
select *
from   [database명].sys.syscomments
where  text like '%ZZ_ComCd%'
---------------------------------------------

--oracle도 마찬가지겠지만, application개발자들도 각 DBMS에서 제공하는 catalog들을 잘 활용하면
--많은 도움을 얻을 수 있다.

--한 숫가락 얹기.. ㅋㅋ.
-- 8천자이상도 나옴~~~.
SELECT
SO.name, SM.definition, DATALENGTH(SM.definition) AS '한글2자처리', LEN(SM.definition) AS '문자수'
FROM sys.sql_modules AS SM
INNER JOIN sys.sysobjects AS SO
ON SM.object_id = SO.id
WHERE  SM.definition like '%ZZ_ComCd%'

참조 :

http://holictoweb.tistory.com/21

참조는 참 설명이 잘 되어있습니다. ^^. 

--

select TOP 10 SetCd from LNK.ABC.dbo.TableDataSet

메시지 9514, 수준 16, 상태 1, 줄 1
분산 쿼리에서 Xml 데이터 형식을 사용할 수 없습니다.
원격 개체 'OCS01.HISE.dbo.MR_PredefinedSet'에 xml 열이 있습니다.

SELECT * from OPENQUERY
(KNK,
'select TOP 10 SetCd from ABC.dbo.TableDataSet'
)

:Identity를 선언한 경우에는 인서트 시 ,Identity선언 컬럼의 값을 임의로 지정할 수 없다
ex) insert into test(no,name) values(1000,'James')
테이블 생성 시 디폴트 값으로 임의의 번호로 지정하고 싶거나,
삭제된 번호를 채워 넣고 싶을 때 유용한 방법이다.

-SET IDENTITY_INSERT [테이블명] ON;
  증가값을 수동 지정

-SET IDENTITY_INSERT [테이블명] OFF;
 증가값을 자동 지정 (보통 우리가 사용하는 상태 )

-DBCC CHECKIDENT ('[테이블명]', RESEED, 0);
 시작값을 임의 지정

사용예)

SET IDENTITY_INSERT test ON;                      -- Identity 증가값을 수동지정
insert test(no,name) values(99999,'admin')      --수동입력 가능
SET IDENTITY_INSERT product_lank OFF;        --Identity 증가값을 자동지정
                                                                  --※ 증가값이 최초 1-->99999 변경되어짐
                                                                       즉,다음 인서트 값 Identity는 100000 됨

DBCC CHECKIDENT ('product_lank', RESEED, 0);   -- 시작값을 0으로 지정, 다음 증가값은 1이됨.