Microsoft SQL Server 2005 는 정수, 문자열, 날짜 등의 기본적인 데이터 형식에 대한 강력한 인덱싱 및 쿼리 기능을 제공합니다. 하지만 복잡한 데이터가 포함된 대형 데이터 집합의 경우 기존의 인덱싱 및 쿼리 기능만으로는 충분하지 않을 수 있습니다. 공간 예제에서는 보다 복잡한 데이터 형식과 이러한 데이터에 대한 쿼리를 지원하도록 SQL Server 2005 의 CLR(공용 언어 런타임) 통합 기술을 사용하여 기본적인 인덱싱 및 쿼리 기능을 향샹시키는 방법을 보여 줍니다. 공간 예제는 지형 공간적 쿼리 및 천체적 쿼리를 지원하도록 만들어졌지만 예제의 기본 개념은 다른 많은 종류의 데이터에 사용될 수 있습니다.
이 예제에는 다음이 포함되어 있습니다.
1. 공간 정보를 표현하고 조작하기 위한 전체 클래스 라이브러리
2. 공간 정보가 있는 예제 데이터베이스
3. 예제의 기본 개념과 사용된 알고리즘에 대한 자세한 설명서
4. 설치 및 테스트 스크립트
설치 디렉터리: drive:\Program Files\Microsoft SQL Server\90\Samples\Engine\Programmability\CLR\Spatial\
시나리오 Adventure Works Cycles 의 개발자인 Jane은 공간 정보를 표현, 조작 및 쿼리해야 합니다.
언어 Transact-SQL, Visual C# 및 Visual Basic
기능 공간 예제는 다음과 같은 Microsoft SQL Server 기능을 사용합니다.
응용 프로그램 영역 - 전체
기능 - CLR, 테이블 값 함수, Transact-SQL
필수 구성 요소 이 예제를 실행하기 전에 다음 소프트웨어가 설치되어 있는지 확인하십시오.
Microsoft SQL Server 2005 또는 Microsoft SQL Server 2005 Express Edition(SQL Server Express). SQL Server 2005 Express Edition 설명서 및 예제 웹 사이트에서 무료로 SQL Server Express 를 구할 수 있습니다.
SQL Server 2005 데이터베이스 엔진 예제. 이 예제는 SQL Server 2005 에 포함되어 있습니다. SQL Server Developer 웹 사이트에서 최신 버전의 예제를 다운로드할 수 있습니다.
.NET Framework SDK 2.0 또는 Microsoft Visual Studio 2005. .NET Framework SDK는 무료로 구할 수 있습니다. 자세한 내용은 .NET Framework SDK 설치를 참조하십시오.
예제 빌드
강력한 이름 키 파일을 생성하지 않았다면 다음 지침에 따라 해당 키 파일을 생성합니다.
강력한 이름 키 파일을 생성하려면
Microsoft Visual Studio 2005 명령 프롬프트를 엽니다. 시작을 클릭하고 모든 프로그램, Microsoft .NET Framework SDK 2.0을 차례로 가리킨 다음 SDK 명령 프롬프트를 클릭합니다.
-- 또는--
Microsoft .NET Framework 명령 프롬프트를 엽니다. 시작을 클릭하고 모든 프로그램, Microsoft .NET Framework SDK 2.0을 차례로 가리킨 다음 SDK 명령 프롬프트를 클릭합니다.
디렉터리 변경 명령(CD)을 사용하여 명령 프롬프트 창의 현재 디렉터리를 예제가 설치된 폴더로 변경합니다.참고:
예제가 있는 폴더를 확인하려면 시작 단추를 클릭하고 모든 프로그램, Microsoft SQL Server 2005, 설명서 및 자습서를 차례로 가리킨 다음 예제 디렉터리를 클릭합니다. 기본 설치 위치를 사용하면 예제는 :\Program Files\Microsoft SQL Server\90\Samples에 있습니다.
명령 프롬프트에서 다음 명령을 실행하여 키 파일을 생성합니다.
sn -k SampleKey.snk
중요:
강력한 이름 키 쌍에 대한 자세한 내용은 MSDN의 .NET Development Center에서 "Security Briefs: Strong Names and Security in the .NET Framework"를 참조하십시오.
공간 예제를 빌드하려면
Visual Studio 2005 와 제공된 Visual Studio 솔루션을 사용하거나 .NET Framework SDK 2.0에 포함되어 있는 Microsoft MSBuild 를 사용하여 예제를 컴파일합니다. .NET Framework 명령 프롬프트에서 다음과 비슷한 명령을 실행합니다.
SQL Server 데이터베이스 엔진 예제를 기본 위치에 설치하지 않은 경우 예제가 설치된 위치를 나타내도록 Scripts\spHtmCsharp.sql에서 스크립트의 CREATE ASSEMBLY 부분에 포함된 경로를 수정하고 Scripts\AttachSpatialDatabase.bat 파일에서 데이터베이스의 경로를 수정합니다.
명령 프롬프트 창에서 Scripts\AttachSpatialDatabase.bat 파일을 실행하여 Spatial 데이터베이스를 연결합니다.
사용 중인 SQL Server 인스턴스의 관리자가 아닌 경우 설치를 완료하기 위해 관리자로부터 CreateAssembly 권한을 부여 받아야 합니다.
SQL Server Management Studio 에서 scripts\spHtmCsharp.sql을 엽니다. 파일에 포함된 스크립트를 실행하거나 명령 프롬프트 창에서 다음과 비슷한 명령을 실행합니다.
sqlcmd -E -I -i Scripts\spHtmCsharp.sql
예제 실행
공간 예제를 실행하려면
Management Studio 에서 Scripts\TestScript.sql 파일을 엽니다. 파일에 포함된 스크립트를 실행하거나 명령 프롬프트 창에서 다음과 비슷한 명령을 실행합니다.
sqlcmd -E -I -i Scripts\TestScript.sql
설명
이 예제가 제대로 작동되려면 SQL Server 2005 또는 SQL Server Express 의 CLR을 설정해야 합니다.
예제는 교육용으로만 제공되므로 프로덕션 환경에서 사용하기에 적합하지 않으며 프로덕션 환경에서 테스트되지 않았습니다. Microsoft에서는 이러한 예제에 대해 기술 지원을 제공하지 않습니다. 시스템 관리자의 허가 없이 프로덕션 SQL Server 데이터베이스 또는 보고서 서버에 예제 응용 프로그램 및 어셈블리를 연결하거나 사용해서는 안 됩니다.
요약
한글 NT Option Pack을 설치하면 IIS 4.0 이 설치되고 Default 웹 사이트의 기본 로그 파일 형식은 “W3C Extended Log File” 입니다. 그리고 이 로그 파일형식에 의해서 만들어지는 로그 파일을 보면 시간 필드값이 현지 시간( 시스템 설정 시간)으로 나타나지 않고 그리니치 표준시간으로 설정됩니다. 이것은 제품의 하자가 아니고 기본 제품 스펙에 따른 것이기 때문에 이점을 알고 로그파일을 이용하여야 적절한 결과를 얻을 수 있습니다. 이번 자료에 서는 이에 대한 자세한 설명을 덧붙였습니다.
추가 정보
W3C Extended Log File Format을 사용할 경우는 시간 값이 GMT 표준 시간으 로 기록되는데 즉, 현지 시간이 10시 44분이고 한국 시간대가 설정된 시스템 이라면 로그파일에는 1시 44분으로 기록되게 됩니다. 즉, GMT 표준시간과 9 시간의 시간차가 나타나는 현상이 있습니다. 이것은 만약 로그 파일을 분석 하는 자체 프로그래밍을 해야 된다면 이점을 인식하고 프로그램을 수정해야 된다는 결론이 납니다.
이를 해소하기 위한 방안은 다음 두가지가 있습니다. • 로그 파일 분석 프로그램의 소스를 변경하는 방법
프로그래밍을 할 때 현지 시간대를 미리 알고 (시스템 구성값을 알아내면 됨 ) 그 값에 해당되는 값만큼 로그 파일의 시간에 더하여 주면 이러한 차이를 극복할 수 있습니다.
• Microsoft IIS Log File 로그 파일 형식을 사용하는 방법
이 로그파일 형식은 IIS 3.0과 호환성을 유지하기 위해 계속 지원하는 로그 파일 형식 입니다. W3C Extended 로그파일 형식에 비해 기능이 다소 떨어지 지만, 시간 설정값을 현지 시간을 그대로 반영하고 있기 때문에 만약, 로그 파일 분석 프로그램을 수정할 수 없는 상황에서는 이 방법을 사용하면 됩니 다.
해당 웹 사이트의 Property 대화상자에서 Web site 탭에서 로그 파일 형식을 변경하시면 됩니다. 기본 설정은 W3C Extended Log 파일 형식입니다.
In this article, Carlos explains how to integrate XML into your applications using ASP.
Summary:
This article details an approach to integrating XML into database-driven applications or websites using classic ASP. The goal of this approach is to encapsulate database interactions into a reusable object that will handle data retrieval, including simple hierarchical data, by leveraging ADO and XML.
The result is the beginning of a framework that allows the separation of the data, business and data-access layers. This, in turn, facilitates the implementation of distributed application architectures.
Introduction:
By now most web developers have heard of XML and of its value to the web and distributed computing. In classic ASP, Microsoft has provided the pieces for developers to take advantage of XML in different ways. This article suggests a framework for making database driven ASP applications using only XML. It achieves this goal by encapsulating data retrieval into a VBScript class.
Prerequisites:
This article requires that the reader be familiar with the following:
• VBScript Classes and the advantages of OO design,
• XML, XSL, their use in ASP, and advantages, and
• ADO and its use in ASP, particularly persistence of Recordsets and Data-Shaping.
Overview:
Many articles have been written demonstrating object-oriented (OO) and XML features available in ASP including:
1. Creating a Datagrid Class in classic ASP - 2-Dimensional Arrays by Brian O'Connell 2. A Generic GetRows VBScript Class 3. Using Classes in ASP (OOP in ASP/VBScript - Part #2) by Marcus Brinkhoff 4. Converting Hierarchical Recordsets Into XML By Richard Chisholm 5. Increasing ConvertRStoXML() Performance By David O'Neill 6. An ASP Class for XML Data Transfer by Seth Juarez
These articles adequately explain the benefits of OO design and of XML use in ASP. The data access objects even show how OO design can help manage creation and destruction of precious ADO connection objects.
However, they still either bind the interface to the data (as in the DataGrid article) or they still looped through recordsets or arrays to create their XML. It was not until Transforming ADO Recordsets to XML with XSLT that we begin to see that we can piece the tools available in the classic ASP platform to create a reusable, extensible, and flexible framework with XML as the foundation.
XDO seeks to implement such a framework and makes it possible to retrieve XML data from the database. This allows the developer to have SQL Server-like XML capabilities with any database that can be accessed with ADO via OLEDB.
Design:
Current architectures for distributed applications recommend object models that follow object factory (or engine – collection – class) design patterns. XDO emulates this design pattern for the data access tier in n-tier architectures. Business rules, captured as function libraries or as other classes, can create an instance of the XDO object and return XML that matches the rule coded within the called function or object method. The resulting XML is then transformed with XSLT to render the user interface (See figure 1).
XDO is a custom data access object that encapsulates the functionality of MSADO objects. In a “real-world” implementation, no ASP page interfaces with ADO objects directly. Instead, ASP pages create instances of the XDO object, which in turn interacts with ADO. Instances of ADO Connections and Recordsets are managed by XDO. Furthermore, Business objects can encapsulate the instantiation of XDO objects.
Examples include a Customer XML Engine or a Catalog XML Engine. This adds another layer of abstraction in the object model thereby separating business rules from the presentation (display) code.
XDO is a custom data access object that encapsulates the functionality of MSADO objects. In a “real-world” implementation, no ASP page interfaces with ADO objects directly. Instead, ASP pages create instances of the XDO object, which in turn interacts with ADO. Instances of ADO Connections and Recordsets are managed by XDO. Furthermore, Business objects can encapsulate the instantiation of XDO objects.
Examples include a Customer XML Engine or a Catalog XML Engine. This adds another layer of abstraction in the object model thereby separating business rules from the presentation (display) code.
The required components of this framework are:
• IIS 4.0+ running VBScript 5.0+,
• MSXML parser 3.0+, and
• Microsoft’s ADO 2.0+
The main object used in the architecture is the XDO object. This object is a stateless component that returns an XML document object model (DOM’s) of data in the database. These XML DOMs are stateful representations of customers, employees, orders, and products. The XML DOM represents the data describing the object.
Implementation:
The sample code provided demonstrates the use of the XDO framework. It uses the Northwind Access database for the data-source but will work for and ADO accessible database. The default.asp page displays a drop-down list of customers by default and allows the user to click buttons to view either the selected customer’s details or their 3 most recent orders.
Execution of the typical ASP page in this framework involves adding the class references to the page through #include directives, validating the request, creating an instance of the business object engine, CustomerXMLEngine, calling the appropriate business object method, transforming the data with appropriate XSL, and returning the resulting HTML interface.
The demo is made up of:
• The default.asp page, that controls which business object method is called,
• The Class.CustomerXMLEngine.asp, that houses the business model’s logic, and
• XSL stylesheets, that are used to transform the returned XML. The default.asp determines which view is displayed based on the arguments submitted.
• XDO, with serves as the
Default.asp
This design allows the same ASP page to render 3 different interfaces (Figure 2). Looking at the code, one can see that the implementation code is reduced to a few lines of code.
The default.asp page interacts with the business object using the “new” statement, returning an instance of the business object.
'/// CREATE AN INSTANCE OF THE CustomerXMLEngine
dim engCustomer : set engCustomer = new CustomerXMLEngine
Data is retrieved from the CustomerXMLEngine object by calling its methods. The example below returns a simple list of customers (ID and CompanyName) and is transformed into a drop-down list using xsl.
'/// GET A STYLE SHEET
dim objXSL
set objXSL = loadStyleSheet("xsl/customerSimpleList.xsl")
'/// CALL THE getSimpleCustomerList AND TRANSFORM TO GET DROPDOWN LIST
dim sCustomerDropDown
sCustomerDropDown = engCustomer.getSimpleCustomerList().transformNode(objXSL)
Class.CustomerXMLEngine.asp
The CustomerXMLEngine contains the rules that define what data to retrieve from the database and applying any rules (like applying a customer discount) at that time. Each method has a SQL statement that determines the resulting XML. On initialization, the business object creates a single instance of the XDO object and sets the connection string.
'INITIALIZE OBJECT
Private Sub Class_Initialize()
m_sObjectTag = "Customer"
set m_oXDO = new CXDO
m_oXDO.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
& Server.MapPath(".") & "\database\NWIND.mdb;"
End Sub
This instance of the XDO object is used in each method to execute their SQL and returns an XML dataset.
Public Function getCustomerList()
dim sSQL : sSQL = "SELECT * FROM Customers"
set getCustomerList = getDOM(sSQL)
End Function
…
'GET XML DOM FOR PROVIDED SQL
Private Function getDOM(SQL)
set getDOM = m_oXDO.getXMLDOM(m_sObjectTag, SQL)
End Function
The XML is returned as a DOM object to facilitate XSL transformation.
XDO
At the heart of this framework lies the XDO object. It is designed to convert Recordsets, including hierarchical (or shaped) Recordsets, into element-based xml. I chose element-based xml to facilitate the creation of XSL templates.
An XDO instance’s lifespan begins with the creation of an ADO Connection. This Connection is available to the XDO object until the terminate method is called. The primary method is getXML, which is exposed as a public method if only the xml string is needed. A secondary public function is getXMLDOM.
This method calls getXML, loads the xml string into an XML DOM object, and returns the DOM instance.
The getXML method, when called, executes the loadData() method. This calls openConnection() and the MSDataShape provider is set if the a shaped SQL statement is provided.
Private Sub openConnection()
dim sConn : sConn = m_sConn
if (Instr(1, UCase(m_sSQL), "SHAPE") = 1) then
if (m_Connection.State = adStateOpen) then m_Connection.Close()
m_Connection.Provider = "MSDataShape"
sConn = Replace(sConn, "Provider", "Data Provider")
end if
if (m_Connection.State <> adStateOpen) then m_Connection.Open(sConn)
End Sub
Next, loadData() executes the query and the Recordset is persisted as XML using the Recordset’s Save() method. At this point, the XML format is in the Microsoft ADO-specific markup. I have written a generic XSL template that transforms shaped and non-shaped Recordsets from the Microsoft markup to element-based markup. This XSL is coded as a string, instead of an external XSL file, to aid XDO’s portability. After the transformation, the resulting XML string is returned.
Though the demo is implemented as VBScript classes, this model is easily translated to COM or other platforms. I have used VBScript classes because some of my clients and my web-host do not support custom COM development.
XDO facilitates the creation of XSL templates by rendering element-based (also called tag-based) XML. Element-based XML, unlike attribute-based XML (like that of ADO), uses a simpler XSL syntax. This makes creation or updating of XSL templates, which render HTML, much easier. Also, the XML document object model (or DOM) still allows searching and sorting of data and requires less overhead than an ADO Recordsets.
Persistence to the database is not mentioned in this model. However, there are a number of ways to address this. One might extend the XDO object to support persistence to the database. Another method might be to create a separate database persistence object to handle this function. Either way this function would also be encapsulated by the business object and not directly called by the ASP page.
This framework separates web applications into different tiers, a data tier, a business rules tier, and the presentation tier. This delegation of duties is clearly delineated with XDO, ADO, and the database making up the data tier, the CustomerXMLEngine in the business rules tier, and XML, XSL and HTML in the presentation tier.
Extending a business object is as simple as adding the new method and altering the default page to process the inputs and render the appropriate interface with XSL. Adding a new Business object, like an EmployeeXMLEngine, is easy to create with the CustomerXMLEngine as a template. The result is a flexible architecture that is easy to expand and maintain.
Further Reading:
• Creating a Datagrid Class in classic ASP - 2-Dimensional Arrays by Brian O'Connell
• A Generic GetRows VBScript Class • Using Classes in ASP (OOP in ASP/VBScript - Part #2) by Marcus Brinkhoff
• Converting Hierarchical Recordsets Into XML By Richard Chisholm
• Increasing ConvertRStoXML() Performance By David O'Neill
• An ASP Class for XML Data Transfer by Seth Juarez
이 문서가 적용되는 제품 보기.
기술 자료 ID : 231282
마지막 검토 : 2005년 11월 17일 목요일
수정 : 9.0
이 문서는 이전에 다음 ID로 출판되었음: KR231282
모든 사용자가 Microsoft Windows Server 2003에서 실행되는 Microsoft Internet Information Services(IIS) 버전 6.0으로 업그레이드하는 것이 좋습니다. IIS 6.0을 사용하면 웹 인프라 보안이 크게 향상됩니다. IIS 보안 관련 항목에 대한 자세한 내용을 보려면 다음 Microsoft 웹 사이트를 방문하십시오.
http://www.microsoft.com/technet/security/prodtech/IIS.mspx (http://www.microsoft.com/technet/security/prodtech/IIS.mspx)(영문)
요약
이 문서에는 웹 응용 프로그램의 로드를 시뮬레이션하는 데 사용할 수 있는 도구 목록이 나와 있습니다. 응용 프로그램의 로드를 시뮬레이션하면 동시성 문제를 테스트할 수 있을 뿐만 아니라 로드 중 응용 프로그램의 작동 상태를 보다 잘 이해할 수 있습니다. 브라우저를 새로 고쳐 로드 및 동시성을 테스트하는 것은 올바른 테스트 방법이 아닙니다.
추가 정보
다음과 같은 Microsoft 웹 스트레스 도구를 사용할 수 있습니다. • 웹 성능 분석 도구(Web Capacity Analysis Tool)
IIS 6.0 Resource Kit 도구에 WCAT 5.2가 포함되어 있습니다. IIS 6.0 Resource Kit 도구를 다운로드하려면 다음 Microsoft 웹 사이트를 방문하십시오.
http://www.microsoft.com/downloads/details.aspx?FamilyID=56fc92ee-a71a-4c73-b628-ade629c89499&DisplayLang=en (http://www.microsoft.com/downloads/details.aspx?FamilyID=56fc92ee-a71a-4c73-b628-ade629c89499&DisplayLang=en)(영문)
• 웹 응용 프로그램 스트레스 도구(Web Application Stress Tool)
웹 응용 프로그램 스트레스 도구(Web Application Stress Tool)를 다운로드하려면 다음 Microsoft 웹 사이트를 방문하십시오.
http://www.microsoft.com/downloads/details.aspx?FamilyID=e2c0585a-062a-439e-a67d-75a89aa36495&DisplayLang=en (http://www.microsoft.com/downloads/details.aspx?FamilyID=e2c0585a-062a-439e-a67d-75a89aa36495&DisplayLang=en)(영문)
이러한 도구를 사용하여 웹 서버의 스트레스를 테스트함으로써 수백명의 사용자가 한꺼번에 응용 프로그램에 액세스할 때 웹 서버의 반응을 확인할 수 있습니다. 이러한 도구는 서버쪽 구성 요소의 성능, 잠금 및 기타 확장성 문제를 테스트하는 데도 사용할 수 있습니다. 데이터베이스에 의존하는 웹 응용 프로그램의 경우에는 동시성, 트랜잭션, 사용자 수, 잠금, 풀링 등의 매개 변수를 적용하여 테스트할 수 있습니다.
Microsoft 제품 관련 기술 전문가들과 온라인으로 정보를 교환하시려면 Microsoft 뉴스 그룹 (http://support.microsoft.com/newsgroups/default.aspx)에 참여하시기 바랍니다.
WAS(웹 응용 프로그램 스트레스) 도구 설치 및 사용 방법 이 문서가 적용되는 제품 보기.
기술 자료 ID : 313559
마지막 검토 : 2004년 12월 15일 수요일
수정 : 2.2
이 문서는 이전에 다음 ID로 출판되었음: KR313559
이 페이지에서
요약
웹 응용 프로그램 스트레스 도구를 설치하는 방법
테스트 스크립트를 만드는 방법
스크립트를 수동으로 만드는 방법
브라우저 활동을 기록하여 스크립트를 만드는 방법
IIS 로그에서 스크립트를 만드는 방법
웹 사이트 콘텐츠에서 스크립트를 만드는 방법
스크립트를 구성하는 방법
웹 프로그램을 테스트하는 방법
테스트 보고서를 보는 방법
문제 해결
참조
요약
이 문서에서는 Microsoft WAS(Web Application Stress) 도구를 설치하는 방법과 WAS를 사용하여 웹 프로그램을 테스트하기 위한 스크립트를 만들고 실행하는 방법을 단계별로 설명합니다.
WAS 도구는 Microsoft Internet Information Services(IIS) 5.0 웹 서버를 시뮬레이트된 로드에 두는 데 사용할 수 있는 시뮬레이션 도구입니다. 이 도구는 여러 브라우저가 웹 사이트에서 페이지를 요청하는 환경을 현실적으로 재현하도록 설계되었습니다. WAS를 사용하여 웹 프로그램에 대한 성능 데이터를 수집하고 웹 서버의 성능과 안정성을 평가하십시오. WAS는 스크립트를 사용하여 상대적으로 적은 수의 클라이언트를 사용함으로써 많은 수의 요청을 시뮬레이트합니다. 따라서 가능한 생산 환경에 가까운 시나리오를 만듭니다. 웹 프로그램이 스트레스 하에서 어떻게 응답하며 배포하기 전에 프로그램의 문제를 어떻게 식별하고 제거하는지 이해하기 위해 수집하는 데이터를 분석할 수 있습니다.
위로 가기
웹 응용 프로그램 스트레스 도구를 설치하는 방법
참고: 클라이언트 컴퓨터에서 다음 절차를 수행해야 합니다. WAS는 Microsoft Windows NT 4.0 서비스 팩 4(SP 4) 이상과 Microsoft Windows 2000에서 지원됩니다. 테스트 중인 웹 서버에는 WAS를 설치하지 마십시오. 설치할 경우 WAS 설치가 웹 서버 성능에 영향을 미칠 수 있으며 테스트 결과에 방해가 될 수 있습니다. 1. 관리자 또는 Administrators 그룹의 구성원으로 컴퓨터에 로그온합니다.
2. WAS를 다운로드합니다. 브라우저를 시작하고 다음 Microsoft 웹 사이트로 이동하여 WAS를 다운로드할 수 있습니다.
http://www.microsoft.com/downloads/details.aspx?FamilyID=e2c0585a-062a-439e-a67d-75a89aa36495&DisplayLang=en (http://www.microsoft.com/downloads/details.aspx?FamilyID=e2c0585a-062a-439e-a67d-75a89aa36495&DisplayLang=en)
Microsoft 지원 파일을 다운로드하는 방법은 Microsoft 기술 자료의 다음 문서를 참조하십시오.
119591 (http://support.microsoft.com/kb/119591/) 온라인 서비스로부터 Microsoft 지원 파일을 구하는 방법
3. 웹 페이지의 오른쪽 창에서 Download를 누릅니다.
4. 저장을 누릅니다.
5. Setup.exe 파일을 저장할 위치를 지정한 다음 저장을 누릅니다.
6. 테스트를 수행할 각 클라이언트 컴퓨터에 Setup.exe 파일을 복사합니다.
7. 각 클라이언트 컴퓨터에서 Microsoft Windows 탐색기를 시작한 다음 5단계에서 저장한 Setup.exe 파일을 두 번 누릅니다.
8. Yes를 눌러 사용 계약에 동의하여 기본 설치 위치를 적용하거나 WAS를 설치할 위치를 지정한 후 Next를 누릅니다.
9. OK를 누른 다음 Finish를 누릅니다.
위로 가기
테스트 스크립트를 만드는 방법
웹 프로그램을 테스트할 스크립트를 만들려면 다음 방법 중 하나를 사용하십시오.
스크립트를 수동으로 만드는 방법
스크립트를 수동으로 만들려면 다음과 같이 하십시오. 1. 시작을 누르고 프로그램, Microsoft Web Application Stress Tool을 차례로 가리킨 다음 Microsoft Web Application Stress Tool을 누릅니다.
2. Scripts 메뉴에서 Create를 가리킨 다음 Manual을 누릅니다.
스크립트가 왼쪽 창에 "New Script"로 표시됩니다. 새 스크립트 이름을 변경하려면 New Script를 누른 다음 스크립트의 새 이름을 입력합니다.
3. 오른쪽 창의 Server 상자에 웹 서버의 이름, FQDN(정식 도메인 이름) 또는 IP(인터넷 프로토콜) 주소를 입력합니다.
4. Notes 상자에 설명을 입력합니다.
5. Verb 아래에서 첫 번째 셀의 아래 화살표를 누른 다음 사용할 HTTP(Hypertext Transfer Protocol) 동사(예: GET)를 누르거나 셀에 사용할 동사를 입력합니다.
6. Path 아래에 웹 페이지의 이름과 경로(예: /scripts/test.asp)를 입력합니다.
참고: 서버 이름은 포함하지 마십시오.
7. 페이지 그룹을 사용하려는 경우 Group 아래를 적절히 변경합니다.
8. 스크립트 항목 사이에 대기 시간을 지정하려면 Delay 아래에 지연 값(밀리초 단위)을 입력합니다. 기본값은 0입니다.
9. 스크립트에 항목을 추가하려면 5단계부터 8단계까지 반복합니다.
브라우저 활동을 기록하여 스크립트를 만드는 방법
브라우저 활동을 기록하여 스크립트를 만들려면 이 절에서 설명하는 절차를 사용하십시오.
참고: 프록시 서버를 사용 중인 경우 사용자 계정에 먼저 로그온하도록 Microsoft WebTool 서비스를 구성해야 합니다. 프록시 서버를 사용하지 않는 경우 본 문서의 2단계: 브라우저 활동 기록 절로 바로 이동하십시오.
Microsoft WebTool 서비스를 구성하는 방법
프록시 서버를 사용 중인 경우 사용자 계정에 로그온하도록 Microsoft WebTool 서비스를 구성하십시오. 구성하려면 다음과 같이 하십시오. 1. 시작을 누르고 설정을 가리킨 다음 제어판을 누릅니다.
2. 관리 도구를 두 번 누른 다음 서비스를 두 번 누릅니다.
3. WebTool을 두 번 누른 다음 로그온 탭을 누릅니다.
4. 다음 계정으로 로그온에서 계정 지정을 누른 다음 아래의 형식을 사용하여 사용자 이름을 입력합니다.
\\Domain\UserName
5. 해당 상자에 암호를 입력하고 확인한 다음 확인을 누릅니다.
6. WebTool을 마우스 오른쪽 단추로 누른 다음 중지를 누릅니다.
7. WebTool을 마우스 오른쪽 단추로 누른 다음 시작을 누릅니다.
8. 서비스 대화 상자를 닫은 다음 관리 도구 대화 상자를 닫습니다.
브라우저 활동을 기록하는 방법
1. Microsoft Internet Explorer를 시작합니다.
2. 도구 메뉴에서 인터넷 옵션을 누른 다음 일반 탭을 누릅니다.
3. 임시 인터넷 파일에서 파일 삭제를 누릅니다.
4. 연결 탭을 누릅니다.
5. 전화 걸기 설정 상자에서 전화 접속 네트워킹 연결을 누른 다음 설정을 누릅니다.
6. 자동 구성에서 설정 자동 검색 확인란이 선택되어 있으면 선택을 취소합니다.
7. 프록시 서버에서 프록시 서버 사용 확인란을 선택하고 주소 상자에 localhost를 입력한 다음 포트 상자에 8000을 입력합니다.
8. 로컬 주소에 프록시 서버 사용 안함 확인란이 선택되어 있으면 선택을 취소합니다.
9. 확인을 두 번 누른 다음 Internet Explorer를 종료합니다.
10. 시작을 누르고 프로그램, Microsoft Web Application Stress Tool을 차례로 가리킨 다음 Microsoft Web Application Stress Tool을 누릅니다.
11. Scripts 메뉴에서 Create를 가리킨 다음 Record를 누릅니다.
12. 기록할 설정 옆의 확인란을 선택하고 Next를 누른 다음 Finish를 누릅니다.
13. Internet Explorer를 시작한 후에 주소 표시줄에 테스트할 웹 사이트의 URL(Uniform Resource Locator)을 입력한 다음 Enter 키를 누릅니다.
14. 테스트할 웹 사이트의 페이지를 탐색합니다.
탐색하는 페이지의 경로가 WAS 기록 창에 표시됩니다.
15. 작업을 마쳤으면 Stop Recording을 누릅니다.
스크립트가 왼쪽 창에 "New Recorded Script"로 표시됩니다. 새 스크립트 이름을 변경하려면 New Recorded Script를 누른 다음 스크립트의 새 이름을 입력합니다.
IIS 로그에서 스크립트를 만드는 방법
IIS 로그에서 스크립트를 만들려면 다음과 같이 하십시오. 1. 시작을 누르고 프로그램, Microsoft Web Application Stress Tool을 차례로 가리킨 다음 Microsoft Web Application Stress Tool을 누릅니다.
2. Scripts 메뉴에서 Create를 가리킨 다음 Log를 누릅니다.
3. Log file 상자에서 Browse를 눌러 스크립트를 만드는 데 사용할 IIS 로그를 찾고 Open을 누른 다음 Next를 누릅니다.
4. 로그 파일을 구문 분석할 옵션을 누른 다음 Finish를 누릅니다.
스크립트는 왼쪽 창에 "LogFile.log"로 표시됩니다. 여기서 LogFile.log는 IIS 로그 파일의 이름입니다. 새 스크립트 이름을 변경하려면 LogFile.log를 누른 다음 스크립트의 새 이름을 입력합니다.
웹 사이트 콘텐츠에서 스크립트를 만드는 방법
웹 사이트에 있는 파일에서 스크립트를 만들려면 다음 단계를 수행하십시오. 1. 시작을 누르고 프로그램, Microsoft Web Application Stress Tool을 차례로 가리킨 다음 Microsoft Web Application Stress Tool을 누릅니다.
2. Scripts 메뉴에서 Create를 가리킨 다음 Contents를 누릅니다.
스크립트가 왼쪽 창에 "New Script"로 표시됩니다. 새 스크립트 이름을 변경하려면 New Script를 누른 다음 스크립트의 새 이름을 입력합니다.
3. 트리가 아직 확장되지 않은 경우 스크립트 옆의 더하기 기호(+)를 눌러 트리를 확장합니다.
스크립트 항목이 스크립트 트리에 표시됩니다.
4. Content Tree를 누릅니다.
5. 오른쪽 창의 Content location 상자에 콘텐츠 폴더 경로를 입력하거나 Browse를 눌러 폴더를 찾은 다음 OK를 누릅니다.
6. 필요할 경우 Virtual root 상자에 가상 루트 자리 표시자를 입력합니다.
7. Apply를 누릅니다.
WAS는 웹 콘텐츠에 기반하여 콘텐츠 트리를 만듭니다. 테스트에 포함할 파일 옆의 확인란을 선택합니다.
위로 가기
스크립트를 구성하는 방법
스크립트 설정을 구성하려면 다음과 같이 하십시오. 1. 시작을 누르고 프로그램, Microsoft Web Application Stress Tool을 차례로 가리킨 다음 Microsoft Web Application Stress Tool을 누릅니다.
2. 트리가 아직 확장되지 않은 경우 왼쪽 창에서 ScriptName( ScriptName은 스크립트의 이름)을 두 번 눌러 트리를 확장합니다.
스크립트 항목이 오른쪽 창에 표시됩니다.
3. 구성할 스크립트 항목(Verb 열 왼쪽에 있는 검정색 단추)의 행 머리글을 두 번 누릅니다.
이렇게 하면 스크립트 Details 보기가 열립니다. 이 보기를 사용하여 쿼리 문자열 이름-값 쌍을 편집하거나 게시 데이터를 사용자 지정하거나 HTTP 헤더를 수정하거나 SSL(Secure Sockets Layer) 암호화를 설정하거나 스크립트를 RDS(Remote Data Service) 쿼리로 서식 지정할 수 있습니다.
4. 적절한 탭을 눌러 원하는 설정을 지정한 다음 OK를 누릅니다.
5. 왼쪽 창에서 Settings를 누릅니다.
Settings 대화 상자에 표시된 스크립트 옵션에 대한 설정을 지정합니다. 예를 들어, 스트레스 수준 값을 수정하거나 테스트 실행 시간을 설정하거나 대역폭 조절을 설정할 수 있습니다.
6. 스크립트에 성능 모니터 카운터를 추가하려면 Perf Counters를 누르고 오른쪽 창에서 Add Counter를 누르고 추가할 개체와 카운터를 누르고 Add를 누른 다음 OK를 누릅니다.
7. 해당 스크립트에 정의된 페이지 그룹 목록을 보거나 페이지 그룹 분배를 변경하려면 Page Groups를 누릅니다.
8. 기본 사용자에 사용자를 추가하고 제거하거나 새로운 사용자를 만들려면 Users를 누르고 오른쪽 창에서 Default를 두 번 누르고 다음 단계 중 하나를 수행하십시오. • 새 사용자를 추가하려면 해당 상자에 다음 정보를 입력한 다음 Create를 누릅니다. • 만들려는 사용자 수
• 사용자 이름 접두사
• 암호
• 새 사용자를 추가하려면 왼쪽 창에서 Default를 마우스 오른쪽 단추로 누른 다음 Add를 누릅니다.
새 사용자가 왼쪽 창에 "New Population"으로 표시됩니다. 새 사용자 이름을 변경하려면 New Population을 누른 다음 새 이름을 입력합니다.
9. View 메뉴에서 Scripts를 눌러 Scripts 보기로 돌아갑니다.
10. 현재 그룹에 클라이언트 컴퓨터를 추가 또는 제거하거나 클라이언트 컴퓨터의 새 그룹을 추가하려면 Clients를 누른 다음 오른쪽 창에서 Default를 누릅니다. • 클라이언트 컴퓨터를 추가하려면 Machine name 상자에 컴퓨터 이름(또는 IP 주소)을 입력한 다음 Add를 누릅니다.
• 새 그룹을 추가하려면 왼쪽 창에서 Default를 마우스 오른쪽 단추로 누른 다음 Add를 누릅니다. 새 그룹이 왼쪽 창에 "New Group"으로 표시됩니다. 새 그룹 이름을 변경하려면 New Group을 누른 다음 새 이름을 입력합니다.
11. View 메뉴에서 Scripts를 눌러 Scripts 보기로 돌아갑니다.
12. 각 사용자와 함께 저장된 쿠키를 보려면 Cookies를 누릅니다.
위로 가기
웹 프로그램을 테스트하는 방법
스크립트를 사용하여 테스트를 실행하려면 다음과 같이 하십시오. 1. 시작을 누르고 프로그램, Microsoft Web Application Stress Tool을 차례로 가리킨 다음 Microsoft Web Application Stress Tool을 누릅니다.
2. 왼쪽 창에서 사용할 스크립트를 누른 다음 Scripts 메뉴에서 Run을 누릅니다.
위로 가기
테스트 보고서를 보는 방법
테스트 보고서를 보려면 다음과 같이 하십시오. 1. 시작을 누르고 프로그램, Microsoft Web Application Stress Tool을 차례로 가리킨 다음 Microsoft Web Application Stress Tool을 누릅니다.
2. View 메뉴에서 Reports를 누릅니다.
수행하는 각 테스트의 보고서는 왼쪽 창의 관련 스크립트 아래 표시됩니다. 보고서 제목은 테스트를 시작한 날짜와 시간입니다.
3. 트리를 아직 확장하지 않았으면 보고서를 두 번 눌러 트리를 확장합니다.
4. 보고서 트리에서 추가 정보를 보려는 항목(예: Page Summary)을 누릅니다.
해당 항목에 대한 세부 정보가 오른쪽 창에 표시됩니다.
위로 가기
문제 해결
• WAS를 시작할 수 없습니다.
이 문제는 WebTool 서비스가 실행되고 있지 않을 경우 발생할 수 있습니다. 이 문제를 해결하려면 WebTool 서비스가 실행 중인지 확인하십시오. 확인하려면 다음과 같이 하십시오. 1. 시작을 누르고 프로그램, 보조프로그램을 차례로 가리킨 다음 명령 프롬프트를 누릅니다.
2. 명령 프롬프트에서 net start webtool을 입력한 다음 Enter 키를 누릅니다.
3. 현재 실행 중인 서비스 목록을 표시하려면 net start를 입력한 다음 Enter 키를 누릅니다.
목록에 WebTool이 표시되는지 확인합니다.
• 클라이언트 컴퓨터를 추가하거나 클라이언트 컴퓨터에 연결할 수 없습니다.
다음 경우 중 하나에 해당하면 이러한 현상이 발생할 수 있습니다. • 클라이언트 컴퓨터가 Windows NT 4.0 기반 또는 Windows 2000 기반 컴퓨터가 아닙니다. 이 문제를 해결하려면 Windows NT 기반 또는 Windows 2000 기반 컴퓨터에 WAS를 설치하십시오.
또는
• WAS가 클라이언트 컴퓨터에 설치되지 않았습니다. 이 문제를 해결하려면 연결할 클라이언트 컴퓨터에 WAS를 설치하십시오.
또는
• WAS가 설치된 클라이언트 컴퓨터에서 WebTool 서비스가 실행되고 있지 않습니다. 이 문제를 해결하려면 연결할 클라이언트 컴퓨터에서 WebTool 서비스를 시작하십시오.
또는
• 연결할 클라이언트 컴퓨터에서 로컬 관리자 그룹의 구성원이 아닙니다. 이 문제를 해결하려면 연결할 각 클라이언트 컴퓨터에서 로컬 관리자 그룹의 구성원인 사용자 계정을 사용하여 로그온하십시오.
WAS 문제 해결 방법에 대한 자세한 내용은 WAS 도움말의 "Troubleshooting" 절을 참조하십시오. Help 메뉴에서 Web Application Stress Help를 누르고 Contents 탭을 누른 다음 Troubleshooting을 두 번 누릅니다.
위로 가기
참조
WAS 도구를 사용하는 방법에 대한 자세한 내용은 Help 메뉴에서 Web Application Stress Help를 눌러 WAS 도움말을 보십시오.
WAS 도구에 대한 자세한 내용을 보려면 다음 Microsoft 웹 사이트를 방문하십시오.
Microsoft 웹 응용 프로그램 스트레스 도구 (http://webtool.rte.microsoft.com/)
웹 서버 테스트에 사용할 수 있는 WAS 및 기타 도구에 대한 자세한 내용은 Microsoft 기술 자료의 다음 문서를 참조하십시오.
231282 (http://support.microsoft.com/kb/231282/) 웹 서버를 테스트하는 스트레스 도구
IIS 설치 및 사용을 위한 리소스에 대한 자세한 내용은 Microsoft 기술 자료의 다음 문서를 참조하십시오.
266115 (http://support.microsoft.com/kb/266115/) IIS 5.0 설치 및 사용을 위한 리소스
------------------------------------------------------------
--EX)Employee 테이블 조회
------------------------------------------------------------
SELECT EmployeeID, EmployeeName
FROM Employee
------------------------------------------------------------
--EX)WHERE절 사용
------------------------------------------------------------
SELECT *
FROM Employee
WHERE EmployeeUD = 'ahsoo'
------------------------------------------------------------
--EX)AND 조건의 사용
------------------------------------------------------------
SELECT *
FROM Employee
WHERE BirthYMD <= '19800101'
AND JoinYMD >= '20050601'
------------------------------------------------------------
--EX)OR 조건의 사용(UNION/UNION ALL로도 커버 가능)
------------------------------------------------------------
SELECT *
FROM Employee
WHERE BirthYMD <= '19800101'
OR JoinYMD >= '20050601'
------------------------------------------------------------
--EX)1월1일이나 7월1일에 입사한 직원 찾아내기(UNION뿐 아니라 IN으로도 커버)
------------------------------------------------------------
SELECT *
FROM Employee
WHERE JoinYMD = '20050701'
OR JoinYMD = '20050101'
------------------------------------------------------------
--EX)1월1일이나 7월1일에 입사했고 EmployeeType이 0001인 경우
------------------------------------------------------------
SELECT *
FROM Employee
WHERE JoinYMD = '20050701'
OR JoinYMD = '20050101'
AND EmployeeType = '0001'
-- 총12건의 데이터가 나오고 EmployeeType이 0002인 데이터도 존재
-- 1월1일 입사했고, EmployeeType이 0001이거나 입사일이 7월1일인 데이터가 조회됨
------------------------------------------------------------
--EX)괄호를 사용 – 정확한 데이터가 조회됨
------------------------------------------------------------
SELECT *
FROM Employee
WHERE (JoinYMD = '20050701'
OR JoinYMD = '20050101')
AND EmployeeType = '0001'
------------------------------------------------------------
--EX)IN의 사용
------------------------------------------------------------
SELECT *
FROM Employee
WHERE JoinYMD IN ('20050701', '20050101')
AND EmployeeType = '0001'
------------------------------------------------------------
--EX)LIKE의 사용
------------------------------------------------------------
SELECT *
FROM Employee
WHERE EmployeeID LIKE 'a%'
go
SELECT *
FROM Employee
WHERE EmployeeID LIKE 'h%77'
go
SELECT *
FROM Employee
WHERE EmployeeID LIKE '___[0-9][0-9]'
go
SELECT *
FROM Employee
WHERE EmployeeID LIKE '[as]%'
go
SELECT *
FROM Employee
WHERE EmployeeID LIKE '[^as]%'
go
------------------------------------------------------------
--EX)NULL값에 대한 조회
------------------------------------------------------------
SELECT *
FROM Employee
WHERE RetireYMD = NULL
go
SELECT *
FROM Employee
WHERE RetireYMD IS NULL
go
SELECT *
FROM Employee
WHERE RetireYMD IS NOT NULL
go
------------------------------------------------------------
--EX)BETWEEN의 사용
------------------------------------------------------------
SELECT *
FROM Employee
WHERE JoinYMD BETWEEN '20050601' AND '20050701'
-- BETWEEN연산자는 >=와 <=를 섞어 쓴것과 동일
------------------------------------------------------------
--EX)별칭의 여러가지 유형
------------------------------------------------------------
SELECT T1.EmployeeID as EmpID,
T1.EmployeeType EmpType,
EmpName = T1.EmployeeNAme
FROM Employee T1
------------------------------------------------------------
--EX)별칭 사용시 SQL의 작성이 보다 쉬워진다.
------------------------------------------------------------
SELECT SalesmanSeq, Amount
FROM Sales
INNER JOIN ShopSalesman
ON SalesmanSeq = SalesmanSeq
WHERE SaleYMD = '20060201'
go
--'열 이름 'SalesmanSeq'이(가) 불확실합니다.'와 같은 에러 발생
--중복되는 컬럼명에 테이블명을 명시해준다.
SELECT ShopSalesman.SalesmanSeq, Sales.Amount
FROM Sales
INNER JOIN ShopSalesman
ON Sales.SalesmanSeq = ShopSalesman.SalesmanSeq
WHERE Sales.SaleYMD = '20060201'
go
-- 작성이 번거로움, 별칭사용
SELECT T2.SalesmanSeq, T1.Amount
FROM Sales T1
INNER JOIN ShopSalesman T2
ON T1.SalesmanSeq = T2.SalesmanSeq
WHERE T1.SaleYMD = '20060201'
go
------------------------------------------------------------
--EX)SELECT문을 사용한 INSERT문(Test직원을 이용해 Test1직원을 만든다.)
------------------------------------------------------------
INSERT INTO Employee
(EmployeeID, EmployeeName, EmployeeType,
BirthYMD, JoinYMD, RetireYMD,
PhoneNo, EmailAddress, MobileNo,
Address, EmployeeState)
SELECT 'Test1', '테스트1', EmployeeType,
BirthYMD, JoinYMD, RetireYMD,
PhoneNo, EmailAddress, MobileNo,
Address, EmployeeState
FROM Employee
WHERE EmployeeID = 'Test'
------------------------------------------------------------
--EX)UPATE문의 사용
------------------------------------------------------------
UPDATE Employee
SET JoinYMD = '20060101'
WHERE EmployeeID = 'Test'
------------------------------------------------------------
--EX)여러 개의 컬럼을 한 번에 UPDATE할 수 있다.
------------------------------------------------------------
UPDATE Employee
SET JoinYMD = '20060201',
RetireYMD = '20061231'
WHERE EmployeeID = 'Test'
------------------------------------------------------------
--EX)UPDATE문장에 별칭과 FROM절 사용
------------------------------------------------------------
UPDATE T1
SET T1.JoinYMD = '20061231',
T1.RetireYMD = '20060201'
FROM Employee T1
WHERE T1.EmployeeID = 'Test1'
------------------------------------------------------------
--EX)두 컬럼간의 값을 변경하기(이전의 값들을 참조하므로 가능)
------------------------------------------------------------
UPDATE T1
SET T1.RetireYMD = T1.JoinYMD,
T1.JoinYMD = T1.RetireYMD
FROM Employee T1
WHERE T1.EmployeeID = 'Test1'
------------------------------------------------------------
--EX)DELETE문의 사용
------------------------------------------------------------
DELETE Employee
WHERE EmployeeID = 'Test1'
------------------------------------------------------------
--EX)DELETE문장에 별칭과 FROM절 사용
------------------------------------------------------------
DELETE T1
FROM Employee T1
WHERE EmployeeID = 'Test'
------------------------------------------------------------
--EX)기본적인 ORDER BY
------------------------------------------------------------
SELECT *
FROM Sales
WHERE SaleYMD LIKE '200501%'
ORDER BY SaleYMD DESC
------------------------------------------------------------
--EX)DESC, ASC지정한 ORDER BY
------------------------------------------------------------
SELECT *
FROM Sales
WHERE SaleYMD LIKE '200501%'
ORDER BY ModelID ASC, SaleYMD DESC, SalesTime DESC
------------------------------------------------------------
--EX)고정형 데이터와 가변형 데이터의 차이
------------------------------------------------------------
SELECT '1' + cast('234' as varchar(10)) + '5'
SELECT '1' + cast('234' as char(10)) + '5'
INSERT INTO #TBL(float_val, numeric_val)
VALUES (12.13142132, 12.13142132)
INSERT INTO #TBL(float_val, numeric_val)
VALUES (22.13142136, 22.13142136)
SELECT *
FROM #TBL
--데이터를 조회하면, 원래 입력한 데이터와 틀리다는 것을 알 수 있다.
--근사 숫자로 자료형을 만들 경우 실제 원하는 결과와 틀린 결과가 나올 수 있다는 것을 유념해 두자.
------------------------------------------------------------
--EX)특정 일자의 데이터를 조회하는 방법
------------------------------------------------------------
--한건의 데이터도 조회되지 않는다.
SELECT *
FROM Sales
WHERE RegiDT = '2005-03-01'
--3월 2일 데이터가 포함될 수 있다.
SELECT *
FROM Sales
WHERE RegiDT BETWEEN '2005-03-01 00:00:00' AND '2005-03-02 00:00:00'
--제대로 된 구간 조회
SELECT *
FROM Sales
WHERE RegiDT >= '2005-03-01 00:00:00'
AND RegiDT < '2005-03-02 00:00:00'
------------------------------------------------------------
--EX)날짜 및 시간 함수
------------------------------------------------------------
--GETDATE() : 현재 날짜 가져오기
SELECT GETDATE()
go
--DATEADD() : 현재 날짜에서 한달을 더하고 빼기
SELECT DATEADD(m, 1, GETDATE()), DATEADD(m, -1, GETDATE())
go
--DATEDIFF() : 날짜간의 차이
SELECT DATEDIFF(yy, '20060401', '20060520') [년도차이],
DATEDIFF(mm, '20060401', '20060520') [월차이],
DATEDIFF(dd, '20060401', '20060520') [일수차이]
go
--DATEPART() : 해당일자의 특정 부분 값 알아내기
SELECT DATEPART(yy, GETDATE()) [년],
DATEPART(mm, GETDATE()) [월],
DATEPART(dd, GETDATE()) [일자],
DATEPART(dw, GETDATE()) [요일], --1은 일요일, 7은 토요일
DATEPART(hh, GETDATE()) [시],
DATEPART(mm, '2006-05-21 09:10:15') [분],
DATEPART(ss, '2006-05-21 09:10:15') [초]
go
------------------------------------------------------------
--EX)DISTINCT 사용
------------------------------------------------------------
--판매일자 중복제거하기
SELECT DISTINCT T1.SaleYMD
FROM Sales T1
WHERE SaleYMD BETWEEN '20060501' AND '20060531'
ORDER BY T1.SaleYMD
go
--판매된 모델 중복 제거하기
SELECT DISTINCT T1.ModelID
FROM Sales T1
WHERE SaleYMD BETWEEN '20060501' AND '20060531'
ORDER BY T1.ModelID
go
--판매일자 모델별 중복 제거하기
SELECT DISTINCT T1.SaleYMD, T1.ModelID
FROM Sales T1
WHERE SaleYMD BETWEEN '20060501' AND '20060531'
ORDER BY T1.SaleYMD, T1.ModelID
go
------------------------------------------------------------
--EX)GROUP BY 기본 구문
------------------------------------------------------------
--직원타입별로 직원 카운트 하기
SELECT T1.EmployeeType, COUNT(*)
FROM Employee T1
GROUP BY T1.EmployeeType
--직원 타입, 직원의 입사년도 별로 카운트 하기
SELECT T1.EmployeeType,
LEFT(T1.JoinYMD, 4) JoinYY,
COUNT(*)
FROM Employee T1
GROUP BY T1.EmployeeType, LEFT(T1.JoinYMD, 4)
ORDER BY T1.EmployeeType, LEFT(T1.JoinYMD, 4)
------------------------------------------------------------
--EX)GROUP BY와 집계함수의 사용
------------------------------------------------------------
--일자별, 모델별 판매 카운트 구하기
SELECT T1.SaleYMD, T1.ModelID, COUNT(T1.Qty)
FROM Sales T1
WHERE T1.SaleYMD BETWEEN '20060501' AND '20060531'
GROUP BY T1.SaleYMD, T1.ModelID
ORDER BY T1.SaleYMD, T1.ModelID
go
--일별 가장 높은 금액, 가장 낮은 금액 구하기
SELECT T1.SaleYMD, MAX(T1.Amount), MIN(T1.Amount)
FROM Sales T1
WHERE T1.SaleYMD BETWEEN '20060501' AND '20060531'
GROUP BY T1.SaleYMD
ORDER BY T1.SaleYMD
go
--일자별로 판매 수량 구하기
SELECT T1.SaleYMD, SUM(T1.Qty)
FROM Sales T1
WHERE T1.SaleYMD BETWEEN '20060501' AND '20060531'
GROUP BY T1.SaleYMD
ORDER BY T1.SaleYMD
go
--모델별로 판매수량 구하기
SELECT T1.ModelID, SUM(T1.Qty)
FROM Sales T1
WHERE T1.SaleYMD BETWEEN '20060501' AND '20060531'
GROUP BY T1.ModelID
ORDER BY T1.ModelID
go
--일별 평균 판매 금액 구하기
SELECT T1.SaleYMD, CONVERT(numeric(18,2), AVG(T1.Amount))
FROM Sales T1
WHERE T1.SaleYMD BETWEEN '20060501' AND '20060531'
GROUP BY T1.SaleYMD
ORDER BY T1.SaleYMD
------------------------------------------------------------
--EX)GROUP BY되는 컬럼의 변경
------------------------------------------------------------
--요일별 판매 수량 구하기
SELECT DATEPART(dw, T1.SaleYMD), DATENAME(dw, T1.SaleYMD), SUM(T1.Qty)
FROM Sales T1
WHERE T1.SaleYMD BETWEEN '20060501' AND '20060531'
GROUP BY DATEPART(dw, T1.SaleYMD), DATENAME(dw, T1.SaleYMD)
ORDER BY DATEPART(dw, T1.SaleYMD)
------------------------------------------------------------
--EX)HAVING 사용하기
------------------------------------------------------------
--특정 기간내 판매금액이 1억보다 작은 모델
SELECT T1.ModelID, SUM(T1.Amount)
FROM Sales T1
WHERE T1.SaleYMD BETWEEN '20060501' AND '20060531'
GROUP BY T1.ModelID
HAVING SUM(T1.Amount) <= 100000000
go
--특정 기간내 판매금액이 1억보다 큰 모델
SELECT T1.ModelID, SUM(T1.Amount)
FROM Sales T1
WHERE T1.SaleYMD BETWEEN '20060501' AND '20060531'
GROUP BY T1.ModelID
HAVING SUM(T1.Amount) >= 100000000
go
--특정 기간내 일별로 집계된 판매금애에서 2006년 5월 3일 데이터만 찾아내기
SELECT T1.SaleYMD, SUM(T1.Amount)
FROM Sales T1
WHERE T1.SaleYMD BETWEEN '20060501' AND '20060531'
GROUP BY T1.SaleYMD
HAVING T1.SaleYMD = '20060531'
go
--WHERE절로 조건을 옮기는 것과 동일
SELECT T1.SaleYMD, SUM(T1.Amount)
FROM Sales T1
WHERE T1.SaleYMD = '20060531'
GROUP BY T1.SaleYMD
EX)HAVING절을 사용해서 중복된 데이터 찾아내기
--한 상점에 두 명 이상의 판매원이 등록된 경우
SELECT T1.ShopID, COUNT(*)
FROM ShopSalesman T1
WHERE T1.SalesEndYMD = '99991231'
GROUP BY T1.ShopID
HAVING COUNT(*)>=2
------------------------------------------------------------
--응용EX)GROUP BY 1.판매 테이블에 대해서 각 년월별로 판매금액을 집계
------------------------------------------------------------
년 + 해당 년도의 총 판매 금액
SELECT LEFT(T1.SaleYMD, 4) SaleYY,
SUM(T1.Amount) TTL_AMT
FROM Sales T1
GROUP BY LEFT(T1.SaleYMD, 4)
YYYYMMDD날짜 형식에서 년도를 가져오는 다른 방법
SELECT DATEPART(yy, T1.SaleYMD) SaleYY,
SUM(T1.Amount) TTL_AMT
FROM Sales T1
GROUP BY DATEPART(yy, T1.SaleYMD)
------------------------------------------------------------
--응용EX)GROUP BY 2.2006년 5월 판매 데이터에 대해서 각 모델별 판매 수량을 집계
------------------------------------------------------------
모델ID + 해당모델의 총 판매 수량
SELECT T1.ModelID, SUM(T1.Qty) TTL_Qty
FROM Sales T1
WHERE T1.SaleYMD LIKE '200605%'
GROUP BY T1.ModelID
ORDER BY T1.ModelID
------------------------------------------------------------
--응용EX)GROUP BY 3.2006년 판매 데이터에 대해서 각 월별, 판매수량과 판매 금액
------------------------------------------------------------
년월 + 모델ID + 해당월 모델의 판매 수량 + 해당월 모델의 판매 금액
SELECT LEFT(T1.SaleYMD, 6) SaleYM, T1.ModelID,
SUM(T1.Qty) TTL_Qty, SUM(T1.Amount) TTL_Amt
FROM Sales T1
WHERE T1.SaleYMD LIKE '2006%'
GROUP BY LEFT(T1.SaleYMD, 6), T1.ModelID
ORDER BY LEFT(T1.SaleYMD, 6), T1.ModelID
------------------------------------------------------------
--응용EX)GROUP BY 4.2006년 5월 판매 데이터중 판매 수량이 200 이상인 데이터만 추출
------------------------------------------------------------
모델ID + 판매수량(200이상인 경우만)
SELECT T1.ModelID, SUM(T1.Qty) TTL_Qty
FROM Sales T1
WHERE T1.SaleYMD LIKE '200605%'
GROUP BY ModelID
HAVING SUM(T1.Qty) >= 200
ORDER BY SUM(T1.Qty) ASC --ORDER BY에 집계된 내용이 올 수 있다.
------------------------------------------------------------
--응용EX)GROUP BY 5.2006년 4월에서 각 요일별 시간대별 판매 수량 구하기
------------------------------------------------------------
요일 + 시 + 판매 수량
SELECT DATENAME(dw, T1.SaleYMD) SaleDW,
LEFT(SalesTime, 2) SalesTime,
SUM(T1.Qty)
FROM Sales T1
WHERE T1.SaleYMD LIKE '200604%'
GROUP BY DATEPART(dw, T1.SaleYMD), DATENAME(dw, T1.SaleYMD), LEFT(SalesTime, 2)
ORDER BY DATEPART(dw, T1.SaleYMD), LEFT(SalesTime, 2)
-- DATENAME과 DATEPART에 dw를 사용하면 일자(YYYYMMDD)에 대해 요일을 찾을 수 있게 된다.
-- GROUP BY 에서 DATEPART를 사용한 이유는 DATEPART는 요일에 대해 1부터 7로 숫자로 표현해주고,
-- DATENAME은 요일에 대해 월~일같은 문자로 표기해 준다. 이때 정렬을 문자가 아닌 숫자로 하는 것이
-- 우리가 일반적으로 알고 있는 요일대로 정렬이 되기 때문에 실제 SELECT절에서는 사용하지 않지만
-- GROUP BY에 DATEPART를 한번 다 사용하게 된다.
------------------------------------------------------------
--EX)CASE 기본 구문1의 사용1 -> 2006년 3월 데이터의 판매일자에 대한 요일을 영문 3문자로 표기.
------------------------------------------------------------
SELECT T1.SaleYMD,
CASE WHEN DATEPART(dw, T1.SaleYMD) = 1 THEN 'Sun'
WHEN DATEPART(dw, T1.SaleYMD) = 2 THEN 'Mon'
WHEN DATEPART(dw, T1.SaleYMD) = 3 THEN 'Tue'
WHEN DATEPART(dw, T1.SaleYMD) = 4 THEN 'Wed'
WHEN DATEPART(dw, T1.SaleYMD) = 5 THEN 'Thu'
WHEN DATEPART(dw, T1.SaleYMD) = 6 THEN 'Fri'
WHEN DATEPART(dw, T1.SaleYMD) = 7 THEN 'Sat'
END DayOfWeek,
T1.ModelID, T1.Qty
FROM Sales T1
WHERE T1.SaleYMD LIKE '200603%'
CASE의 기본 구문2 : CASE를 사용하는 또 다른 구문이 있다.
SELECT CASE <값1> WHEN <값2> THEN <결과값1>
WHEN <값3> THEN <결과값2>
…
ELSE <결과값3>
END
------------------------------------------------------------
--EX)CASE 기본 구문1의 사용2 -> 2006년 6월 데이터에 대해 판매 금액이 판매 금액이 백만원보다 낮으면 Low로
--백만원을 넘고 이백만원 사이이면 Mid로 이백만원 이상일 경우는 High로 판매에 대해 가격 등급을 구하기.
------------------------------------------------------------
SELECT T1.SaleYMD,
T1.ModelID,
T1.Qty,
T1.Amount,
CASE WHEN T1.Amount < 1000000 THEN 'Low'
WHEN T1.Amount >= 1000000 AND T1.Amount < 2000000 THEN 'Mid'
WHEN T1.Amount >= 2000000 THEN 'High'
END PriceGrade
FROM Sales T1
WHERE T1.SaleYMD LIKE '200606%'
--각격등급, 모델별 정렬을 해보도록 하자. -> ORDER BY에도 CASE문을 그대로 사용할 수 있다.
SELECT T1.SaleYMD,
T1.ModelID,
T1.Qty,
T1.Amount,
CASE WHEN T1.Amount < 1000000 THEN 'Low'
WHEN T1.Amount >= 1000000 AND T1.Amount < 2000000 THEN 'Mid'
WHEN T1.Amount >= 2000000 THEN 'High'
END PriceGrade
FROM Sales T1
WHERE T1.SaleYMD LIKE '200606%'
ORDER BY
CASE WHEN T1.Amount < 1000000 THEN 0
WHEN T1.Amount >= 1000000 AND T1.Amount < 2000000 THEN 1
WHEN T1.Amount >= 2000000 THEN 2
END DESC, --High인 가격부터 나오도록 처리 -> Low, Mid, High를 그대로 사용하면 정렬은 알파벳순으로 된다.(H->L->M)
T1.ModelID
--CASE문에 적은 조건들중에 가장 위에 조건부터 처리 된다. 즉, 위의 예제에서
--CASE의 첫번째 조건인 Amount가 백만 미만인 경우가 아닌 경우에만 두번째 조건이 수행된다. 그러므로 두 번째 조건에서
--백만 이상이란 조건은 필요없다. 마찬가지로 첫 번째 조건과 두 번째 조건을 모두 만족시키지 않은 Amount는 당연히 이백만 이상인 금액이므로
--3번째 조건을 사용하지 않고 ELSE문으로 처리해도 된다.
SELECT T1.SaleYMD,
T1.ModelID,
T1.Qty,
T1.Amount,
CASE WHEN T1.Amount < 1000000 THEN 'Low'
WHEN T1.Amount < 2000000 THEN 'Mid'
ELSE 'High'
END PriceGrade
FROM Sales T1
WHERE T1.SaleYMD LIKE '200606%'
ORDER BY
CASE WHEN T1.Amount < 1000000 THEN 0
WHEN T1.Amount < 2000000 THEN 1
ELSE 2
END DESC, --High인 가격부터 나오도록 처리 -> Low, Mid, High를 그대로 사용하면 정렬은 알파벳순으로 된다.(H->L->M)
T1.ModelID
------------------------------------------------------------
--EX)CASE 기본 구문2의 사용 2-2006년 3월 데이터의 판매일자에 대한 요일을 영문 3문자로 표기
------------------------------------------------------------
SELECT T1.SaleYMD,
CASE DATEPART(dw, T1.SaleYMD)
WHEN 1 THEN 'Sun'
WHEN 2 THEN 'Mon'
WHEN 3 THEN 'Tue'
WHEN 4 THEN 'Wed'
WHEN 5 THEN 'Thu'
WHEN 6 THEN 'Fri'
WHEN 7 THEN 'Sat'
END DayOfWeek,
T1.ModelID, T1.Qty
FROM Sales T1
WHERE T1.SaleYMD LIKE '200603%'
------------------------------------------------------------
--EX)CASE를 이용한 GROUP BY이해하기->이전에 사용한 각격대별로 GROUP BY를 지어서 가격대별 판매수량을 구한다.
--Low 등급 : 백만원 미만 가격
--Mid 등급 : 백만원 이상 이백만원 미만 가격
--High 등급 : 이백만원 이상 가격
------------------------------------------------------------
SELECT CASE WHEN T1.Amount < 1000000 THEN 'Low'
WHEN T1.Amount < 2000000 THEN 'Mid'
ELSE 'High'
END PriceGrade,
SUM(T1.Qty) QtyByPriceGrade
FROM Sales T1
WHERE T1.SaleYMD LIKE '200606%'
GROUP BY
CASE WHEN T1.Amount < 1000000 THEN 'Low'
WHEN T1.Amount < 2000000 THEN 'Mid'
ELSE 'High'
END
------------------------------------------------------------
--EX)CASE의 조건절에서 여러 조건 사용하기
--월요일부터 금요일까지의 가격등급은 이전과 동일하나 토요일, 일요일일 경우에는 다른 가격등급을 적용한다.
--평일의 각격 등급
-- Low 등급 : 백만원 미만 가격
-- Mid 등급 : 백만원 이상 이백만원 미만 가격
-- High 등급 : 이백만원 이상 가격
--토,일요일의 각격 등급
-- Low 등급 : 120만원 미만 가격
-- Mid 등급 : 120만원 이상 210만원 미만 가격
-- High 등급 : 210만원 이상 가격
------------------------------------------------------------
SELECT CASE WHEN DATEPART(dw, T1.SaleYMD) IN (1, 7) AND T1.Amount < 1200000 THEN 'Low'
WHEN DATEPART(dw, T1.SaleYMD) IN (1, 7) AND T1.Amount < 2100000 THEN 'Mid'
WHEN DATEPART(dw, T1.SaleYMD) IN (1, 7) AND T1.Amount >= 2100000 THEN 'High'
WHEN DATEPART(dw, T1.SaleYMD) NOT IN (1, 7) AND T1.Amount < 1000000 THEN 'Low'
WHEN DATEPART(dw, T1.SaleYMD) NOT IN (1, 7) AND T1.Amount < 2000000 THEN 'Mid'
WHEN DATEPART(dw, T1.SaleYMD) NOT IN (1, 7) AND T1.Amount >= 2000000 THEN 'High'
END PriceGrade,
SUM(T1.Qty) QtyByPriceGrade
FROM Sales T1
WHERE T1.SaleYMD LIKE '200606%'
GROUP BY
CASE WHEN DATEPART(dw, T1.SaleYMD) IN (1, 7) AND T1.Amount < 1200000 THEN 'Low'
WHEN DATEPART(dw, T1.SaleYMD) IN (1, 7) AND T1.Amount < 2100000 THEN 'Mid'
WHEN DATEPART(dw, T1.SaleYMD) IN (1, 7) AND T1.Amount >= 2100000 THEN 'High'
WHEN DATEPART(dw, T1.SaleYMD) NOT IN (1, 7) AND T1.Amount < 1000000 THEN 'Low'
WHEN DATEPART(dw, T1.SaleYMD) NOT IN (1, 7) AND T1.Amount < 2000000 THEN 'Mid'
WHEN DATEPART(dw, T1.SaleYMD) NOT IN (1, 7) AND T1.Amount >= 2000000 THEN 'High'
END
------------------------------------------------------------
--EX)중첩된 CASE문 사용하기 - 위와 동일한 SQL을 중첩된 CASE문으로 해결.
------------------------------------------------------------
SELECT CASE WHEN DATEPART(dw, T1.SaleYMD) IN (1, 7) THEN
CASE WHEN T1.Amount < 1200000 THEN 'Low'
WHEN T1.Amount < 2100000 THEN 'Mid'
WHEN T1.Amount >= 2100000 THEN 'High'
END
WHEN DATEPART(dw, T1.SaleYMD) NOT IN (1, 7) THEN
CASE WHEN T1.Amount < 1000000 THEN 'Low'
WHEN T1.Amount < 2000000 THEN 'Mid'
WHEN T1.Amount >= 2000000 THEN 'High'
END
END PriceGrade,
SUM(T1.Qty) QtyByPriceGrade
FROM Sales T1
WHERE T1.SaleYMD LIKE '200606%'
GROUP BY
CASE WHEN DATEPART(dw, T1.SaleYMD) IN (1, 7) THEN
CASE WHEN T1.Amount < 1200000 THEN 'Low'
WHEN T1.Amount < 2100000 THEN 'Mid'
WHEN T1.Amount >= 2100000 THEN 'High'
END
WHEN DATEPART(dw, T1.SaleYMD) NOT IN (1, 7) THEN
CASE WHEN T1.Amount < 1000000 THEN 'Low'
WHEN T1.Amount < 2000000 THEN 'Mid'
WHEN T1.Amount >= 2000000 THEN 'High'
END
END
------------------------------------------------------------
--응용EX)CASE 예제1.2006년3월 1일부터 10일까지의 판매에 대해서 1일부터 10일까지의 모델별 판매 수량 보여주기.
--여기서 1일부터 10일은 컬럼이 되어야 한다.
--결과의 키를 찾아내는 훈련이 필요하다. -> 모델별 집계이므로 모델ID가 키가 된다.
--GROUP BY ModelID를 하면 ModelID가 키가된 결과집합을 만들어 내게 된다.
------------------------------------------------------------
모델ID + 1일판매수량 + 2일판매수량 + ... + 10일판매수량
SELECT T1.ModelID,
SUM(CASE WHEN RIGHT(T1.SaleYMD,2) = '01' THEN T1.Qty ELSE 0 END) QTY_1,
SUM(CASE WHEN RIGHT(T1.SaleYMD,2) = '02' THEN T1.Qty ELSE 0 END) QTY_2,
SUM(CASE WHEN RIGHT(T1.SaleYMD,2) = '03' THEN T1.Qty ELSE 0 END) QTY_3,
SUM(CASE WHEN RIGHT(T1.SaleYMD,2) = '04' THEN T1.Qty ELSE 0 END) QTY_4,
SUM(CASE WHEN RIGHT(T1.SaleYMD,2) = '05' THEN T1.Qty ELSE 0 END) QTY_5,
SUM(CASE WHEN RIGHT(T1.SaleYMD,2) = '06' THEN T1.Qty ELSE 0 END) QTY_6,
SUM(CASE WHEN RIGHT(T1.SaleYMD,2) = '07' THEN T1.Qty ELSE 0 END) QTY_7,
SUM(CASE WHEN RIGHT(T1.SaleYMD,2) = '08' THEN T1.Qty ELSE 0 END) QTY_8,
SUM(CASE WHEN RIGHT(T1.SaleYMD,2) = '09' THEN T1.Qty ELSE 0 END) QTY_9,
SUM(CASE WHEN RIGHT(T1.SaleYMD,2) = '10' THEN T1.Qty ELSE 0 END) QTY_10
FROM Sales T1
WHERE T1.SaleYMD >= '20060301'
AND T1.SaleYMD <= '20060310'
GROUP BY T1.ModelID
ORDER BY T1.ModelID
------------------------------------------------------------
--응용EX)CASE 예제2.2006년 2월 판매에 대해 모델별 요일별 판매수량 보여주기.
--요일은 컬럼이 되어야 한다.
------------------------------------------------------------
모델ID + 월요일 + 화요일 + 수요일 + ... + 일요일
SELECT T1.ModelID,
SUM(CASE WHEN DATEPART(dw, T1.SaleYMD) = 2 THEN T1.Qty ELSE 0 END) QTY_Mon,
SUM(CASE WHEN DATEPART(dw, T1.SaleYMD) = 3 THEN T1.Qty ELSE 0 END) QTY_Tue,
SUM(CASE WHEN DATEPART(dw, T1.SaleYMD) = 4 THEN T1.Qty ELSE 0 END) QTY_Wed,
SUM(CASE WHEN DATEPART(dw, T1.SaleYMD) = 5 THEN T1.Qty ELSE 0 END) QTY_Thu,
SUM(CASE WHEN DATEPART(dw, T1.SaleYMD) = 6 THEN T1.Qty ELSE 0 END) QTY_Fri,
SUM(CASE WHEN DATEPART(dw, T1.SaleYMD) = 7 THEN T1.Qty ELSE 0 END) QTY_Sat,
SUM(CASE WHEN DATEPART(dw, T1.SaleYMD) = 1 THEN T1.Qty ELSE 0 END) QTY_Sun
FROM Sales T1
WHERE T1.SaleYMD LIKE '200602%'
GROUP BY T1.ModelID
------------------------------------------------------------
--응용EX)CASE 예제3.2006년 3월 판매에 대해서 모델별로 월별 판매 수량을 구해서 모델별 판매 수량 등급을 구한다.
--판매수량 등급 : 300개 미만 판매 : LowSale
-- : 300개 이상 450개 미만 판매 : MidSale
-- : 450개 이상 판매 : HighSale
------------------------------------------------------------
모델ID + 판매수량 + 모델등급
SELECT T1.ModelID,
SUM(Qty) TTL_Qty,
CASE WHEN SUM(Qty) < 300 THEN 'LowSale'
WHEN SUM(Qty) < 450 THEN 'MidSale'
WHEN SUM(Qty) >= 450 THEN 'HighSale'
END ModelSaleGrade
FROM Sales T1
WHERE T1.SaleYMD LIKE '200603%'
GROUP BY T1.ModelID
--위의 결과를 HighSale, MidSale, LowSale순으로 정렬을 한다.
SELECT T1.ModelID,
SUM(Qty) TTL_Qty,
CASE WHEN SUM(Qty) < 300 THEN 'LowSale'
WHEN SUM(Qty) < 450 THEN 'MidSale'
WHEN SUM(Qty) >= 450 THEN 'HighSale'
END ModelSaleGrade
FROM Sales T1
WHERE T1.SaleYMD LIKE '200603%'
GROUP BY T1.ModelID
ORDER BY
CASE WHEN SUM(Qty) < 300 THEN 0
WHEN SUM(Qty) < 450 THEN 1
WHEN SUM(Qty) >= 450 THEN 2
END DESC
------------------------------------------------------------
--EX)INNER JOIN사용하기 – 판매를 실시한 사원의 이름도 조회
------------------------------------------------------------
SELECT T3.EmployeeName SalesmanName, T2.SalesmanID, T1.SaleYMD, T1.SalesTime, T1.ModelID, T1.Qty
FROM Sales T1
INNER JOIN ShopSalesman T2
ON T2.SalesmanSeq = T1.SalesmanSeq
INNER JOIN Employee T3
ON T3.EmployeeID = T2.SalesmanID
WHERE T1.SaleYMD BETWEEN '20060501' AND '20060530'
ORDER BY T3.EmployeeName
------------------------------------------------------------
--응용EX)INNER JOIN 예제1.모델과 제품명 같이 보여주기 : 모델정보 + 제품명
------------------------------------------------------------
->모델테이블 : Model
->제품테이블 : Product
SELECT T1.ModelID, T1.ModelName, T1.ProductCode, T2.ProductName
FROM Model T1
INNER JOIN Product T2
ON T1.ProductCode = T2.ProductCode
------------------------------------------------------------
--응용EX)INNER JOIN 예제2.제품명별 모델종류 카운트 보여주기 : 제품명 + 해당제품에 속한 모델의 카운트
------------------------------------------------------------
SELECT T1.ProductCode, T1.ProductName, COUNT(*)
FROM Product T1
INNER JOIN Model T2
ON T1.ProductCode = T2.ProductCode
GROUP BY T1.ProductCode, T1.ProductName
------------------------------------------------------------
--응용EX)INNER JOIN 예제3.모델별 인센티브와 제품명 보여주기 : 제품명+모델+인센티브시작일+인센티브종료일+인센티브 금액
------------------------------------------------------------
SELECT T3.ProductName, T1.ModelID, T2.StartYMD, T2.EndYMD, T2.IncentiveAmt
FROM Model T1
INNER JOIN ModelIncentive T2
ON T1.ModelID = T2.ModelID
INNER JOIN Product T3
ON T3.ProductCode = T1.ProductCode
------------------------------------------------------------
--응용EX)INNER JOIN 예제4.2006년 5월 판매에 대해 판매원ID, 판매원명과 판매일자, 수량정보 보여주기
------------------------------------------------------------
판매원ID, 판매원명, 판매일자, 수량
SELECT T2.SalesmanID, T1.EmployeeName SalesmanName, T3.SaleYMD, T3.Qty
FROM Employee T1
INNER JOIN ShopSalesman T2
ON T1.EmployeeID = T2.SalesmanID
INNER JOIN Sales T3
ON T3.SalesmanSeq = T2.SalesmanSeq
AND T3.SaleYMD LIKE '200605%'
WHERE T1.EmployeeType = '0002' --Salesman
--동일한 결과를 얻는 조인
SELECT T2.SalesmanID, T3.EmployeeName SalesmanName, T1.SaleYMD, T1.Qty
FROM Sales T1
INNER JOIN ShopSalesman T2
ON T2.SalesmanSeq = T1.SalesmanSeq
INNER JOIN Employee T3
ON T3.EmployeeID = T2.SalesmanID
AND T3.EmployeeType = '0002'
WHERE T1.SaleYMD LIKE '200605%'
------------------------------------------------------------
--응용EX)INNER JOIN 예제5.2006년 5월 판매에 대해 판매원 ID별로 판매수량 집계하기
------------------------------------------------------------
판매원ID, 판매원명, 판매수량
SELECT T2.SalesmanID, T1.EmployeeName SalesmanName, SUM(T3.Qty) TTL_Qty
FROM Employee T1
INNER JOIN ShopSalesman T2
ON T1.EmployeeID = T2.SalesmanID
INNER JOIN Sales T3
ON T3.SalesmanSeq = T2.SalesmanSeq
AND T3.SaleYMD LIKE '200605%'
WHERE T1.EmployeeType = '0002' --Salesman
GROUP BY T2.SalesmanID, T1.EmployeeName
ORDER BY SUM(T3.Qty) DESC --GROUP BY 를 사용했을 경우에 ORDER BY에 집계함수가 올 수 있다.
------------------------------------------------------------
--응용EX)INNER JOIN 예제6.2006년 5월 판매에 대해 상장ID, 상장명별로 판매수량, 판매 금액 집계하기
------------------------------------------------------------
SELECT T1.ShopID, MIN(T1.ShopName) ShopName,
SUM(T3.Qty) TTL_Qty, SUM(T3.Amount) TTL_Amount
FROM SalesShop T1
INNER JOIN ShopSalesman T2
ON T1.ShopID = T2.ShopID
INNER JOIN Sales T3
ON T3.SalesmanSeq = T2.SalesmanSeq
AND T3.SaleYMD LIKE '200605%'
GROUP BY T1.ShopID
ORDER BY SUM(T3.Amount) DESC
------------------------------------------------------------
--응용EX)INNER JOIN 예제7.2006년 5월 판매 정보와 판매가 발생된 상점의 상점명, 판매한 판매원명, 상점의 상점매니점명 보여주기
------------------------------------------------------------
: 판매일+모델ID+수량+금액+상점명+판매원명+상점관리자명
SELECT T1.SaleYMD, T1.ModelID, T1.Qty, T1.Amount,
T4.ShopName,
T3.EmployeeName SalesmanName,
T6.EmployeeName ManagerName
FROM Sales T1
INNER JOIN ShopSalesMan T2
ON T1.SalesmanSeq = T2.SalesmanSeq
INNER JOIN Employee T3
ON T3.EmployeeID = T2.SalesmanID
INNER JOIN SalesShop T4
ON T4.ShopID = T2.ShopID
INNER JOIN ShopManager T5
ON T5.ShopID = T4.ShopID
INNER JOIN Employee T6
ON T6.EmployeeID = T5.ManagerID
WHERE T1.SaleYMD LIKE '200605%'
------------------------------------------------------------
--EX)LEFT OUTER JOIN사용하기
------------------------------------------------------------
--2006년 3월 1일 데이터를 조회 -- 171건
SELECT *
FROM Sales T1
WHERE T1.SaleYMD = '20060301'
ORDER BY T1.ModelID
--2006년 3월 1일 데이터에 대해 판매가 되지 않은 모델도 모두 보고 싶은 경우 --177건
SELECT *
FROM Model T1
LEFT OUTER JOIN Sales T2
ON T1.ModelID = T2.ModelID
AND T2.SaleYMD = '20060301'
ORDER BY T1.ModelID
------------------------------------------------------------
--LEFT OUTER JOIN 이해하기
------------------------------------------------------------
SELECT *
FROM Model T1
LEFT OUTER JOIN Sales T2
ON T1.ModelID = T2.ModelID
------------------------------------------------------------
--RIGHT OUTER JOIN 이해하기
------------------------------------------------------------
SELECT *
FROM Model T1
RIGHT OUTER JOIN Sales T2
ON T1.ModelID = T2.ModelID
------------------------------------------------------------
--FULL OUTER JOIN 이해하기
------------------------------------------------------------
SELECT *
FROM Model T1
FULL OUTER JOIN Sales T2
ON T1.ModelID = T2.ModelID
------------------------------------------------------------
--응용EX)OUTER JOIN 예제1.2006년 5월 1일에 판매된 모든 판매를 조회, 판매되지 않은 모델도 판매에 대한 정보를 NULL로 조회
------------------------------------------------------------
SELECT *
FROM Model T1
LEFT OUTER JOIN Sales T2
ON T1.ModelID = T2.ModelID
AND T2.SaleYMD = '20060501'
ORDER BY T1.ModelID
------------------------------------------------------------
--응용EX)OUTER JOIN 예제2.2006년 5월 1일에 판매된 정보를 모델별로 판매수량을 집계한다. 판매되지 않은 모델의 수량은 0으로 조회
------------------------------------------------------------
SELECT T1.ModelID,
ISNULL(SUM(T2.Qty),0) SaleQty
FROM Model T1
LEFT OUTER JOIN Sales T2
ON T1.ModelID = T2.ModelID
AND T2.SaleYMD = '20060501'
GROUP BY T1.ModelID
ORDER BY T1.ModelID
------------------------------------------------------------
--응용EX)OUTER JOIN 예제3.2006년 5월 1일에 판매된 정보를 제품별로 판매수량을 집계한다. 판매되지 않은 제품의 수량은 0으로 조회
------------------------------------------------------------
--제품과 모델은 마스터 테이블이므로 조인하면 항상 모든 데이터가 나온다.
--필요없이 LEFT OUTER JOIN을 할 필요는 없다.
SELECT T1.ProductCode, ISNULL(SUM(T3.Qty),0)
FROM Product T1
INNER JOIN Model T2
ON T2.ProductCode = T1.ProductCode
LEFT OUTER JOIN Sales T3
ON T3.ModelID = T2.ModelID
AND T3.SaleYMD = '20060501'
GROUP BY T1.ProductCode
------------------------------------------------------------
--응용EX)OUTER JOIN 예제4. 2006년 5월 1일 판매중에 판매가 없는 상점의 판매원 ID찾아내기
------------------------------------------------------------
SELECT T1.EmployeeID, T1.EmployeeName
FROM Employee T1
INNER JOIN ShopSalesman T2
ON T1.EmployeeID = T2.SalesmanID
--조회하는 판매일자에 활성화되어 있던 상점의 Salesman만 대상으로 한다.
AND T2.SalesStartYMD <= '20060501' AND T2.SalesEndYMD >= '20060501'
LEFT OUTER JOIN Sales T3
ON T3.SalesmanSeq = T2.SalesmanSeq
AND T3.SaleYMD = '20060501'
WHERE T3.SalesmanSeq IS NULL
--이 문장은 NOT EXISTS로도 변경이 가능하다.
------------------------------------------------------------
--EX)파생된 테이블 사용하기
------------------------------------------------------------
--1:N관계를 1:1로 만들어 조인하기
--2006년 6월 판매에 대해 각 상점별로 판매수량, 판매금액을 구하기
SELECT T1.ShopID, T1.ShopName, SUM(T3.Qty) Qty, SUM(T3.Amt) Amt
FROM SalesShop T1
INNER JOIN ShopSalesman T2
ON T1.ShopID = T2.ShopID
INNER JOIN (
--Sales를 ShopsalesmanSeq별로 GROUP BY를 수행한 집합을 만들어 낸다.
SELECT A.SalesmanSeq, SUM(Qty) Qty, SUM(Amount) Amt
FROM Sales A
WHERE A.SaleYMD LIKE '200606%'
GROUP BY A.SalesmanSeq
) T3
ON T3.SalesmanSeq = T2.SalesmanSeq
GROUP BY T1.ShopID, T1.ShopName
--파생된 테이블을 사용하면 단계적으로 SQL을 분석하기 쉬워진다.
--조인횟수가 줄어들게 되는 경우가 있다.
------------------------------------------------------------
--EX)WHERE절의 조건은 파생된 테이블 내에서부터 주도록 한다.
------------------------------------------------------------
SELECT *
FROM Model T1
INNER JOIN Product T2
ON T1.ProductCode = T2.ProductCode
INNER JOIN (
SELECT T1.SaleYMD, T1.ModelID, SUM(T1.Qty) Qty
FROM Sales T1
WHERE T1.SaleYMD LIKE '200606%'
GROUP BY T1.SaleYMD, T1.ModelID
) T3
ON T3.ModelID = T1.ModelID
--조회조건을 안에 주니, Sales테이블에서 클러스터드 인덱스를 스캔해 4381건을 얻어낸후 GROUP BY를 수행했다.
Rows EXEC StmtTxt
348 1 SELECT * FROM Model T1 INNER JOIN Product T2 ON T1.ProductCode = T2.ProductCode INNER JOIN ( SELECT T1.SaleYMD, T1.ModelID, SUM(T1.Qty) Qty FROM Sales T1 WHERE T1.SaleYMD LIKE '200606%' GROUP BY T1.SaleYMD, T1.ModelID ) T3 ON T3.ModelID = T1.ModelID 4 1 0 NULL NULL NULL NULL 180.87758 NULL NULL NULL 2.5557406 NULL NULL SELECT 0 NULL
348 1 |--Hash Match(Inner Join, HASH:([T1].[ModelID])=([T1].[ModelID]), RESIDUAL:([T1].[ModelID]=[T1].[ModelID])) 4 3 1 Hash Match Inner Join HASH:([T1].[ModelID])=([T1].[ModelID]), RESIDUAL:([T1].[ModelID]=[T1].[ModelID]) NULL 180.87758 0.0 2.2488363E-2 176 2.5557225 [T1].[SaleYMD], [T1].[ModelID], [Expr1001], [T1].[ModelID], [T1].[ModelName], [T1].[ProductCode], [T2].[ProductCode], [T2].[ProductName] NULL PLAN_ROW 0 1.0
19 1 |--Nested Loops(Inner Join, OUTER REFERENCES:([T1].[ProductCode])) 4 4 3 Nested Loops Inner Join OUTER REFERENCES:([T1].[ProductCode]) NULL 5.7000003 0.0 7.9420002E-5 207 4.5676775E-2 [T1].[ModelID], [T1].[ModelName], [T1].[ProductCode], [T2].[ProductCode], [T2].[ProductName] NULL PLAN_ROW 0 1.0
19 1 | |--Clustered Index Scan(OBJECT:([ILLHWAN_SQL].[dbo].[Model].[PK_Model] AS [T1])) 4 5 4 Clustered Index Scan Clustered Index Scan OBJECT:([ILLHWAN_SQL].[dbo].[Model].[PK_Model] AS [T1]) [T1].[ModelID], [T1].[ModelName], [T1].[ProductCode] 19.0 3.7578501E-2 0.0000994 128 3.7677899E-2 [T1].[ModelID], [T1].[ModelName], [T1].[ProductCode] NULL PLAN_ROW 0 1.0
19 19 | |--Clustered Index Seek(OBJECT:([ILLHWAN_SQL].[dbo].[Product].[PK_Product] AS [T2]), SEEK:([T2].[ProductCode]=[T1].[ProductCode]) ORDERED FORWARD) 4 6 4 Clustered Index Seek Clustered Index Seek OBJECT:([ILLHWAN_SQL].[dbo].[Product].[PK_Product] AS [T2]), SEEK:([T2].[ProductCode]=[T1].[ProductCode]) ORDERED FORWARD [T2].[ProductCode], [T2].[ProductName] 1.0 6.3284999E-3 7.9603E-5 88 7.9194568E-3 [T2].[ProductCode], [T2].[ProductName] NULL PLAN_ROW 0 19.0
348 1 |--Hash Match(Aggregate, HASH:([T1].[SaleYMD], [T1].[ModelID]), RESIDUAL:([T1].[SaleYMD]=[T1].[SaleYMD] AND [T1].[ModelID]=[T1].[ModelID]) DEFINE:([Expr1001]=SUM([T1].[Qty]))) 4 7 3 Hash Match Aggregate HASH:([T1].[SaleYMD], [T1].[ModelID]), RESIDUAL:([T1].[SaleYMD]=[T1].[SaleYMD] AND [T1].[ModelID]=[T1].[ModelID]) [Expr1001]=SUM([T1].[Qty]) 602.92523 0.0 0.08101359 43 2.4875546 [T1].[SaleYMD], [T1].[ModelID], [Expr1001] NULL PLAN_ROW 0 1.0
4381 1 |--Clustered Index Scan(OBJECT:([ILLHWAN_SQL].[dbo].[Sales].[PK_Sales] AS [T1]), WHERE:(like([T1].[SaleYMD], '200606%', NULL))) 4 8 7 Clustered Index Scan Clustered Index Scan OBJECT:([ILLHWAN_SQL].[dbo].[Sales].[PK_Sales] AS [T1]), WHERE:(like([T1].[SaleYMD], '200606%', NULL)) [T1].[Qty], [T1].[SaleYMD], [T1].[ModelID] 5108.5137 2.2168376 0.1054255 96 2.3222632 [T1].[Qty], [T1].[SaleYMD], [T1].[ModelID] NULL PLAN_ROW 0 1.0
SELECT *
FROM Model T1
INNER JOIN Product T2
ON T1.ProductCode = T2.ProductCode
INNER JOIN (
SELECT T1.SaleYMD, T1.ModelID, SUM(T1.Qty) Qty
FROM Sales T1
GROUP BY T1.SaleYMD, T1.ModelID
) T3
ON T3.ModelID = T1.ModelID
WHERE T3.SaleYMD LIKE '200606%'
--조회조건을 밖에 주니, Sales테이블에서 클러스터드 인덱스를 스캔해 95770건을 얻어낸후 GROUP BY를 수행하고, 그 중에서 SaleYMD가 200606인 데이터를 얻어냈다.
Rows EXEC StmtTxt
348 1 SELECT * FROM Model T1 INNER JOIN Product T2 ON T1.ProductCode = T2.ProductCode INNER JOIN ( SELECT T1.SaleYMD, T1.ModelID, SUM(T1.Qty) Qty FROM Sales T1 GROUP BY T1.SaleYMD, T1.ModelID ) T3 ON T3.ModelID = T1.ModelID WHERE T3.SaleYMD LIKE '200606%' 5 1 0 NULL NULL NULL NULL 180.87758 NULL NULL NULL 3.6041574 NULL NULL SELECT 0 NULL
348 1 |--Hash Match(Inner Join, HASH:([T1].[ModelID])=([T1].[ModelID]), RESIDUAL:([T1].[ModelID]=[T1].[ModelID])) 5 3 1 Hash Match Inner Join HASH:([T1].[ModelID])=([T1].[ModelID]), RESIDUAL:([T1].[ModelID]=[T1].[ModelID]) NULL 180.87758 0.0 2.2488363E-2 176 3.6041393 [T1].[SaleYMD], [T1].[ModelID], [Expr1001], [T1].[ModelID], [T1].[ModelName], [T1].[ProductCode], [T2].[ProductCode], [T2].[ProductName] NULL PLAN_ROW 0 1.0
19 1 |--Nested Loops(Inner Join, OUTER REFERENCES:([T1].[ProductCode])) 5 4 3 Nested Loops Inner Join OUTER REFERENCES:([T1].[ProductCode]) NULL 5.7000003 0.0 7.9420002E-5 207 4.5676775E-2 [T1].[ModelID], [T1].[ModelName], [T1].[ProductCode], [T2].[ProductCode], [T2].[ProductName] NULL PLAN_ROW 0 1.0
19 1 | |--Clustered Index Scan(OBJECT:([ILLHWAN_SQL].[dbo].[Model].[PK_Model] AS [T1])) 5 5 4 Clustered Index Scan Clustered Index Scan OBJECT:([ILLHWAN_SQL].[dbo].[Model].[PK_Model] AS [T1]) [T1].[ModelID], [T1].[ModelName], [T1].[ProductCode] 19.0 3.7578501E-2 0.0000994 128 3.7677899E-2 [T1].[ModelID], [T1].[ModelName], [T1].[ProductCode] NULL PLAN_ROW 0 1.0
19 19 | |--Clustered Index Seek(OBJECT:([ILLHWAN_SQL].[dbo].[Product].[PK_Product] AS [T2]), SEEK:([T2].[ProductCode]=[T1].[ProductCode]) ORDERED FORWARD) 5 6 4 Clustered Index Seek Clustered Index Seek OBJECT:([ILLHWAN_SQL].[dbo].[Product].[PK_Product] AS [T2]), SEEK:([T2].[ProductCode]=[T1].[ProductCode]) ORDERED FORWARD [T2].[ProductCode], [T2].[ProductName] 1.0 6.3284999E-3 7.9603E-5 88 7.9194568E-3 [T2].[ProductCode], [T2].[ProductName] NULL PLAN_ROW 0 19.0
348 1 |--Filter(WHERE:(like([T1].[SaleYMD], '200606%', NULL))) 5 7 3 Filter Filter WHERE:(like([T1].[SaleYMD], '200606%', NULL)) NULL 602.92523 0.0 9.9467468E-3 43 3.5359712 [T1].[SaleYMD], [T1].[ModelID], [Expr1001] NULL PLAN_ROW 0 1.0
8491 1 |--Hash Match(Aggregate, HASH:([T1].[SaleYMD], [T1].[ModelID]), RESIDUAL:([T1].[SaleYMD]=[T1].[SaleYMD] AND [T1].[ModelID]=[T1].[ModelID]) DEFINE:([Expr1001]=SUM([T1].[Qty]))) 5 8 7 Hash Match Aggregate HASH:([T1].[SaleYMD], [T1].[ModelID]), RESIDUAL:([T1].[SaleYMD]=[T1].[SaleYMD] AND [T1].[ModelID]=[T1].[ModelID]) [Expr1001]=SUM([T1].[Qty]) 11303.121 0.0 1.2037612 43 3.5260243 [T1].[SaleYMD], [T1].[ModelID], [Expr1001] NULL PLAN_ROW 0 1.0
95770 1 |--Clustered Index Scan(OBJECT:([ILLHWAN_SQL].[dbo].[Sales].[PK_Sales] AS [T1])) 5 9 8 Clustered Index Scan Clustered Index Scan OBJECT:([ILLHWAN_SQL].[dbo].[Sales].[PK_Sales] AS [T1]) [T1].[Qty], [T1].[SaleYMD], [T1].[ModelID] 95770.0 2.2168376 0.1054255 96 2.3222632 [T1].[Qty], [T1].[SaleYMD], [T1].[ModelID] NULL PLAN_ROW 0 1.0
------------------------------------------------------------
--EX)SELECT절의 서브쿼리
------------------------------------------------------------
--2006-05-01의 판매를 조회, 각 판매의 상점명을 서브쿼리를 사용해서 가져오자.
--상관서브쿼리(외부의 값과 서브쿼리의 결과가 연관된다.)
SELECT T1.SaleYMD, T1.ModelID, T1.SalesTime, T1.Qty, T1.UnitPrice, T1.Amount,
( SELECT A.ShopName
FROM SalesShop A
INNER JOIN ShopSalesman B
ON A.ShopID = B.ShopID
WHERE B.SalesmanSeq = T1.SalesmanSeq) ShopName
FROM Sales T1
WHERE T1.SaleYMD = '20060501'
--2006년 5월1일의 판매모델별로 금액을 구하고, 모델별 판매금액 옆에 총 판매금액을 구한다.
--외부결과와 상관되지 않은 서브쿼리
SELECT T1.ModelID, SUM(T1.Amount) AmountByModel,
(SELECT SUM(A.Amount) FROM Sales A WHERE A.SaleYMD = '20060501')
FROM Sales T1
WHERE T1.SaleYMD = '20060501'
GROUP BY T1.ModelID
------------------------------------------------------------
--EX)WHERE절의 서브쿼리
------------------------------------------------------------
--2006년 6월에 판매 수량이 100개 이상인 판매원들만 조회하기
SELECT T1.EmployeeID, T1.EmployeeName
FROM Employee T1
INNER JOIN ShopSalesman T2
ON T1.EmployeeID = T2.SalesmanID
WHERE T2.SalesmanSeq IN (
SELECT T1.SalesmanSeq
FROM Sales T1
WHERE T1.SaleYMD LIKE '200606%'
GROUP BY T1.SalesmanSeq
HAVING SUM(T1.Qty) >= 100
)
--위의 문장을 파생된 테이블을 이용해서 해결할 수도 있다.
--파생된 테이블과 조인을 사용하면 판매 수량까지 얻어낼 수 있다. -> 일거양득
SELECT T1.EmployeeID, T1.EmployeeName, T3.Qty
FROM Employee T1
INNER JOIN ShopSalesman T2
ON T1.EmployeeID = T2.SalesmanID
INNER JOIN (
SELECT T1.SalesmanSeq, SUM(T1.Qty) Qty
FROM Sales T1
WHERE T1.SaleYMD LIKE '200606%'
GROUP BY T1.SalesmanSeq
HAVING SUM(T1.Qty) >= 100
) T3
ON T2.SalesmanSeq = T3.SalesmanSeq
--심하게 잘못 만든 SQL
SELECT T1.EmployeeID, T1.EmployeeName,
( SELECT SUM(A.Qty)
FROM Sales A
WHERE A.SaleYMD LIKE '200606%'
AND A.SalesmanSeq = T2.SalesmanSeq
) Qty
FROM Employee T1
INNER JOIN ShopSalesman T2
ON T1.EmployeeID = T2.SalesmanID
WHERE T2.SalesmanSeq IN (
SELECT T1.SalesmanSeq
FROM Sales T1
WHERE T1.SaleYMD LIKE '200606%'
GROUP BY T1.SalesmanSeq
HAVING SUM(T1.Qty) >= 100
)
------------------------------------------------------------
--응용EX)2006년5월의 판매에 대해서 모델별로 판매금액이 총판매금액에 대해 얼만큼 비중이 있는지 비율구하기
------------------------------------------------------------
모델+판매금액+판매비율(모델의판매금액/총판매금액*100)
SELECT T1.ModelID,
T1.AmtBYModel,
CONVERT(numeric(18,2), (T1.AmtBYModel / TTL_Amt) * 100.00) AmtRate
FROM (
SELECT T1.ModelID, SUM(T1.Amount) AmtBYModel,
(SELECT SUM(A.Amount) FROM Sales A WHERE A.SaleYMD LIKE '200605%') TTL_Amt
FROM Sales T1
WHERE T1.SaleYMD LIKE '200605%'
GROUP BY T1.ModelID
) T1
ORDER BY T1.ModelID
------------------------------------------------------------
--EX)UNION 사용하기
------------------------------------------------------------
--상점 관리자 집합
SELECT 'Manager' DataTP, T2.ShopName, T1.ManagerID
FROM ShopManager T1
INNER JOIN SalesShop T2
ON T1.ShopID = T2.ShopID
UNION
--상점 판매원 집합
SELECT 'Salesman', T2.ShopName, T1.SalesmanID
FROM ShopSalesman T1
INNER JOIN SalesShop T2
ON T1.ShopID = T2.ShopID
--위 집합은 절대 중복될 수 없다. 그러므로 UNION보다는 UNION ALL을 사용한다.
SELECT 'Manager' DataTP, T2.ShopName, T1.ManagerID
FROM ShopManager T1
INNER JOIN SalesShop T2
ON T1.ShopID = T2.ShopID
UNION ALL
--상점 판매원 집합
SELECT 'Salesman', T2.ShopName, T1.SalesmanID
FROM ShopSalesman T1
INNER JOIN SalesShop T2
ON T1.ShopID = T2.ShopID
------------------------------------------------------------
--EX)OR를 대신하는 UNION/UNION ALL
------------------------------------------------------------
--분기가 시작되는 월에 Open한 상점들
SELECT CASE WHEN T1.OpenYMD LIKE '200501%' THEN '1/4'
WHEN T1.OpenYMD LIKE '200504%' THEN '2/4'
WHEN T1.OpenYMD LIKE '200507%' THEN '3/4'
WHEN T1.OpenYMD LIKE '200510%' THEN '4/4'
END OpenQuarter,
T1.ShopID, T1.ShopName, T1.OpenYMD
FROM SalesShop T1
WHERE (T1.OpenYMD LIKE '200501%'
OR T1.OpenYMD LIKE '200504%'
OR T1.OpenYMD LIKE '200507%'
OR T1.OpenYMD LIKE '200510%')
--위와 같은 OR를 UNION ALL로 변경한다.(위의 결과도 중복되지 않으므로 UNION을 사용하지 않는다.)
--결과의 정렬 순서를 주목하자, 만약에 1/4분기에 대한 집합을 제일 아래서 처리한다면 1/4분기가 제일 아래로 내려간 결과가 반환될 것이다.
SELECT '1/4' OpenQuarter, T1.ShopID, T1.ShopName, T1.OpenYMD
FROM SalesShop T1
WHERE T1.OpenYMD LIKE '200501%'
UNION ALL
SELECT '2/4' OpenQuarter, T1.ShopID, T1.ShopName, T1.OpenYMD
FROM SalesShop T1
WHERE T1.OpenYMD LIKE '200504%'
UNION ALL
SELECT '3/4' OpenQuarter, T1.ShopID, T1.ShopName, T1.OpenYMD
FROM SalesShop T1
WHERE T1.OpenYMD LIKE '200507%'
UNION ALL
SELECT '4/4' OpenQuarter, T1.ShopID, T1.ShopName, T1.OpenYMD
FROM SalesShop T1
WHERE T1.OpenYMD LIKE '200510%'
------------------------------------------------------------
--EX)IN를 대신하는 UNION/UNION ALL
------------------------------------------------------------
--5월동안에 3개 모델을 판매한 판매원의 ID
SELECT DISTINCT T2.SalesmanID
FROM Sales T1
INNER JOIN ShopSalesman T2
ON T2.SalesmanSeq = T1.SalesmanSeq
WHERE T1.SaleYMD LIKE '200605%'
AND T1.ModelID IN ('PDA_M2', 'PDA_M3', 'PDA_M7')
--중복을 제거하는 DISTINCT가 있다. 그러므로 UNION을 사용한다.
SELECT T2.SalesmanID
FROM Sales T1
INNER JOIN ShopSalesman T2
ON T2.SalesmanSeq = T1.SalesmanSeq
WHERE T1.SaleYMD LIKE '200605%'
AND T1.ModelID = 'PDA_M2'
UNION
SELECT T2.SalesmanID
FROM Sales T1
INNER JOIN ShopSalesman T2
ON T2.SalesmanSeq = T1.SalesmanSeq
WHERE T1.SaleYMD LIKE '200605%'
AND T1.ModelID = 'PDA_M3'
UNION
SELECT T2.SalesmanID
FROM Sales T1
INNER JOIN ShopSalesman T2
ON T2.SalesmanSeq = T1.SalesmanSeq
WHERE T1.SaleYMD LIKE '200605%'
AND T1.ModelID = 'PDA_M7'
------------------------------------------------------------
--EX)총계 같이 보여주기
------------------------------------------------------------
--2006년 6월 1일 판매에 대해 판매원이름 모델ID, 판매수량, 금액 리스트를 보여주고,
--제일 마지막에 2006년 6월 1일의 총 판매수량과 판매금액을 같이 보여주시오.
SELECT T3.EmployeeName, T1.ModelID, T1.Qty, T1.Amount
FROM Sales T1
INNER JOIN ShopSalesman T2
ON T1.SalesmanSeq = T2.SalesmanSeq
INNER JOIN Employee T3
ON T3.EmployeeID = T2.SalesmanID
WHERE SaleYMD = '20060601'
UNION ALL
SELECT 'TTL', 'TTL', SUM(T1.Qty), SUM(T1.Amount)
FROM Sales T1
WHERE SaleYMD = '20060601'
------------------------------------------------------------
--EX)계층별 중간계도 포함하기
------------------------------------------------------------
--1.2006년6월 판매에 대해 상장이름, 모델ID 별로 판매수량, 판매금액을 집계
--2.1번 결과에 추가적으로 2006년 6월 판매에 대해 각 상장별로 판매수량, 판매금액을 집계해서 결과에 추가(모델별 집계를 제외한다.)
--3.1번과 2번의 결과에 2006년 6월 판매에 대해 총 판매수량, 판매금액을 집계해서 결과에 추가
SELECT T3.ShopID, T3.ShopName, T1.ModelID,
SUM(T1.Qty) ShopSaleQty, SUM(T1.Amount) ShopSaleAmount
FROM Sales T1
INNER JOIN ShopSalesman T2
ON T1.SalesmanSeq = T2.SalesmanSeq
INNER JOIN SalesShop T3
ON T3.ShopID = T2.ShopID
WHERE T1.SaleYMD LIKE '200606%'
GROUP BY T3.ShopID, T3.ShopName, T1.ModelID
UNION ALL
SELECT T3.ShopID, T3.ShopName, 'TTL',
SUM(T1.Qty) ShopSaleQty, SUM(T1.Amount) ShopSaleAmount
FROM Sales T1
INNER JOIN ShopSalesman T2
ON T1.SalesmanSeq = T2.SalesmanSeq
INNER JOIN SalesShop T3
ON T3.ShopID = T2.ShopID
WHERE T1.SaleYMD LIKE '200606%'
GROUP BY T3.ShopID, T3.ShopName
UNION ALL
SELECT NULL, 'TTL', 'TTL',
SUM(T1.Qty) TTL_Qty, SUM(T1.Amount) TTL_Amount
FROM Sales T1
WHERE T1.SaleYMD LIKE '200606%'
--위의 결과에 정렬 순서를 적용시키려면 어떻게 해야 할까?
--먼저 상장별로 데이터가 나오도록 한다. 상장별로 나오면서 상장, 모델별 집계된 데이터가 나오고, 상장별 집계된 데이터가 나온다.
--제일 마지막에 총 집계된 데이터가 나오도록 한다.
--원하는 정렬 결과를 얻기 위해 인라인뷰(파생된 테이블)을 사용한다.
SELECT T1.ShopID, T1.ShopName, T1.ModelID,
T1.ShopSaleQty, T1.ShopSaleAmount
FROM (
SELECT T3.ShopID, T3.ShopName, T1.ModelID,
SUM(T1.Qty) ShopSaleQty, SUM(T1.Amount) ShopSaleAmount
FROM Sales T1
INNER JOIN ShopSalesman T2
ON T1.SalesmanSeq = T2.SalesmanSeq
INNER JOIN SalesShop T3
ON T3.ShopID = T2.ShopID
WHERE T1.SaleYMD LIKE '200606%'
GROUP BY T3.ShopID, T3.ShopName, T1.ModelID
UNION ALL
SELECT T3.ShopID, T3.ShopName, 'TTL',
SUM(T1.Qty) ShopSaleQty, SUM(T1.Amount) ShopSaleAmount
FROM Sales T1
INNER JOIN ShopSalesman T2
ON T1.SalesmanSeq = T2.SalesmanSeq
INNER JOIN SalesShop T3
ON T3.ShopID = T2.ShopID
WHERE T1.SaleYMD LIKE '200606%'
GROUP BY T3.ShopID, T3.ShopName
UNION ALL
SELECT NULL, 'TTL', 'TTL',
SUM(T1.Qty) TTL_Qty, SUM(T1.Amount) TTL_Amount
FROM Sales T1
WHERE T1.SaleYMD LIKE '200606%'
) T1
ORDER BY CASE WHEN T1.ShopName <> 'TTL' THEN 1 --상장명이 TTL이 아닌 경우 먼저 나오도록 한다.(총계가 가장 늦게 나오게 하기 위해
ELSE 2 END,
T1.ShopName,
CASE WHEN T1.ModelID <> 'TTL' THEN 1 --모델ID가 TTL이 아닌 경우 먼저 나오도록 한다.(상장별 중간계가 늦게 나오게 하기 위해)
ELSE 2 END
------------------------------------------------------------
--응용EX)UNION 예제1. 2006년4월 판매에 대해서 제품,모델별 판매수량을 집계하고, 제품별 중간계를 추가한다.
------------------------------------------------------------
SELECT T2.ProductCode, T3.ProductName,
T1.ModelID, SUM(T1.Qty) ModelSaleQty
FROM Sales T1
INNER JOIN Model T2
ON T2.ModelID = T1.ModelID
INNER JOIN Product T3
ON T3.ProductCode = T2.ProductCode
WHERE T1.SaleYMD LIKE '200604%'
GROUP BY T2.ProductCode, T3.ProductName, T1.ModelID
UNION ALL
SELECT T2.ProductCode, T3.ProductName,
'TTL', SUM(T1.Qty) ProductSaleQty
FROM Sales T1
INNER JOIN Model T2
ON T2.ModelID = T1.ModelID
INNER JOIN Product T3
ON T3.ProductCode = T2.ProductCode
WHERE T1.SaleYMD LIKE '200604%'
GROUP BY T2.ProductCode, T3.ProductName
------------------------------------------------------------
--응용EX)UNION 예제2. 예제1의 결과에 대해 제품별, 모델별 정렬을 수행하는데,
--제품별중간계가 각 제품의 모데별 집계 결과보다 먼저 나오도록 구성
------------------------------------------------------------
SELECT T1.ProductCode, T1.ProductName,
T1.ModelID, T1.ModelSaleQty
FROM (
SELECT T2.ProductCode, T3.ProductName,
T1.ModelID, SUM(T1.Qty) ModelSaleQty
FROM Sales T1
INNER JOIN Model T2
ON T2.ModelID = T1.ModelID
INNER JOIN Product T3
ON T3.ProductCode = T2.ProductCode
WHERE T1.SaleYMD LIKE '200604%'
GROUP BY T2.ProductCode, T3.ProductName, T1.ModelID
UNION ALL
SELECT T2.ProductCode, T3.ProductName,
'TTL', SUM(T1.Qty) ProductSaleQty
FROM Sales T1
INNER JOIN Model T2
ON T2.ModelID = T1.ModelID
INNER JOIN Product T3
ON T3.ProductCode = T2.ProductCode
WHERE T1.SaleYMD LIKE '200604%'
GROUP BY T2.ProductCode, T3.ProductName
) T1
ORDER BY T1.ProductCode,
CASE WHEN T1.ModelID = 'TTL' THEN 1 ELSE 2 END
------------------------------------------------------------
--EX)CROSS JOIN 사용하기
------------------------------------------------------------
--지역별로 가능한 모든 판매장 형태 데이터 만들기
SELECT T2.RegionCode, T2.RegionName, T1.Code ShopType, T1.CodeName ShopTypeName
FROM MasterCode T1
CROSS JOIN Region T2
WHERE T1.CodeType = 'ShopType'
ORDER BY T2.RegionCode, T1.Code
--SalesShop에 등록된 강남지역의 매장이 백화점매장만 있다면, 대리점, 특판매장등의 판매등은 발생되지 않는다.
--하지만 발생되지 않은 경우도 데이터 결과에 포함해야 한다면 이와 같이 CROSS JOIN을 사용해서 가능한 모든 결과를 얻어낼 필요가 있다.
------------------------------------------------------------
--EX)CROSS JOIN을 사용해서 판매 데이터를 3배로 불리기
------------------------------------------------------------
--UNION ALL을 이용해 3건의 데이터를 만들고 이를 인라인 뷰에 집어넣어서 Sales테이블과 CROSS JOIN을 수행한다.
SELECT *
FROM Sales T1
CROSS JOIN
( SELECT 1 DATA_NO
UNION ALL
SELECT 2 DATA_NO
UNION ALL
SELECT 3 DATA_NO
) T2
------------------------------------------------------------
--EX)판매금액과 판매수량을 각각 로우로 분리하기
------------------------------------------------------------
--2006년 6월 1일 판매 데이터에 대해 모델별로 판매 수량, 금액을 집계해서 보여주는데,
--한 로우는 수량만, 한 로우는 금액이 나오도록 처리
--수량을 나타내는 로우일 경우에는 DATA_TP란 컬럼에 QTY로, 판매금액일 경우네는 AMT로 표시해준다.
--즉, 한 모델은 두개의 로우에 나오게 된다.
SELECT T1.ModelID,
T2.DATA_TP,
CASE WHEN T2.DATA_TP = 'QTY' THEN T1.Qty
WHEN T2.DATA_TP = 'AMT' THEN T1.Amount
END Value
FROM (
SELECT T1.ModelID, SUM(T1.Qty) Qty, SUM(T1.Amount) Amount
FROM Sales T1
WHERE T1.SaleYMD = '20060601'
GROUP BY T1.ModelID
) T1 CROSS JOIN
( SELECT 'QTY' DATA_TP
UNION ALL
SELECT 'AMT' DATA_TP
) T2
ORDER BY T1.ModelID, T2.DATA_TP
