SQL高级查询

nullGrouping and Summarizing DataGrouping and Summarizing DataOverviewOverviewListing the TOP n Values Using Aggregate Functions GROUP BY FundamentalsListing the TOP n ValuesListing the TOP n ValuesLists Only the First n Rows of a Result Set Specifies the Range of Values in the ORDER BY Clause Returns Ties if WITH TIES Is UsedUsing Aggregate FunctionsUsing Aggregate FunctionsUsing Aggregate Functions with Null ValuesUsing Aggregate Functions with Null ValuesMost Aggregate Functions Ignore Null Values COUNT(*) Function Counts Rows with Null ValuesUSE northwind SELECT COUNT (*) FROM employees GOUSE northwind SELECT COUNT(reportsto) FROM employees GO Example 1Example 2 GROUP BY Fundamentals GROUP BY FundamentalsUsing the GROUP BY Clause Using the GROUP BY Clause with the HAVING ClauseUsing the GROUP BY ClauseUsing the GROUP BY ClauseUSE northwind SELECT productid, orderid ,quantity FROM orderhist GOUSE northwind SELECT productid ,SUM(quantity) AS total_quantity FROM orderhist GROUP BY productid GOOnly rows that satisfy the WHERE clause are groupedUSE northwind SELECT productid ,SUM(quantity) AS total_quantity FROM orderhist WHERE productid = 2 GROUP BY productid GOUsing the GROUP BY Clause with the HAVING ClauseUsing the GROUP BY Clause with the HAVING ClauseJoining Multiple TablesJoining Multiple TablesOverviewOverviewUsing Aliases for Table Names Combining Data from Multiple Tables Combining Multiple Result Sets Using Aliases for Table Names Using Aliases for Table NamesExample 1 (without an alias name) Example 2 (with an alias name)USE joindb SELECT buyer_name, s.buyer_id, qty FROM buyers AS b INNER JOIN sales AS s ON b.buyer_id = s.buyer_id GOUSE joindb SELECT buyer_name, sales.buyer_id, qty FROM buyers INNER JOIN sales ON buyers.buyer_id = sales.buyer_id GO Combining Data from Multiple Tables Combining Data from Multiple TablesIntroduction to Joins Using Inner Joins Using Outer Joins Using Cross Joins Joining More Than Two Tables Joining a Table to ItselfIntroduction to JoinsIntroduction to JoinsSelects Specific Columns from Multiple Tables JOIN keyword specifies that tables are joined and how to join them ON keyword specifies join condition Queries Two or More Tables to Produce a Result Set Use primary and foreign keys as join conditions Use columns common to specified tables to join tablesUsing Inner JoinsUSE joindb SELECT buyer_name, sales.buyer_id, qty FROM buyers INNER JOIN sales ON buyers.buyer_id = sales.buyer_id GOUsing Inner JoinsExample 1Using Outer JoinsUSE joindb SELECT buyer_name, sales.buyer_id, qty FROM buyers LEFT OUTER JOIN sales ON buyers.buyer_id = sales.buyer_id GOUsing Outer JoinsExample 1Using Cross JoinsUsing Cross JoinsExample 1Joining More Than Two TablesJoining More Than Two TablesSELECT buyer_name, prod_name, qty FROM buyers INNER JOIN sales ON buyers.buyer_id = sales.buyer_id INNER JOIN produce ON sales.prod_id = produce.prod_id GOExample 1Joining a Table to ItselfJoining a Table to ItselfExample 3Combining Multiple Result SetsCombining Multiple Result SetsUse the UNION Operator to Create a Single Result Set from Multiple Queries Each Query Must Have: Similar data types Same number of columns Same column order in select listUSE northwind SELECT (firstname + ' ' + lastname) AS name ,city, postalcode FROM employees UNION SELECT companyname, city, postalcode FROM customers GOCreating a Table from a Result SetCreating a Table from a Result SetUsing the SELECT INTO Statement Requires unique table name You can create a local or global temporary table Set the select into/bulkcopy database option on in order to create a permanent tableUSE northwind SELECT productname AS products, unitprice AS price, (unitprice * 1.1) AS tax INTO #pricetable FROM productsWorking with SubqueriesWorking with SubqueriesOverviewOverviewIntroduction to Subqueries Using a Subquery as a Derived Table Using a Subquery as an Expression Using a Subquery to Correlate Data Using the EXISTS and NOT EXISTS Clauses Introduction to Subqueries Introduction to SubqueriesWhy to Use Subqueries To break down a complex query into a series of logical steps To answer a query that relies on the results of an other query Why to Use Joins Rather Than Subqueries SQL Server executes joins faster than subqueries How to Use SubqueriesUsing a Subquery as a Derived TableUsing a Subquery as a Derived TableIs a Recordset Within a Query That Functions as a Table Takes the Place of a Table in the FROM Clause Is Optimized with the Rest of the QueryUSE northwind SELECT T.orderid, T.customerid FROM ( SELECT orderid, customerid FROM orders ) AS T GOUsing a Subquery as an ExpressionUsing a Subquery as an ExpressionIs Evaluated and Treated as an Expression Is Executed Once for the QueryUSE pubs SELECT title, price ,( SELECT AVG(price) FROM titles) AS average ,price-(SELECT AVG(price) FROM titles) AS difference FROM titles WHERE type='popular_comp' GOUsing a Subquery to Correlate DataUsing a Subquery to Correlate DataEvaluating a Correlated Subquery Mimicking a JOIN Clause Mimicking a HAVING ClauseEvaluating a Correlated SubqueryEvaluating a Correlated SubqueryBack to Step 1USE northwind SELECT orderid, customerid FROM orders AS or1 WHERE 20 < (SELECT quantity FROM [order details] AS od WHERE or1.orderid = od.orderid AND od.productid = 23) GOExample 1Using the EXISTS and NOT EXISTS ClausesUsing the EXISTS and NOT EXISTS ClausesUse with Correlated Subqueries Determine Whether Data Exists in a List of Values SQL Server Process Outer query tests for the existence of rows Inner query returns TRUE or FALSE No data is producedUSE northwind SELECT lastname, employeeid FROM employees AS e WHERE EXISTS (SELECT * FROM orders AS o WHERE e.employeeid = o.employeeid AND o.orderdate = '9/5/97') GOExample 1