'Query'에 해당되는 글 18건
- 2009/07/07 [MSSQL] 존재하는 테이블에 다른 테이블 내용 복사하기
- 2009/06/03 [DB&Query] 트리구조 하위노드 삭제쿼리
- 2009/05/25 조직도와 비슷한 재귀적 쿼리(Query) 예문. (1)
- 2009/05/25 sum 처리한 테이블과 회원 테이블의 inner join 예제
- 2009/05/07 [MSSQL] while 로 반복 작업하는 코드
- 2009/04/24 [MSSQL] exec 구문 사용시 특수 문자의 변경이 필요하다.
- 2009/04/24 [MSSQL] sp_executesql 사용법. (1)
- 2009/04/15 [MS-SQL] A 테이블에 B의 내용 중에서 일부 컬럼만 추가 하는 쿼리
- 2009/03/07 한국 지역별 소트 하기
- 2009/02/19 10초에 한번씩 입력되는 데이터를 5분간 통계를 내는 저장 프로시저
- 2009/02/19 문자열을 DATETIME로 변경해 주는 함수
- 2009/02/19 DATETIME 값을 5분 단위로 잘라서 문자열로 반환하는 함수#2
- 2009/02/19 DATETIME 값을 5분 단위로 잘라서 문자열로 반환하는 함수
- 2008/10/16 방명록(GuestBook) 기본 코드
- 2008/10/16 애플리케이션 및 세션 카운터 처리 코드
insert into aaa.dbo.aa select * from bbb.dbo.bb;
-- 이걸 사용하게 되면 다른 DB의 같은 테이블 구조를 가지고 있을때 내용을 복사할 수 있다.
DROP TABLE #MyTempTable; --임시테이블을 만든다.
-- CTE 구조로 해서 원하는 내용을 뽑아 온다.
with empCTE(
_idx,
_uid,
_puid)
as
(
-- boss only
select idx, uid, puid
from userList_tmp
where uid = 'test1' and grade = '2' --최초 탐색 시점
union all
select aa.idx, aa.uid, aa.puid
from userList_tmp as aa inner join empCTE as bb
on aa.puid = bb._uid
--where bb.level < 3
)
select _idx into #MyTempTable from empCTE --임시테이블로 복사
GO
-- 임시 테이블의 내용을 원본 테이블에서 삭제
delete from userList_tmp where idx in (select * from #MyTempTable) 제
 |
위쪽의 쿼리 결과 리스트에서 보면 uid, puid, grade 등의 조직도를 구성할 수 있는 정보가 존재 할때
아래와 같은 결과를 만들어 낼 수 있다.
[쿼리예]
as
(
-- 보스
select uid, puid, grade, 0
from userList
where puid is null and grade = '0'
union all
-- 나머지 grade를 포함한 내용
select aa.uid, aa.puid, aa.grade, bb.level+1
from userList as aa inner join empCTE as bb
on aa.puid = bb.empUid
where bb.level < 3 -- 이 코드가 없어지면 모든 grade에 대해서 출력이 된다. 즉, grade를 제한할때 사용
)
select replicate('ㄴ', level) + empUid as uid, dept from empCTE order by empUid, level
 |
여기서 userList의 테이블과 calcInfo 의 테이블에 대해서 합치는 쿼리를 구성해 볼려구 한다.
두개의 테이블의 내용은 아래와 같다.
[userList]
 |
[calcInfo]
 |
두개의 테이블을 조인해야 하는데 문제는
calcInfo의 uid별 income, outcome을 sum을 해야 하는게 관건이였다.
쿼리는 잘 모르다 보니 이런 구문은 항상 아리송하다 ^^
아래는 결과 쿼리다.
inner join
(select uid, sum(income) as income, sum(outcome) as outcome from calcInfo as bb group by uid)
bb on aa.uid = bb.uid
[출력화면]
 |
declare @idx int
declare @account varchar(20)
declare @nickname varchar(20)
declare @account2 varchar(20)
declare @nickname2 varchar(20)
set @idx = 0
set @account = 'test'
set @nickname = 'Dummy'
while (1=1)
begin
if @idx > 9999 break;
set @account2 = @account + convert(varchar(4), @idx)
set @nickname2 = @nickname + convert(varchar(4), @idx)
print @idx
print @account2 + @nickname2
INSERT INTO [NDT].[dbo].[USERACCOUNT]
([ACCOUNT]
,[PASSWORD]
,[NICKNAME]
,[MONEY]
,[LOGINSTATE])
VALUES
(@account2
,'1'
,@nickname2
,0
,0)
set @idx = @idx + 1
end
게시판을 생성하다 보면 게시물의 내용에 특수문자가 들어 가게 되는데
그것 자체가 쿼리 문의 실패를 가져 오게 된다.
그래서 쿼리문 자체에서 치환을 해주는 형태로 진행하고,
웹에서 데이터를 가져온 다음에 변환해 주는 것으로 작업을 마무리 해봤다.
--DB에서 값을 변환하는 구문
GO
/****** 개체: StoredProcedure [dbo].[sp_ReplyInsert] 스크립트 날짜: 04/24/2009 14:55:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_ReplyInsert]
(
@TableName varchar(100),
@Name varchar(25),
@Email varchar(100),
@Title varchar(150),
@PostIP varchar(15),
@Content varchar(1000),
@Password varchar(20),
@Encoding varchar(10),
@Homepage varchar(100),
@Ref varchar(10),
@Step varchar(10),
@RefOrder varchar(10)
)
AS
SET NOCOUNT OFF;
declare @sql varchar(500)
set @sql= 'INSERT INTO ' + @TableName +
'([Name], [Email], [Title], [PostIP], [Content], [Password], [Encoding], [Homepage], [Ref], [Step], [RefOrder]) ' +
'VALUES ( ''' +
@Name+''', '''+
@Email+''', '''+
@Title+''', '''+
@PostIP+''', '''+
REPLACE(@Content, char(39), '%#39;')+''', '''+
@Password+''', '''+
@Encoding+''', '''+
@Homepage+''', '''+
@Ref+''', '''+
@Step+''', '''+
@RefOrder+ ''' )';
웹에서는 반대로 진행한다.
{
tmpContent =
list[0].Content.ToString().Replace("&", "&").Replace("<", "<").Replace(">", ">").Replace("\r\n", "<br>").Replace("%#39;", "'");
}
우선 이렇게 해서 다른 문제점이 생기는지 파악해 보자.
asp.net 을 하면서 게시판을 제작하다 보면
정말 초보자 수준에서 보게 되면, 게시판 같은 경우
같은 테이블을 사용해야 하는 상황이 생기는데, 이런 경우 저장 프로시저를 각 테이블 별로
제작 하게 되면 작업사항도 번거로워 지는걸 발견해서
저장 프로시저를 동적 테이블을 구성해 볼려고 했다.
쩝.. 세상 프로그램은 쉬운게 없더라..
가장 쉽게 저장 프로시저를 구성하면 아래와 같아 진다.
(
@Num int
)
AS
SET NOCOUNT ON;
SELECT * FROM dbo.Reply Where Num=@Num
이런 경우에 쉽게 생각 하면 dbo.@TableName 이런 식의 표현이 가능할 줄 알았다.
하지만 이렇게 할 경우 허용이 되지 않는 것을 발견하였다.
그래서 이런 경우 exec()를 사용해서 해당 테이블을 만든 다음에 적용해야 한다.
set @sql='Select * From Reply Where ' + @strSearchField + ' Like ''%' + @strSearchQuery + '%'' Order By Num Desc';
exec(@sql)
하지만 이렇게 했을때 결과는 제대로 나오지만 asp.net에서 데이터 셋을 연동하게 되면
제대로 표현이 안되는 경우가 있다.
하나의 예를 보면..
(
@Num int
)
AS
SET NOCOUNT ON;
SELECT * FROM dbo.Reply Where Num=@Num
순수하게 위의 코드를 수행하게 되면 * 부분에서 Reply 의 모든 컬럼을 리턴 시킨다.
그래서 데이터 셋에 서버탐색기를 통해서 저장 프로시저를 드래그앤 드롭을 하게 되면
아래와 같은 내용으로 적용이 된다.
 |
이런 식의 테이블과 테이블 어뎁트가 같이 구성이 된다.
하지만 문제는 exec()를 실행하게 되면 결과는 int 형태로만 나오기 때문에,
테이블 형태로 받아야 할때가 필요 하게 되더라
그래서 찾아보다 보니 sp_executesql 이게 필요하다는 것을 알게 되었다.
아래 부분은 해당 쿼리를 보내고 나서, 보낸 내용중에서 필요한 컬럼만 리턴해 보기 위한 쿼리 내용입니다.
@TableName varchar(100)
AS
SET NOCOUNT ON;
declare @v_Num int
declare @v_Name varchar(25)
declare @v_Email varchar(100)
declare @v_Title varchar(150)
declare @v_PostDate datetime
declare @v_PostIP varchar(15)
declare @v_Content varchar(1000)
declare @v_Password varchar(20)
declare @v_ReadCount int
declare @v_Encoding varchar(10)
declare @v_Homepage varchar(100)
declare @v_ModifyDate datetime
declare @v_ModifyIP varchar(15)
declare @v_Ref int
declare @v_Step int
declare @v_RefOrder int
--exec(@sql)
exec sp_executesql
@sql,
N'@v_Num int out, @v_Name varchar(25) out,
@v_Email varchar(100) out, @v_Title varchar(150) out, @v_PostDate datetime out,
@v_PostIP varchar(15) out, @v_Content text out, @v_Password varchar(20) out,
@v_ReadCount int out, @v_encoding varchar(10) out, @v_Homepage varchar(100) out,
@v_ModifyDate datetime out, @v_ModifyIP varchar(15) out,
@v_Ref int out, @v_Step int out, @v_RefOrder int out',
@v_Num out,
@v_Name out,
@v_Email out,
@v_Title out,
@v_PostDate out,
@v_PostIP out,
@v_Content out,
@v_Password out,
@v_ReadCount out,
@v_Encoding out,
@v_Homepage out,
@v_ModifyDate out,
@v_ModifyIP out,
@v_Ref out,
@v_Step out,
@v_RefOrder out
select @v_Num as Num,
@v_Name as Name,
@v_Email as Email,
@v_Title as Title,
@v_PostDate as PostDate,
@v_PostIP as PostIP,
@v_Content as Content,
@v_Password as Password,
@v_Readcount as ReadCount,
@v_Encoding as Encoding,
@v_Homepage as Homepage,
@v_ModifyDate as ModifyDate,
@v_ModifyIP as ModifyIP,
@v_Ref as Ref,
@v_Step as Step,
@v_RefOrder as RefOrder
모든 컬럼 내용을 다 복사 하지는 못한다.
그래서 아래와 같이 추가할 컬럼을 지정한 후에 복사를 해야 한다.
INSERT INTO userList
([uid]
,[passwd]
,[name]
,[phone]
,[email]
,[jumin1]
,[jumin2]
,[zipcode1]
,[zipcode2]
,[address1]
,[address2]
,[regdate]
,[level]
,[exp]
,[cash]
,[gem]
,[IsBlocked]
,[IsGameUser]
,[gameScore]
,[gameCash]
,[grade])
select a.uid, a.passwd, a.name, a.phone, a.email,
a.jumin1, a.jumin2, a.zipcode1, a.zipcode2, a.address1, a.address2,
a.regdate, a.level, a.exp, a.cash, a.gem, a.IsBlocked, a.IsGameUser,
a.gameScore, a.gameCash, a.grade
from leave_userList a
where idx=3
지역 코드가 여러개의 혼합으로 되어 있어서 그걸로 안되고 해서
주소의 내용 일부를 order by 해서 사용하도록 했다.
SELECT * FROM dbo.Agency order by case left(ConAddr,2)
when '서울' then 1
when '경기' then 2
when '인천' then 3
when '강원' then 4
when '대전' then 5
when '충남' then 6
when '충분' then 7
when '부산' then 8
when '울산' then 9
when '경남' then 10
when '대구' then 11
when '경북' then 12
when '광주' then 13
when '전남' then 14
when '전북' then 15
when '제주' then 16
end
-- 최초 입력 데이터
idx hostName value regDate flag
----------- -------------------------------------------------- ----------- ----------------------- ----
15934 P6901756001C 4 2009-02-19 16:25:36.150 0
15935 P6901756001C 5 2009-02-19 16:25:46.150 0
15936 P6901756001C 1 2009-02-19 16:25:56.167 0
15937 P6901756001C 3 2009-02-19 16:26:06.150 0
15938 P6901756001C 3 2009-02-19 16:26:16.150 0
15939 P6901756001C 3 2009-02-19 16:26:26.150 0
15940 P6901756001C 2 2009-02-19 16:26:36.150 0
15941 P6901756001C 2 2009-02-19 16:26:46.150 0
15942 P6901756001C 1 2009-02-19 16:26:56.150 0
15943 P6901756001C 2 2009-02-19 16:27:06.150 0
15944 P6901756001C 1 2009-02-19 16:27:16.150 0
-- 변경후 데이터
idx hostName value valueSum valueCount regDate flag
----------- -------------------------------------------------- ----------- ----------- ----------- ----------------------- ----
5 P6901756001C 0 50 20 2009-02-19 15:45:00.000 0
6 P6901756001C 0 99 30 2009-02-19 15:50:00.000 0
7 P6901756001C 0 97 30 2009-02-19 15:55:00.000 0
8 P6901756001C 0 287 30 2009-02-19 16:00:00.000 0
9 P6901756001C 0 218 30 2009-02-19 16:05:00.000 0
10 P6901756001C 0 362 30 2009-02-19 16:10:00.000 0
11 P6901756001C 0 131 30 2009-02-19 16:15:00.000 0
12 P6901756001C 0 163 30 2009-02-19 16:20:00.000 0
13 P6901756001C 0 23 3 2009-02-19 16:25:00.000 0
-- 구성 내용
10초에 한번씩 입력 되는 해당 데이터의 내용을 5분 단위로 끊어서 새로운 테이블로 통계를 낸다.
값이 일정하지도 않을수 있고, 값의 주기가 조금씩 틀릴수도 있다.
우선 테스트를 통해서 더 보완 해야 할듯 하다. (커서를 몰라서 이렇게 걍 작성 했다.)
-- 배울점
1. 시간 값의 구간을 5분 단위로 구분
더보기
USE [GPMS]
GO
/****** 개체: UserDefinedFunction [dbo].[CHANGEDATETIME] 스크립트 날짜: 02/19/2009 16:57:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- 날짜변환하는부분을함수로작성
CREATE FUNCTION [dbo].[CHANGEDATETIME] (
@sdate varchar(30)
)RETURNS DATETIME
AS
BEGIN
DECLARE @RETURNVALUE DATETIME
BEGIN
SELECT @RETURNVALUE=CASE WHEN CHARINDEX('오후',@sdate)>0
THEN DATEADD(HH,12,CONVERT (DATETIME, REPLACE(@sdate,'오후','')))
ELSE CONVERT (DATETIME,REPLACE(@sdate,'오전','') )
END
END
RETURN(@RETURNVALUE)
END
-- 결과
문자열을 시간 값으로 변경해 준다.
하지만 문자열이 시간값과 같은 포멧으로 구성되어 있어야 한다.
ex) 2009-02-19 14:25:00
USE [GPMS]
GO
/****** 개체: UserDefinedFunction [dbo].[GETSTIMEPER5MINTODATETIME] 스크립트 날짜: 02/19/2009 16:55:38 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
/*
GETDATETIMEPERMIN : 2006-09-19 BY J.Y. PARK,
DESC: 입력받은 DATETIME의 초,밀리세컨드를 0으로 수정하고 5분단위 시간으로 반환
*/
CREATE FUNCTION [dbo].[GETSTIMEPER5MINTODATETIME]
( @IDATETYPE DATETIME)
RETURNS VARCHAR(20)
AS
BEGIN
DECLARE @SNEWTIME VARCHAR(20)
DECLARE @SMIN VARCHAR(5)
SET @SNEWTIME = DATENAME(YY,@IDATETYPE)
SET @SNEWTIME = @SNEWTIME + '-' + DATENAME(MM,@IDATETYPE)
IF DATEPART(DD,@IDATETYPE) < 10
SET @SNEWTIME = @SNEWTIME + '0'
SET @SNEWTIME = @SNEWTIME + '-' + DATENAME(DD,@IDATETYPE) + ' '
IF DATEPART(HH,@IDATETYPE) < 10
SET @SNEWTIME = @SNEWTIME + '0'
SET @SNEWTIME = @SNEWTIME + DATENAME(HH,@IDATETYPE)
IF DATEPART(MI,@IDATETYPE) < 10
SET @SMIN = '0' + DATENAME(MI,@IDATETYPE)
ELSE
SET @SMIN = DATENAME(MI,@IDATETYPE)
IF (DATEPART(MI,@IDATETYPE) % 10) >= 5
SET @SNEWTIME = @SNEWTIME + ':' + LEFT(@SMIN,1) + '5'
ELSE
SET @SNEWTIME = @SNEWTIME + ':' + LEFT(@SMIN,1) + '0'
RETURN @SNEWTIME
END
-- 결과
GETSTIMEPER5MIN 와 비슷하지만 이건 이 문자열을 DateTime로 변환이 가능한
문자열 형태로 반환한다.
USE [GPMS]
GO
/****** 개체: UserDefinedFunction [dbo].[GETSTIMEPER5MIN] 스크립트 날짜: 02/19/2009 16:54:01 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
/*
GETDATETIMEPERMIN : 2006-09-19 BY J.Y. PARK,
DESC: 입력받은 DATETIME의 초,밀리세컨드를 0으로 수정하고 5분단위 시간으로 반환
*/
CREATE FUNCTION [dbo].[GETSTIMEPER5MIN]
( @IDATETYPE DATETIME)
RETURNS VARCHAR(20)
AS
BEGIN
DECLARE @SNEWTIME VARCHAR(20)
DECLARE @SMIN VARCHAR(5)
SET @SNEWTIME = DATENAME(YY,@IDATETYPE)
SET @SNEWTIME = @SNEWTIME + DATENAME(MM,@IDATETYPE)
IF DATEPART(DD,@IDATETYPE) < 10
SET @SNEWTIME = @SNEWTIME + '0'
SET @SNEWTIME = @SNEWTIME + DATENAME(DD,@IDATETYPE)
IF DATEPART(HH,@IDATETYPE) < 10
SET @SNEWTIME = @SNEWTIME + '0'
SET @SNEWTIME = @SNEWTIME + DATENAME(HH,@IDATETYPE)
IF DATEPART(MI,@IDATETYPE) < 10
SET @SMIN = '0' + DATENAME(MI,@IDATETYPE)
ELSE
SET @SMIN = DATENAME(MI,@IDATETYPE)
IF (DATEPART(MI,@IDATETYPE) % 10) >= 5
SET @SNEWTIME = @SNEWTIME + LEFT(@SMIN,1) + '5'
ELSE
SET @SNEWTIME = @SNEWTIME +LEFT(@SMIN,1) + '0'
RETURN @SNEWTIME
END
-- 결과는 아래와 같이 나옵니다.
2009-02-19 14:23:41 -> 20090219142340
방명록 기본 코드
이름, 이메일, 컨텐츠, 비밀번호, 입력날짜, 입력 IP로 구성된 심플 방명록이다.
DB에 Insert 하는 것과 Select로 GridView에 추가 하는 내용이 포함되어 있다.
DB 쿼리 기본 내용
더보기
cs 기본 파일
더보기
애플리케이션 카운터 설정 및 세션 카운터 설정 방법
애플리케이션은 현재 애플리케이션이 생성된 때부터 카운터 되는 고유한 값이다.
세션 카운터는 접속한 사용자에 따라서 다르게 나타나는 카운터 값이다.
** 이때 애플리케이션 및 세션 카운터를 초기화를 해주고 싶다면 Global.aspx 에서 제공된 메소드에서
처리해야 한다.
cs 기본 파일
더보기
Prev
Rss Feed