Sum

-------
Select OrderID,ProductID,UnitPrice*Quantity*(1-Discount) as '小計'
from dbo.[Order Details]

-------
Select Convert(int,Sum(UnitPrice*Quantity*(1-Discount))) as '訂單總金額'
from dbo.[Order Details]

----
Select YEAR(OrderDate) as '年度' ,
Convert(int,Sum(UnitPrice*Quantity*(1-Discount))) as '訂單總金額'
from dbo.Orders O join dbo.[Order Details] D on O.OrderID=D.OrderID
GROUP BY YEAR(OrderDate)

------
Select YEAR(OrderDate) as '年度' ,Datepart(Quarter,OrderDate) as '季' ,
Convert(int,Sum(UnitPrice*Quantity*(1-Discount))) as '訂單總金額' ,
Count(distinct O.OrderID) as '訂單總筆數'
from dbo.Orders O join dbo.[Order Details] D on O.OrderID=D.OrderID
GROUP BY YEAR(OrderDate),Datepart(Quarter,OrderDate)
Order BY YEAR(OrderDate),Datepart(Quarter,OrderDate)

Count

use Northwind;

-----------------
Select*
from dbo.Orders

-----
Select Count(*) as '訂單總筆數'
from dbo.Orders

----
Select Count(ShippedDate) as '已出貨總筆數'
from dbo.Orders

Select YEAR(OrderDate) as '年度',Count(*) as '訂單總筆數'
from dbo.Orders
Group by YEAR(OrderDate)

------
Select YEAR(OrderDate) as '年度',Datepart(Quarter,OrderDate) as '季',Count(*) as '訂單總筆數'
from dbo.Orders
GROUP BY YEAR(OrderDate),Datepart(Quarter,OrderDate)
Order BY YEAR(OrderDate),Datepart(Quarter,OrderDate)

---return bigint(ver.2005)
Select Count_BIG(*) as '訂單總筆數'
from dbo.Orders

Except

--1997/1有下訂單但1997/2月無訂單的客戶
Select distinct CustomerID
from dbo.Orders
where year(OrderDate)=1997 and month(OrderDate)=1
except
Select distinct CustomerID
from dbo.Orders
where year(OrderDate)=1997 and month(OrderDate)=2

Intersect

/*
只包括 TABLE1 和 TABLE2 中都有的行並消除所有重複行而派生出一個結果表。當 ALL 隨 INTERSECT 一起使用時 (INTERSECT ALL)，不消除重複行。
*/

--1997/1及1997/2月均有下訂單的客戶
Select distinct CustomerID
from dbo.Orders
where year(OrderDate)=1997 and month(OrderDate)=1
intersect
Select distinct CustomerID
from dbo.Orders
where year(OrderDate)=1997 and month(OrderDate)=2

Union(多個結果集合併)

/*
Union ---不包含重複(Distinct)
Union All ---全部
1.各結果集欄位數須相同
2.同一欄位型別需相同
*/

Select Convert(char(9),EmployeeID) as 'EmployeeID',LastName,HireDate
from Northwind.dbo.Employees
union
select emp_id,lname,hire_date
From pubs.dbo.employee

--列出產品目錄'1'中庫存量不足或安全存量不足的產品
Select ProductID,ProductName,CategoryID,UnitsInStock,UnitsOnOrder,ReorderLevel
from dbo.Products
where CategoryID=1 and (UnitsInStock＜UnitsOnOrder or UnitsInStock＜ReorderLevel )
----Union
Select ProductID,ProductName,CategoryID,UnitsInStock,UnitsOnOrder,ReorderLevel
from dbo.Products
where CategoryID=1 and UnitsInStock＜UnitsOnOrder
union
Select ProductID,ProductName,CategoryID,UnitsInStock,UnitsOnOrder,ReorderLevel
from dbo.Products
where CategoryID=1 and UnitsInStock＜ReorderLevel
---Union All
Select distinct Country
from dbo.Employees
union all ---(可允許重複)
Select distinct Country
from dbo.Customers

Cross Join(交叉聯結)

/*
Cross Join(交叉聯結):將產生聯結所牽涉到的資料表的笛卡兒乘積。
*/

Use Northwind;

Select C.CompanyName as '客戶', S.CompanyName as '貨運'
from dbo.Customers C cross join dbo.Shippers S

Self Join

/*
自我TABLE JOIN 可用於:
1.Parent-Child Relationship
2.自我比對
*/

---使用北風資料庫
use Northwind

