賢爸知識分享站

介紹新增查詢資料的範例:

Use master

if exists( Select * from sys.databases where name=N'Inv')

   Drop Database Inv

======== 新增資料庫 Inv =======

Create Database Inv

Use Inv

Create Table Cust(

CustId int primary key,

CustName nvarchar(50),

Addr nvarchar(50),

Tel nvarchar(50))

======== 新增 Table Prdt Ord OrdDetail======== 

Create Table Prdt(

PrdtId int primary key,

PrdtName nvarchar(50),

Price int)

Create Table Ord(

OrdId int primary key,

CustId int,

OrdDate date)

Create Table OrdDetail(

OrdId int,

PrdtId int,

Qty int

Primary key(OrdId, PrdtId))

======== 新增資料 ======== 

Insert Into Cust(CustId, CustName, Addr, Tel)

Values(1, 'A-Comp', 'TP', '999-9999'),

(2, 'B-Comp', 'TP', '888-9999'),

(3, 'C-Comp', 'TC', '777-9999'),

(4, 'D-Comp', 'KS', '666-9999')

Insert Into Prdt(PrdtId, PrdtName, Price)

Values(1, 'CPU', 6000),

(2, 'RAM', 1200),

(3, 'Keyboard', 650),

(4, 'Mouse', 320)

Insert Into Ord(OrdId, CustId, OrdDate)

Values(1, 1, '2010-1-1'),

(2, 2, '2010-1-15'),

(3, 1, '2010-1-18'),

(4, 3, '2010-2-1'),

(5, 2, '2010-2-15'),

(6, 4, '2010-2-20')

Insert Into OrdDetail(OrdId, PrdtId, Qty)

Values(1, 1, 50),

(1, 2, 50),

(1, 4, 50),

(2, 1, 50),

(2, 4, 50),

(3, 1, 50),

(3, 2, 50),

(3, 4, 50),

(4, 2, 50),

(4, 3, 50),

(5, 2, 50),

(5, 3, 50),

(6, 4, 50)

======== 列出結果 ======== 

Select * From Cust

Select * From Prdt

Select * From Ord

Select * From OrdDetail

======== 列出訂單編號 OrdId = 1 的明細資料 =======

Select p.PrdtId, p.PrdtName,p.Price, od.Qty

From OrdDetail od

Inner Join Prdt p

   on od.PrdtId = p.PrdtId

Where od.OrdId = 1

======== 列出客戶編號 CustId = 1 購買紀錄 ========

Select o.OrdDate, p.PrdtId, p.PrdtName,od.Qty, p.Price

From Ord o

   inner join OrdDetail od on o.OrdId = od.OrdId

   inner join Prdt p on od.PrdtId = p.PrdtId

Where o.CustId = 1

Order By o.OrdDate Desc, p.Price Desc

======== 列出2010年月份各產品合計金額 ========

Select p.PrdtId, p.PrdtName, SUM(od.Qty*p.Price) as total

From Prdt p

   inner join OrdDetail od on p.PrdtId = od.PrdtId

   inner join Ord o on o.OrdId = od.OrdId

Where YEAR(o.OrdDate)=2010 And MONTH(o.OrdDate)=1

Group By p.PrdtId, p.PrdtName

Order By total desc

======== 列出2010年月份各客戶合計金額 ========

Select c.CustId, c.CustName, SUM(od.Qty*p.Price) as Total

From Cust c

inner join Ord o on c.CustId = o.CustId

inner join OrdDetail od on o.OrdId = od.OrdId

inner join Prdt p on od.PrdtId = p.PrdtId

where YEAR(o.OrdDate) = 2010 and MONTH(o.OrdDate) = 1

Group By c.CustId, c.CustName

Order By Total Desc