제목 : sqlGate에서 mysql TABLE 에 컬럼에 한글 insert 시 오류 대처법




소스 :


insert into tb_member(userID,userPW,userName,hp,tel) 

VALUES('test','5252','테스트','010-0000-0000','02-0000-0000');




내용 :


sqlGate 새 접속시 아래 그림에서 유니코드 사용에 체크하면 됨.








예제 소스 파일 :


 출처 :  http://www.okjsp.pe.kr:8080/article/132610




저작자 표시 비영리 변경 금지
신고
Posted by 잠수콩



제목 : MSSQL 랜덤 추출 후, 순차번호 입력하기 ( newid(), Row_number() 사용)




소스 :


--//랜덤 문제 추출 후 등록

INSERT INTO nOxQuiz_Exam_Log (uid, testNum, qIdx, regdate, sortNum, point)

SELECT @uid, @RecordCount2, A.idx, getdate(), Row_number() OVER(ORDER BY (SELECT NULL)) as sortNum, 0

FROM (

SELECT TOP 50 idx FROM nOxQuiz_Exam WHERE openYN = 'Y' ORDER BY NEWID()

) A

ORDER BY sortNum ASC





내용 :


랜덤 ROW 추출 : NEWID() --> 갯수만큼 추출한다.


데이타 SORT : Row_number() OVER(ORDER BY (SELECT NULL)) --> 1,2,3,4,5 로 증가됨.







예제 소스 파일 :


 출처 :  https://stackoverflow.com/questions/41022467/get-row-number-of-random-records




저작자 표시 비영리 변경 금지
신고
Posted by 잠수콩



제목 : mssql SSMS 쿼리문 이쁘게 조정하는 툴




소스 :


http://www.dpriver.com/products/sqlpp/ssms_index.php


내용 :


http://www.dpriver.com/products/sqlpp/ssms_index.php


위 사이트링크에서 자신의 버전에 맞는 sql pretty printer 를 다운받아서 설치하면 됨.


자세한 사용법은 위 사이트링크에서 확인가능.




예제 소스 파일 :


 출처 :  http://www.dpriver.com/products/sqlpp/ssms_index.php




저작자 표시 비영리 변경 금지
신고
Posted by 잠수콩



제목 : mssql 프로시저 cursor, charindex 사용하여, ','파싱한 값을 그룹핑 카운트




소스 :


alter PROCEDURE SP_VIEW_STATIC_MEMBER_REFERER

AS

BEGIN

DECLARE @pos INT

DECLARE @delimiterLen INT

DECLARE @value VARCHAR(100)

DECLARE @delimiter VARCHAR(10)

SET @delimiter = ',';

SET @delimiterLen = LEN(@delimiter);

SET NOCOUNT ON;

create table #temp(item int)


    DECLARE isReferer_Cursor CURSOR FOR  

    

select replace(isReferer, ' ', '') as isReferer from T_MEMBER where isReferer <> '';

OPEN isReferer_Cursor;  

declare @isReferer varchar(100);

declare @data varchar(100);

FETCH NEXT FROM isReferer_Cursor

into @isReferer;  

WHILE @@FETCH_STATUS = 0  

BEGIN  

SET @data = @isReferer

WHILE LEN(@data) > 0

BEGIN

SET @pos = CHARINDEX(@delimiter, @data)

IF (@pos = 0)

BEGIN

INSERT INTO #temp (item) VALUES (cast(@data as int))

break;

END

else if  (@pos > 0)

begin

SET @value = SUBSTRING(@data, 0, @pos)

SET @data = SUBSTRING(@data, @pos+@delimiterLen, LEN(@data)-@pos )

INSERT INTO #temp (item) VALUES (cast(@value as int))

continue;

end

END

FETCH NEXT FROM isReferer_Cursor into @isReferer;  

END;  

CLOSE isReferer_Cursor;  

DEALLOCATE isReferer_Cursor; 

    

    SELECT item, COUNT(*) as itemCount FROM #temp group by item ORDER BY item asc

    

END

GO




내용 :


isReferer isReferer_etc

1               NULL

2, 6         NULL

3         NULL

2, 7         NULL

1, 4, 5 NULL

1, 2, 3, 8 NULL

9, 10         인터넷

6, 10         지인 소개


//////////////////////////////////

t_member 라는 테이블에 isReferer, isReferer_etc 컬럼을 두고, 회원가입시 회원가입 경로를 체크하는값을 isReferer 에 넣었다면...


해당 가입경로를 group by 하여, 가입경로별 갯수 파악하기 위함.


결과

1        11

2        22

3        33

4        31

5        12        

6        331

7        3

8        55

9        13

10      555







예제 소스 파일 :


 출처 :  




저작자 표시 비영리 변경 금지
신고
Posted by 잠수콩



제목 : mssql 이번주 시작일, 종료일




소스 :



--일 ~ 토

SET DATEFIRST 7;

select dateadd(day, 1-datepart(dw, getdate()), CONVERT(date,getdate())) 