---1.Parent-Child Relationship
------列出全部員工ID及其主管ID
Select EmployeeID,ReportsTo
From dbo.Employees
------列出全部員工姓名及其主管姓名
Select A.LastName as '員工' ,B.LastName as '主管'
From dbo.Employees A left join dbo.Employees B on A.ReportsTo=B.EmployeeID
------沒有主管的就是'BOSS'
Select A.LastName as '員工' ,IsNull(B.LastName,'BOSS') as '主管'
From dbo.Employees A left join dbo.Employees B on A.ReportsTo=B.EmployeeID
------使用Case When 表示'BOSS'
Select A.LastName as '員工' ,
case when B.LastName is null then 'Boss'
else B.LastName
end as '主管'
From dbo.Employees A left join dbo.Employees B ---假設A是員工,B是主管
on A.ReportsTo=B.EmployeeID ---假設條件成立(員工的主管編號=主管的員工編號)

---2.自我比對
------相同年資的員工年齡比對(年紀大在前,年紀小在後)
Select A.LastName,A.BirthDate,Datediff(month,A.HireDate,getdate())/12,B.LastName,B.BirthDate
from dbo.Employees A join dbo.Employees B ---假設A是年紀大,B是年紀小
on A.BirthDate＜B.BirthDate and Datediff(month,A.HireDate,getdate())/12=Datediff(month,B.HireDate,getdate())/12
------訂單中買相同產品,不同售價比對(大在前)
Select A.OrderID,A.ProductID,A.UnitPrice,B.OrderID,B.UnitPrice
from dbo.[Order Details] A join dbo.[Order Details] B
on A.UnitPrice>B.UnitPrice and A.ProductID=B.ProductID
-----訂單中同一客戶買相同產品,不同售價比對(大在前)
With Allorders ---CTE
as( Select O.OrderID,CustomerID,OrderDate,ProductID,UnitPrice,Quantity
From dbo.Orders O join dbo.[Order Details] D on O.OrderID=D.OrderID
)
Select A.OrderID,A.OrderDate,A.CustomerID,A.ProductID,A.UnitPrice,B.OrderID,B.OrderDate,B.UnitPrice
from Allorders A join Allorders B
on A.UnitPrice>B.UnitPrice and A.CustomerID=B.CustomerID and A.ProductID=B.ProductID

Outer Join

/*
使用Outer JOIN(外部連結)

CTE(Common Table Expression):暫存的檢視表，生命週期為只存在當下的Context中。
WITH (欄位名稱)
AS(
<查詢語法>
)
SELECT <欄位名稱>
FROM
*/

---使用北風資料庫
use Northwind

--列出從來沒下過訂單的客戶
--SubQuery
Select CustomerID,CompanyName
from dbo.Customers
where CustomerID not in ( Select CustomerID From dbo.Orders)
--Outer Join
Select C.CustomerID,CompanyName,OrderDate
from dbo.Customers C left join dbo.Orders O on C.CustomerID=O.CustomerID
where O.OrderDate is null

--列出1997年一月沒下訂單的客戶
--SubQuery
Select CustomerID,CompanyName
from dbo.Customers
where CustomerID not in (
Select CustomerID
From dbo.Orders
where Year(OrderDate)=1997 and Month(OrderDate)=1
)
--Outer Join
Select C.CustomerID,CompanyName
from dbo.Customers C left join dbo.Orders O
on C.CustomerID=O.CustomerID and Year(OrderDate)=1997 and Month(OrderDate)=1
where O.OrderDate is null

----列出1997年一月沒有訂單的產品
--SubQuery
select ProductName
from dbo.Products
where ProductID not in(
Select ProductID
from dbo.Orders O join dbo.[Order Details] D
on O.OrderID=D.OrderID and Year(OrderDate)=1997 and Month(OrderDate)=1
)
--Outer Join
Select ProductName
From dbo.Orders O join dbo.[Order Details] D
on O.OrderID=D.OrderID and Year(OrderDate)=1997 and Month(OrderDate)=1
right join dbo.Products P
on P.ProductID=D.ProductID
where OrderDate is null

