Transact SQL 101 - Lesson 11_01_SQL.txt

(0 KB) Pobierz

--Example 1
USE adventureworkslt
GO

WITH address_cte (CustID, AddID, Address1, City, State, Zip)
AS
(
	SELECT ca.customerid, a.addressid, a.addressline1, a.city, a.stateprovince, a.postalcode
	FROM saleslt.address a
		INNER JOIN saleslt.customeraddress ca ON a.addressid = ca.addressid
	WHERE a.countryregion = 'United States'
)

SELECT c.companyname, a.CustID, a.AddID, a.address1, a.City, a.State, a.Zip
FROM saleslt.customer c
	INNER JOIN address_cte a ON c.customerid = a.CustID
ORDER BY a.custid

--Example 2
WITH rank_cte (Ranking, CustID, Total)
AS
(
	SELECT RANK() OVER (ORDER BY totaldue DESC), CustomerID, TotalDue
	FROM saleslt.salesorderheader
)
SELECT r.ranking, r.custid, r.total, c.companyname
FROM rank_cte r
	INNER JOIN saleslt.customer c ON r.CustID = c.customerid 
WHERE r.Ranking BETWEEN 1 AND 10
ORDER BY Ranking

Transact SQL 101 - Lesson 11_01_SQL.txt

Plik z chomika:

Inne pliki z tego folderu:

Inne foldery tego chomika: