SQL排序查询的例子

发布时间:2019-07-25编辑:脚本学堂
SQL排序查询的例子,有需要的朋友可以看看。

SQL排序查询的例子,有需要的朋友可以看看。

1.建一张客户表,包含客户名称,联系人,电话号码,公司地址.
2.建一张订单表,包含订单编号,客户ID,供应商ID,订单金额,客户支付状态,供应商支付状态,订单日期.
3.建一张供应商表,包含供应商名称,联系人,电话号,供应商地址
 

复制代码 代码如下:

create table customer
(
 customerID int primary key identity(1,1) ,
 customerName varchar(50),
 cuslinkMan varchar(50),
 customerPhone varchar(12),
 companyAdress varchar(100)
 )
 
create table supplier
(
supplierID INT primary key identity(1,1) ,
supplierName varchar(50),
supLinkeMan varchar(50),
supAdress varchar(100)
)
 
create table orderForm
(
 orderID int identity(1,1),
 orderNum varchar(50),
 customerID int,
 supplierID int,
 orderMoney decimal ,
 custState int,
 orderState int ,
 orderTime datetime,
 constraint pk_order primary key(orderID) ,
 constraint fk_supplier foreign key(supplierID) references supplier(supplierID),
 constraint fk_customer foreign key(customerID) references customer(customerID)
 )
 
insert into customer(customerName,cuslinkMan,customerPhone,companyAdress) values('太阳','刘敏','18762676815','无锡西区')
 
insert into dbo.supplier(supplierName,supLinkeMan,supAdress)
values('安泰','李丹','大连')

insert into dbo.orderForm(orderNum,customerID,supplierID,orderMoney,custState,orderState,orderTime)
values('0001','1','1','10000','0','0','2012.12.21')

练习一:写存储过程,根据订单日期,客户,供应商来查询订单,包含客户,供应商名称,订单详细信息,订单日期可以为空,客户跟供应商可以全选.
 

复制代码 代码如下:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

create  PROCEDURE 订单查询

    @订单日期 datetime,
    @客户  varchar(50),
    @供应商  varchar(50)
AS
BEGIN
    SET NOCOUNT ON;
 
    SELECT orderNum, C.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
     --判断O.orderTime
     and
     (
    
     @订单日期 =''
     or @订单日期 is null or O.orderTime = @订单日期
     )
     and --判断客户
     (
        @客户='-1' or C.customerName=@客户    
     )
     and--判断供应商
     (
        @供应商='-1' or S.supplierName=@供应商
     )   
   
END
GO

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个客户的订单信息。
 

复制代码 代码如下:

USE [test]
GO
/****** Object:  StoredProcedure [dbo].[订单金额前三]    Script Date: 12/26/2012 09:30:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[订单金额前三]
   
   
AS
BEGIN
   
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    SELECT 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.customerID
    order by sum(O.orderMoney) desc
    )
END

练习四:写sql,删除3条供应商数据,并且相应订单的供应商ID也置为null(这里应该是更新操作)
 

复制代码 代码如下:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE 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 here
    update dbo.orderForm
    set supplierID=null
    where supplierID=@供应商1ID
      or supplierID=@供应商2ID
      or supplierID=@供应商3ID
     
      delete from dbo.supplier 
      where supplierID=@供应商1ID
      or supplierID=@供应商2ID
      or supplierID=@供应商3ID
END
GO

exec 删除供应商信息 '2','3','4'

练习五:查询不存在供应ID的订单信息,用(not exits)
 

复制代码 代码如下:

CREATE PROCEDURE  查询无供应商的订单信息
   
AS
BEGIN

    SET NOCOUNT ON;

    -- Insert statements for procedure here
    SELECT * from orderForm A where  not EXISTS
    (
    select supplierID from dbo.supplier B where B.supplierID=A.supplierID
    )
END
GO

exec 查询无供应商的订单信息