----沒有買過已停產產品的客戶
select CompanyName
from dbo.Orders O join dbo.[Order Details] D
on O.OrderID=D.OrderID
join dbo.Products P
on P.ProductID=D.ProductID and Discontinued=1
right join dbo.Customers C
on C.CustomerID=O.CustomerID
where OrderDate is null
---使用TEMP TABLE
Select O.*
into #包含已停產產品訂單
from dbo.Orders O join dbo.[Order Details] D
on O.OrderID=D.OrderID
join dbo.Products P
on P.ProductID=D.ProductID and Discontinued=1
select CompanyName
from #包含已停產產品訂單 O right join dbo.Customers C
on C.CustomerID=O.CustomerID
where OrderDate is null
--使用CTE
with 包含已停產產品訂單
as(Select O.*
from dbo.Orders O join dbo.[Order Details] D on O.OrderID=D.OrderID
join dbo.Products P on P.ProductID=D.ProductID and Discontinued=1
)
select CompanyName
from 包含已停產產品訂單 O right join dbo.Customers C on C.CustomerID=O.CustomerID
where OrderDate is null

---列出沒買美國產品的美國客戶
select C.CompanyName
from dbo.Orders O join dbo.[Order Details] D on O.OrderID=D.OrderID
join dbo.Products P on P.ProductID=D.ProductID
join dbo.Suppliers S on S.SupplierID=P.SupplierID and S.Country='USA'
right join dbo.Customers C on C.CustomerID=O.CustomerID
where OrderDate is null and C.Country='USA'

Inner Join

---使用北風資料庫
use Northwind

/*
使用INNER JOIN
*/

---客戶訂單
Select CustomerID,CompanyName, ---From dbo.Customers
OrderDate,RequiredDate,ShippedDate ---From dbo.Orders
from dbo.Customers inner join dbo.Orders
on CustomerID=CustomerID ---A.PK=B.FK

-設TABLE別名(AS)
Select C.CustomerID,CompanyName, ---From dbo.Customers
OrderDate,RequiredDate,ShippedDate ---From dbo.Orders
from dbo.Customers as C inner join dbo.Orders as O
on C.CustomerID=O.CustomerID

-----省略 AS , INNER
Select C.CustomerID,CompanyName, ---From dbo.Customers
OrderDate,RequiredDate,ShippedDate ---From dbo.Orders
from dbo.Customers C join dbo.Orders O
on C.CustomerID=O.CustomerID
------------------------------------------------------------------------------
Select O.OrderID,OrderDate, ---From dbo.Orders
P.ProductID,ProductName,P.UnitPrice as '訂價', ---From dbo.Products
D.UnitPrice as '售價',Quantity,Discount ---From dbo.Order Details
from dbo.Orders O join dbo.[Order Details] D on O.OrderID=D.OrderID
join dbo.Products P on P.ProductID=D.ProductID

--找出售價低於目前訂價訂單及其明細
Select O.OrderID,OrderDate, ---From dbo.Orders
P.ProductID,ProductName,P.UnitPrice as '訂價', ---From dbo.Products
D.UnitPrice as '售價',Quantity,Discount ---From dbo.Order Details
From dbo.Orders O join dbo.[Order Details] D on O.OrderID=D.OrderID
join dbo.Products P on P.ProductID=D.ProductID
where P.UnitPrice>D.UnitPrice
-------------------------------------------------------------------------
Select O.OrderID,OrderDate, ---From dbo.Orders
P.ProductID,ProductName,P.UnitPrice as '訂價', ---From dbo.Products
D.UnitPrice as '售價',Quantity,Discount ---From dbo.Order Details
From dbo.Orders O join dbo.[Order Details] D on O.OrderID=D.OrderID
join dbo.Products P on P.ProductID=D.ProductID
where P.UnitPrice>D.UnitPriceOrder by 訂價 desc--只有Order by 可用欄位別名，Where不可用欄位別名
-------------------
Select O.OrderID,OrderDate, ---From dbo.Orders
C.CustomerID,CompanyName, ---From dbo.Customers
E.EmployeeID,FirstName+' '+LastName as '員工', ---from dbo.Employees
P.ProductID,ProductName,P.UnitPrice as '訂價', ---From dbo.Products
D.UnitPrice as '售價',Quantity,Discount, ---From dbo.Order Details
D.UnitPrice*Quantity*(1-Discount) as '小計'
from dbo.Orders O join dbo.[Order Details] D on O.OrderID=D.OrderID
join dbo.Products P on P.ProductID=D.ProductID
join dbo.Customers C on C.CustomerID=O.CustomerID
join dbo.Employees E on E.EmployeeID=O.EmployeeID

數學運算式或搭配系統函式

Use Northwind

/*
使用運算式
*/

--計算每個訂單明細的小計
Select OrderID,ProductID,UnitPrice*Quantity*(1-Discount) as N'小計'
From dbo.[Order Details]