select dateadd(day, 7-datepart(dw, getdate()), CONVERT(date,getdate())) 


--월 ~ 일

SET DATEFIRST 1;

select dateadd(day, 1-datepart(dw, getdate()), CONVERT(date,getdate())) 

select dateadd(day, 7-datepart(dw, getdate()), CONVERT(date,getdate())) 



기본적으로 mssql 의 일주일의 시작일을 '일요일(7)' 이다.

설정을 바꾸려면 아래 참조


SET DATEFIRST { number | @number_var }   
ValueFirst day of the week is
1Monday
2Tuesday
3Wednesday
4Thursday
5Friday
6Saturday
7 (default, U.S. English)Sunday




내용 :





예제 소스 파일 :


 출처 :  https://msdn.microsoft.com/en-us/library/ms181598.aspx




저작자 표시 비영리 변경 금지
신고
Posted by 잠수콩



제목 : mssql 초성 검색 function 만들기




소스 :


1. 아래 쿼리문 실행으로 function 생성


CREATE FUNCTION [dbo].[UTF8_Search1stText]

(

@Value NVARCHAR(MAX)

)

RETURNS NVARCHAR(MAX)

AS 

BEGIN

DECLARE @Rtn NVARCHAR(MAX)

DECLARE @Temp NVARCHAR(2)

DECLARE @SerchValue NVARCHAR(MAX)

DECLARE @i INT

DECLARE @n INT

SET @Rtn = ''

SET @i = 1

SET @n = LEN(@Value)

WHILE @i <= @n

BEGIN

SET @Temp = Substring(@Value, @i, 1)

SET @SerchValue = (CASE @Temp 

WHEN 'ㄱ' THEN '[가-깋]'

WHEN 'ㄲ' THEN '[까-낗]'

WHEN 'ㄴ' THEN '[나-닣]'

WHEN 'ㄷ' THEN '[다-딯]'

WHEN 'ㄸ' THEN '[따-띻]'

WHEN 'ㄹ' THEN '[라-맇]'

WHEN 'ㅁ' THEN '[마-밓]'

WHEN 'ㅂ' THEN '[바-빟]'

WHEN 'ㅃ' THEN '[빠-삫]'

WHEN 'ㅅ' THEN '[사-싷]'

WHEN 'ㅆ' THEN '[싸-앃]'

WHEN 'ㅇ' THEN '[아-잏]'

WHEN 'ㅈ' THEN '[자-짛]'

WHEN 'ㅉ' THEN '[짜-찧]'

WHEN 'ㅊ' THEN '[차-칳]'

WHEN 'ㅋ' THEN '[카-킿]'

WHEN 'ㅌ' THEN '[타-팋]'

WHEN 'ㅍ' THEN '[파-핗]'

WHEN 'ㅎ' THEN '[하-힣]'

ELSE @Temp END)

SET @Rtn = RTRIM(@Rtn) + @SerchValue

SET @i = @i + 1

END

Return @Rtn

END



내용 :


2. 사용법


select * from T_GOODS where PATINDEX(dbo.UTF8_Search1stText('ㄱ') + '%' , Title) > 0






예제 소스 파일 :


 출처 :  




저작자 표시 비영리 변경 금지
신고
Posted by 잠수콩



제목 : mssql 재귀쿼리 기본, 트리구조




소스 :


use TEST


Create Table OrganizationalStructures (

 BusinessUnitID smallint identity(1,1),

 BusinessUnit varchar(100) Not Null,

 ParentUnitID smallint

)

insert into OrganizationalStructures values

('Adventure Works Cycle',NULL),

('Customer Care',1),

('Service',1),

('Channel Sales & Marketing',1),

('Customer Support',2),

('OEM Support',2),

('Central Region',3),

('Eastern Region',3),

('Western Region',3),

('OEM',4),

('Channel Marketing',4),

('National Accounts',4),

('Channel Field Sales',4),

('National Channel Marketing',11),

('Retail Channel Marketing',11),

('Central Region',13),

('Eastern Region',13),

('Western Region',13),

('Bicycles',15),

('Bicycle Parts',15)




select * From OrganizationalStructures



WITH Recursive_CTE AS (

 SELECT

  child.BusinessUnitID,

  CAST(child.BusinessUnit as varchar(100)) BusinessUnit,

  CAST(child.ParentUnitID as SmallInt) ParentUnitID,

  CAST(NULL as varchar(100)) ParentUnit,

  CAST('>> ' as varchar(100)) LVL,

  CAST(child.BusinessUnitID as varchar(100)) Hierarchy,

  1 AS RecursionLevel

 FROM OrganizationalStructures child

 WHERE BusinessUnitID = 1


 UNION ALL


 SELECT

  child.BusinessUnitID,

  CAST(LVL + child.BusinessUnit as varchar(100)) AS BusinessUnit,

  child.ParentUnitID,

  parent.BusinessUnit ParentUnit,

  CAST('>> ' + LVL as varchar(100)) AS LVL,

  CAST(Hierarchy + ':' + CAST(child.BusinessUnitID as varchar(100)) as varchar(100)) Hierarchy,

  RecursionLevel + 1 AS RecursionLevel

 FROM Recursive_CTE parent

 INNER JOIN OrganizationalStructures child ON child.ParentUnitID = parent.BusinessUnitID

)

