1.建一張客戶表，包含客戶名稱，聯系人，電話號碼，公司地址.

2.建一張訂單表，包含訂單編號，客戶ID,供應商ID,訂單金額，客戶支付狀態，供應商支付狀態,訂單日期.

3.建一張供應商表，包含供應商名稱，聯系人，電話號，供應商地址

View Code 1 create table customer 2 ( 3 customerID int primary key identity(1,1) , 4 customerName varchar(50), 5 cuslinkMan varchar(50), 6 customerPhone varchar(12), 7 companyAdress varchar(100) 8 ) 9 10 create table supplier 11 ( 12 supplierID INT primary key identity(1,1) , 13 supplierName varchar(50), 14 supLinkeMan varchar(50), 15 supAdress varchar(100) 16 ) 17 18 create table orderForm 19 ( 20 orderID int identity(1,1), 21 orderNum varchar(50), 22 customerID int, 23 supplierID int, 24 orderMoney decimal , 25 custState int, 26 orderState int , 27 orderTime datetime, 28 constraint pk_order primary key(orderID) , 29 constraint fk_supplier foreign key(supplierID) references supplier(supplierID), 30 constraint fk_customer foreign key(customerID) references customer(customerID) 31 ) 32 33 insert into customer(customerName,cuslinkMan,customerPhone,companyAdress) values('太陽','劉敏','18762676815','無錫西區') 34 35 insert into dbo.supplier(supplierName,supLinkeMan,supAdress) 36 values('安泰','李丹','大連') 37 38 39 insert into dbo.orderForm(orderNum,customerID,supplierID,orderMoney,custState,orderState,orderTime) 40 values('0001','1','1','10000','0','0','2012.12.21')

練習一：寫存儲過程，根據訂單日期，客戶，供應商來查詢訂單，包含客戶，供應商名稱，訂單詳細信息，訂單日期可以為空，客戶跟供應商可以全選.

View Code 1 SET ANSI_NULLS ON 2 GO 3 SET QUOTED_IDENTIFIER ON 4 GO 5 6 create PROCEDURE 訂單查詢 7 8 @訂單日期 datetime, 9 @客戶 varchar(50), 10 @供應商 varchar(50) 11 AS 12 BEGIN 13 SET NOCOUNT ON; 14 15 16 SELECT orderNum, C.customerName,S.supplierName ,orderMoney,custState ,orderState ,orderTime 17 from dbo.orderForm O,dbo.customer C,dbo.supplier S 18 where 19 C.customerID=O.customerID 20 and S.supplierID=O.supplierID 21 --判斷O.orderTime 22 and 23 ( 24 25 @訂單日期 ='' 26 or @訂單日期 is null or O.orderTime = @訂單日期 27 ) 28 and --判斷客戶 29 ( 30 @客戶='-1' or C.customerName=@客戶 31 ) 32 and--判斷供應商 33 ( 34 @供應商='-1' or S.supplierName=@供應商 35 ) 36 37 END 38 GO 39 40 EXEC 訂單查詢 '2012-12-21','星星','上海'

練習二：寫存儲過程，查詢訂單數最多的前3個客戶的訂單信息。

方法一：
ALTER PROCEDURE [dbo].[訂單總數前三]
?? ?
AS
BEGIN
?? ??? ?
?? ?SELECT O.*
?? ?from dbo.orderForm O
?? ?where ?? ?
?? ? O.customerID in
?? ?(
?? ?SELECT top 3? O.customerID -- ,COUNT(O.orderNum) as 'aaa'
?? ?from dbo.orderForm O
?? ?group by O.customerID
?? ?order by COUNT(O.orderNum) DESC
?? ?)
END 方法二：
CREATE PROCEDURE 訂單前三
AS
BEGIN?? ?
? select O.* from orderForm? O
join
(
select
?? top 3? customerID
from(
?? select customerID ,COUNT(orderNum) AS EE FROM dbo.orderForm GROUP BY customerID
) as TT order by EE desc
)as KK
on O.customerID = KK.customerID ?
END
GO

練習三：寫存儲過程，查詢訂單金額最多的前3個客戶的訂單信息。

?

View Code USE [test] GO /****** Object: StoredProcedure [dbo].[訂單金額前三] Script Date: 12/26/2012 09:30:19 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GOALTER PROCEDURE [dbo].[訂單金額前三]AS BEGINSET NOCOUNT ON;-- Insert statements for procedure hereSELECT O.orderID, C.customerID ,customerName,S.supplierName ,orderMoney,custState ,orderState ,orderTime from dbo.orderForm O,dbo.customer C,dbo.supplier S where C.customerID=O.customerID and S.supplierID=O.supplierID and O.customerID in (SELECT top 3 O.customerID --, sum(O.orderMoney)AS 訂單總金額 from dbo.orderForm O group by O.customerIDorder by sum(O.orderMoney) desc) END

?

?

?

練習四：寫sql，刪除3條供應商數據，并且相應訂單的供應商ID也置為null（這里應該是更新操作）

View Code SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GOCREATE PROCEDURE 刪除供應商信息 -- Add the parameters for the stored procedure here@供應商1ID int,@供應商2ID int,@供應商3ID int AS BEGIN-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.SET NOCOUNT ON;-- Insert statements for procedure hereupdate dbo.orderFormset supplierID=null where supplierID=@供應商1IDor supplierID=@供應商2IDor supplierID=@供應商3IDdelete from dbo.supplier where supplierID=@供應商1IDor supplierID=@供應商2IDor supplierID=@供應商3IDEND GOexec 刪除供應商信息 '2','3','4'

練習五：查詢不存在供應ID的訂單信息，用(not exits)

CREATE PROCEDURE 查詢無供應商的訂單信息AS BEGINSET NOCOUNT ON;-- Insert statements for procedure hereSELECT * from orderForm A where not EXISTS (select supplierID from dbo.supplier B where B.supplierID=A.supplierID) END GOexec 查詢無供應商的訂單信息

?

轉載于:https://www.cnblogs.com/hucui/archive/2012/12/25/123hucuis.html

總結

以上是生活随笔為你收集整理的SQL排序查询的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。