/*數學運算子*/
select 10/3 as '商數',10%3 AS '餘數'

/*Date and time Function*/
--1.Getdate(),GetUTCDate() --format for datetime values
select Getdate() as '現在時間'
,GetUTCDate() as '標準時間'

--2.Year()/Month()/Day() --Returns an integer
Select Year(GetDate())-1911,
Month(Getdate()),
Day(Getdate())
--- Convert to varchar
Select Convert(Nvarchar,Year(GetDate())-1911)+N'年'+
Convert(Nvarchar,Month(Getdate()))+N'月'+
Convert(Nvarchar,Day(Getdate()))+N'日' as N'民國'
--- style
select Getdate() as '現在時間',
Convert(varchar,getdate(),111) as 'JAPAN',
Convert(varchar,getdate(),101) as 'US'

--3.Datename()/Datepart()
--Datepart() Returns an integer
--Datename() Returns a character string
Select Year(Getdate()),DatePart(year,getdate()),DateName(year,getdate())
Select Datepart(Month,Getdate()),
Datepart(WeekDay,Getdate()),
DateName(Month,Getdate()),
DateName(WeekDay,Getdate())
--列出語系
select * from sys.syslanguages
--列出本連線使用語系
select @@language
--設定連線使用語系
set language N'繁體中文' --name
Select DateName(Month,Getdate()),
DateName(WeekDay,Getdate())
--set language N'Français'
select Month('10/11/12'),
Convert(datetime,'10/11/12')
set DateFormat 'ymd'
select Month('10/11/12'),
Convert(datetime,'10/11/12')
--set language N'us_english'
Select Datepart(WeekDay,Getdate())
set Datefirst 1--更改本連線設定值:星期第一天為星期一(預設為7星期日)
Select Datepart(WeekDay,Getdate())

--4.DateAdd()/DateDiff()
--Returns a new datetime value
--保存期限二季
select getdate() as '製造日期',
DateAdd(Quarter,2,Getdate()) as '保存期限'
--
Select Getdate(),Datediff(hour, getdate(),'2009/1/1 12:00:00 AM')
--
Select EmployeeID,LastName,BirthDate,HireDate,
datediff(year,BirthDate,getdate()) as 'Age',
Convert(varchar,datediff(month,HireDate,getdate())/12)+'年'+
Convert(varchar,datediff(month,HireDate,getdate())%12)+'月' as '年資'
from dbo.Employees

參考網站:
http://technet.microsoft.com/zh-tw/library/ms187928.aspx

Distinct

Use Northwind

/*
使用Distinct
*/

--列出客戶所在國家
select distinct Country
from dbo.Customers
--Distinct Country已內含ORDER BY Country asc動作

Order By

---使用北風資料庫
Use Northwind

/*
使用Order By
*/

--列出北美地區客戶
Select *
from dbo.Customers
where Country in ('Canada','USA','Mexico')
order by Country ---ASC--預設(升冪)

----列出北美地區客戶
Select *
from dbo.Customers
where Country in ('Canada','USA','Mexico')
Order by Country desc--(降冪)

---列出客戶指定排序(1.USA 2.UK 3.Germany 4.France 5.其他用升冪)
Select *
from dbo.Customers
order by case when Country='USA' then 1 when Country='UK' then 2 when Country='Germany' then 3 when Country='France' then 4 else 5 end,Country asc

AS

Use Northwind

/*
使用as
*/

select ProductID ,
ProductName as '產品名稱',
UnitsInStock '庫存量', --as 可省略
'訂購數量'=UnitsOnOrder,
'安全存量'=ReorderLevel,
'停產'=Discontinued
from dbo.Products;

NULL

Use Northwind

/*
Is Null /Is Not Null
*/

--列出尚未出貨訂單
select *
from dbo.Orders
where ShippedDate is null

--列出已出貨訂單
select *
from dbo.Orders
where ShippedDate is not null

In

---使用北風資料庫
Use Northwind

/*
In /Not In
*/

--列出北美地區客戶
Select *
from dbo.Customers
where Country='Canada' or Country='USA' or Country='Mexico'

--IN
Select *
from dbo.Customers
where Country in('Canada','USA','Mexico')

--列出非北美地區客戶
Select *
from dbo.Customers
where Country<>'Canada' and Country<>'USA' and Country<>'Mexico'

--Not IN
Select *
from dbo.Customers
where Country not in('Canada','USA','Mexico')

Between ... and ...

---使用北風資料庫
Use Northwind