SELECT * FROM Recursive_CTE ORDER BY Hierarchy




내용 :





예제 소스 파일 :


 출처 :  




저작자 표시 비영리 변경 금지
신고
Posted by 잠수콩



제목 : mssql 재귀쿼리, 임시테이블, 프로시저 사용하여 쿼리추출




소스 :


create PROCEDURE SP_HIERARCHY

@strUserID nvarchar(50)

AS

BEGIN

SET NOCOUNT ON;

    

Create Table #Result ( ID nvarchar(20),

NAME nvarchar(20),

PID nvarchar(20),

Level int );


With CTE(UserID, UserName, RecommendID, Lvl, Hierarchy)

As (


  Select UserID, UserName, RecommendID, 0 As Lvl, CAST(UserID as varchar(100)) Hierarchy

  From T_MEMBER

  Where UserID = @strUserID


  Union all


  Select C.UserID, C.UserName, C.RecommendID, Lvl + 1, CAST(Hierarchy + ':' + CAST(C.UserID as varchar(100)) as varchar(100)) Hierarchy

  From T_MEMBER C inner join CTE D On C.RecommendID = D.UserID

)

insert into #Result (ID, NAME, PID, Level)

Select UserID, UserName, RecommendID, Lvl From CTE 

--where Lvl between 0 and 3     --레벨사용하여 부분 level 만큼만 추출하기

Order by Hierarchy ASC;


select ID, PID, NAME, Level  from #Result T1;


drop table #Result;


SET NOCOUNT OFF

END



내용 :


임시테이블에 재귀쿼리한 내용을 insert 한 후, 해당 임시테이블을 가공하여 사용함.





예제 소스 파일 :


 출처 :  




저작자 표시 비영리 변경 금지
신고
Posted by 잠수콩



제목 : mssql 엑셀에서 데이타 가져오기에서 숫자형, 문자형 필드의 NULL 오류 제대로 값 넣기




소스 :


1. 엑셀에 해당 열을 일단 숫자형으로 변환

2. ALT+F11 을 눌러서 메뉴 > 삽입 > 모듈 선택

3. 

Sub Covert2Text()

Dim Cel As Object

ActiveCell.CurrentRegion.Select

For Each Cel In ActiveCell.CurrentRegion

    With Cel

        If IsNumeric(.Value) Then

        

             .Value = "'" & .Value

        End If

    End With

Next

End Sub


입력 후 실행

4. 엑셀로 돌아가면, 숫자형이었던 것들이 문자형으로 변환됨



내용 :


mssql DTS 중. 데이타 가져오기 에서 엑셀 파일의 데이타 중. 숫자형을 제대로 안가져와서 null 로 들어오는 경우가 있음.


해당 엑셀의 전체 영역을 텍스트로 변환 후,


해당 열의 셀서식을 숫자로 변환.


위 방법으로 실행하면, 숫자형이었던 것들이 ' 가 붙으면서 문자형으로 전환됨.







예제 소스 파일 :


 출처 :  https://support.microsoft.com/ko-kr/kb/602141




저작자 표시 비영리 변경 금지
신고
Posted by 잠수콩



제목 : mssql sp_executesql output 매개변수 사용




소스 :

USE AdventureWorks2012;
GO
DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);
DECLARE @SalesOrderNumber nvarchar(25);
DECLARE @IntVariable int;
SET @SQLString = N'SELECT @SalesOrderOUT = MAX(SalesOrderNumber)
    FROM Sales.SalesOrderHeader
    WHERE CustomerID = @CustomerID';
SET @ParmDefinition = N'@CustomerID int,
    @SalesOrderOUT nvarchar(25) OUTPUT';
SET @IntVariable = 22276;
EXECUTE sp_executesql
    @SQLString
    ,@ParmDefinition
    ,@CustomerID = @IntVariable
    ,@SalesOrderOUT = @SalesOrderNumber OUTPUT;
-- This SELECT statement returns the value of the OUTPUT parameter.
SELECT @SalesOrderNumber;
-- This SELECT statement uses the value of the OUTPUT parameter in
-- the WHERE clause.
SELECT OrderDate, TotalDue
FROM Sales.SalesOrderHeader
WHERE SalesOrderNumber = @SalesOrderNumber;





내용 :

OUTPUT 매개 변수 사용

다음 예에서는 OUTPUT 매개 변수를 사용하여 SELECT 문에서 생성된 결과 집합을 @SQLString 매개 변수에 저장합니다.OUTPUT 매개 변수의 값을 사용하는 두 개의 SELECT 문이 실행됩니다.







예제 소스 파일 :


 출처 :  https://msdn.microsoft.com/ko-kr/library/ms188001(v=sql.120).aspx




저작자 표시 비영리 변경 금지
신고
Posted by 잠수콩