작업 하다가 대리점별 지역 주소를 정렬하는 부분에 사용한 쿼리
지역 코드가 여러개의 혼합으로 되어 있어서 그걸로 안되고 해서
주소의 내용 일부를 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
-- 구성 내용
10초에 한번씩 입력 되는 해당 데이터의 내용을 5분 단위로 끊어서 새로운 테이블로 통계를 낸다.
값이 일정하지도 않을수 있고, 값의 주기가 조금씩 틀릴수도 있다.
우선 테스트를 통해서 더 보완 해야 할듯 하다. (커서를 몰라서 이렇게 걍 작성 했다.)
-- 배울점
1. 시간 값의 구간을 5분 단위로 구분
USE [GPMS]
GO
/****** 개체: StoredProcedure [dbo].[SP_TBL_CPU_MIN_INPUT] 스크립트 날짜: 02/19/2009 16:59:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: 손병욱
-- Create date: 2009-02-12
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[SP_TBL_CPU_MIN_INPUT]
@HOSTNAME VARCHAR(50)
AS
DECLARE @FIRSTTIME DATETIME -- 테이블의 제일 적은 시간값
DECLARE @SNEWTIME VARCHAR(30) -- 적은 시간값의 5분 단위 구분값(임시)
DECLARE @STIME DATETIME -- 저장할 시작 시간 범위값
DECLARE @ETIME DATETIME -- 저장할 마지막 시간 범위값
DECLARE @BET_COUNT INT -- 범위 시간에 포함된 아이템의 개수
-- 제일 위에 있는 시간 값을 가져온다.
SELECT top 1 @FIRSTTIME = regDate FROM tbl_rep_cpu_input order by regDate asc
-- 가져온 시간 값을 사용해서 분 단위로 구분한다.
SET @SNEWTIME = DBO.GETSTIMEPER5MINTODATETIME(@FIRSTTIME)
-- 구분된 임시 문자열을 시간값으로 변환(저장할 시간 시간 범위값)
SET @STIME = DBO.CHANGEDATETIME(@SNEWTIME)
-- 마지막 범위값을 구한다.
SET @ETIME = DATEADD(MI, 5, @STIME)
--
--PRINT @SNEWTIME
--PRINT @STIME
--PRINT @ETIME
-- 범위 안에 포함된 개수를 구한다.
SELECT @BET_COUNT = COUNT(*) FROM DBO.TBL_REP_CPU_INPUT WHERE hostName=@HOSTNAME and regDate >= @STIME and regDate < @ETIME
--
--PRINT @BET_COUNT
IF @BET_COUNT > 0
BEGIN -- @BET_COUNT > 0
-- 범위 값을 저장하기 위해서
-- 범위의 시작 값을 키로 사용하게 된다.
-- 이때 이 값이 이미 존재 하는지를 판단해야 한다.
DECLARE @FINDKEY INT
SELECT @FINDKEY = COUNT(*) FROM DBO.TBL_REP_CPU_MIN WHERE regDate=@STIME
--
--PRINT @FINDKEY
-- value의 총 합을 구한다.
DECLARE @SUMVALUE INT
SELECT @SUMVALUE = SUM(value) FROM DBO.TBL_REP_CPU_INPUT WHERE hostName=@HOSTNAME and regDate >= @STIME and regDate < @ETIME
--
--PRINT @SUMVALUE
-- @FINDKEY의 값이 0 이면 하나도 존재 하지 않는 것이기 때문에 INSERT를 한다.
IF @FINDKEY = 0
INSERT INTO [GPMS].[dbo].[TBL_REP_CPU_MIN]
([hostName]
,[value]
,[valueSum]
,[valueCount]
,regDate)
VALUES
(@HOSTNAME
,0
,@SUMVALUE
,@BET_COUNT
,@STIME)
ELSE
--PRINT '여기야'
UPDATE DBO.TBL_REP_CPU_MIN
SET [valueSum] = [valueSum] + @SUMVALUE
,[valueCount] = [valueCount] + @BET_COUNT
WHERE hostName=@HOSTNAME and regDate=@STIME
END --@BET_COUNT > 0
-- 추가한 후에 해당 데이터는 삭제한다.(테스트로 템프로 옮기고 삭제)
INSERT TBL_REP_CPU_INPUT_TMP
SELECT hostName, value, regDate, flag FROM TBL_REP_CPU_INPUT WHERE hostName=@HOSTNAME and regDate >= @STIME and regDate < @ETIME
DELETE FROM DBO.TBL_REP_CPU_INPUT WHERE hostName=@HOSTNAME and regDate >= @STIME and regDate < @ETIME
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
조인 테이블 테스트시 필요한 쿼리문
학생, 동아리 테이블은 기본으로 생성됨
학생동아리 테이블은 FK로 구성되어짐 -------------------------------------------------------------------------------------------
use sqlDB
create table stdTbl
(
stdName nvarchar(10) not null primary key,
addr nchar(4) not null
)
go
create table clubTbl
(
clubName nvarchar(10) not null primary key,
roomNo nchar(4) not null
)
go
create table stdclubTbl
(
num int identity not null primary key,
stdName nvarchar(10) not null
foreign key references stdTbl(stdName),
clubName nvarchar(10) not null
foreign key references clubTbl(clubName)
)
go
insert into stdTbl values('박지성', '서울')
insert into stdTbl values('박주영', '경기')
insert into stdTbl values('조재진', '충북')
insert into stdTbl values('이천수', '인천')
insert into stdTbl values('안정환', '강원')
go
insert into clubTbl values('수영', '101호')
insert into clubTbl values('바둑', '102호')
insert into clubTbl values('축구', '103호')
insert into clubTbl values('봉사', '104호')
go
insert into stdclubTbl values('박지성', '바둑')
insert into stdclubTbl values('박지성', '축구')
insert into stdclubTbl values('조재진', '축구')
insert into stdclubTbl values('이천수', '축구')
insert into stdclubTbl values('이천수', '봉사')
insert into stdclubTbl values('안정환', '봉사')
go
DB 제작할때 쿼리 문은 항상 작성하는 습관을 들이자.
이 코드를 통해서 여러가지 테스트할때 바로바로 적용해볼 수 있도록 하기 위함이다.
[순서]
1. UDL 파일을 만들어서 DB 커넥션 문을 저장해 놓는다.
2. 쿼리 테이블을 제작
3. 필요하면 로그인 계정 추가
4. 샘플 쿼리 문을 만든다. 아래와 같이
use exam
go
-- 고객 테이블
Create Table dbo.Customers
(
CustomerID int Identity(1,1) Primary Key Not NULL, --일련번호
Name Varchar(25) Null, --이름
Email Varchar(100) Null, --이메일
RegistDate DateTime Default(GetDate()) --등록일
)
Go
--입력 예시
Insert Customers Values('홍길동', 'h@h.com', GetDate())
--출력 예시
Select * From Customers Order By CustomerID Desc
--세부출력 예시
Select * From Customers Where Name = '홍길동'
--수정 예시
Begin Tran
Update Customers
Set Name = '백두산', Email = 'b@b.com'
Where Name = '홍길동' And Email = 'h@h.com'
--RollBack Tran
Commit Tran
--삭제 예시
Delete Customers Where Name = '백두산'
--검색 예시
Select * From Customers
Where Name Like '%백%' Or Email Like '%b%'