/*
Between ... And ...
*/

--列出訂價10~30之間的產品
Select *
from dbo.Products
where UnitPrice<=30 and UnitPrice>=10
-- 或
Select *
from dbo.Products
where UnitPrice between 10 and 30

--列出訂價不在10~30之間的產品
Select *
from dbo.Products
where UnitPrice>30 or UnitPrice<10
-- 或
Select *
from dbo.Products
where UnitPrice not between 10 and 30

邏輯運算子

--- 使用北風資料庫
Use Northwind

/*
And /Or /Not
*/

--列出產品目錄'1'中庫存量不足或所有類別中安全存量不足的產品
Select ProductID,ProductName,CategoryID,UnitsInStock,UnitsOnOrder,ReorderLevel
from dbo.Products
where CategoryID=1 and UnitsInStock ＜UnitsOnOrder
or UnitsInStock＜ReorderLevel

--列出產品目錄'1'中-->庫存量不足或安全存量不足的產品
Select ProductID,ProductName,CategoryID,UnitsInStock,UnitsOnOrder,ReorderLevel
from dbo.Products
where CategoryID=1
and (UnitsInStock＜UnitsOnOrder or UnitsInStock＜ReorderLevel )

Like

---使用北風資料庫
Use Northwind

/*
Like '%_[][^]'
*/

--列出'A'開頭客戶
select *
from dbo.Customers
where CompanyName like 'A%'

--列出'S'結尾客戶
select *
from dbo.Customers
where CompanyName like '%s'

--列出客戶名稱有含'B'的客戶
select *
from dbo.Customers
where CompanyName like '%B%'

--列出電話倒數第二個字是'2'的客戶
select *
from dbo.Customers
where Phone like '%2_'

--列出電話第二個字是'2',倒數第二個字是'2'的客戶
select *
from dbo.Customers
where Phone like '_2%2_'

--列出'A' 或'B'或'C'開頭客戶
select *
from dbo.Customers
where CompanyName like 'A%' or CompanyName like 'B%' or CompanyName like 'c%'
-- or
select *
from dbo.Customers
where CompanyName like '[ABC]%'
-- or
select *
from dbo.Customers
where CompanyName like '[A-C]%'

--列出Not 'A' 且Not 'B'且Not 'C'開頭客戶
select *
from dbo.Customers
where CompanyName not like 'A%' and CompanyName not like 'B%' and CompanyName not like 'c%'
-- or
select *
from dbo.Customers
where CompanyName like '[^ABC]%'
-- or
select *
from dbo.Customers
where CompanyName like '[^A-C]%'

-------------------------test
update dbo.Products
set QuantityPerUnit='70%'
where ProductID=1

--Select *
from dbo.Products
where ProductID=1

--列出有%產品
Select *
from dbo.Products
where QuantityPerUnit like '%#%%' escape '#'

比較運算子

---使用北風資料庫
Use Northwind

/*
>, < ,= ,>= ,<= ,<>
*/

--列出延遲出貨訂單
SELECT OrderID,OrderDate,ShippedDate,RequiredDate
From dbo.Orders
where ShippedDate>RequiredDate

--列出非美國地區客戶
Select CustomerID,CompanyName,City,Country
From dbo.Customers
where Country<>'USA'

--列出庫存量不足的產品
Select ProductID,ProductName,UnitPrice,UnitsOnOrder,UnitsInStock
from dbo.Products
where UnitsOnOrder>UnitsInStock

--列出安全存量不足的產品Select ProductID,ProductName,UnitPrice,UnitsOnOrder,ReorderLevel
from dbo.Products
where UnitsOnOrder>ReorderLevel

Select

---使用北風資料庫
use Northwind


/*
Select Column1,Column2,... (垂直篩選)
From Table
Where Row條件式 (水平篩選)
*/


--------All
select *
from dbo.Customers


--列出美國客戶
select CustomerID,CompanyName,City,Country --垂直篩選
from dbo.Customers
where Country='USA' --水平篩選


---------All
Select *
from dbo.Employees


--列出1960年後出生的員工
Select LastName,FirstName,BirthDate,HireDate
from dbo.Employees
Where BirthDate>='1960/1/1'


--------使用YEAR(日期格式)
Select LastName,FirstName,BirthDate,HireDate
from dbo.Employees
Where Year(BirthDate)>=1960

------All
Select *
from dbo.Products

--列出單位售價>=100元以上的產品
Select ProductID,ProductName,UnitPrice
from dbo.Products
where UnitPrice>=100