SQL流程控制语句

(1)判断公司订购产品的总数量，
如果大于1000,显示‘一级客户’及该公司名称和订购总数
如果大于500，显示‘二级客户’及该公司名称和订购总数
如果小于500，显示‘三级客户’及该公司名称和订购总数。
一种:if...........else
二种:case

use northwind
go
alter proc aaa
@name nvarchar(40)
as
if(select count(quantity)
    from [Order Details]
    join Orders
    on [Order Details].orderid=orders.orderid
    join Customers
    on Customers.CustomerID=Orders.CustomerID
    where Customers.CompanyName=@name
    group by Customers.CompanyName)>1000
begin
    select '一级客户'
    select count(quantity) 订购总量
    from [Order Details]
    join Orders
    on [Order Details].orderid=orders.orderid
    join Customers
    on Customers.CustomerID=Orders.CustomerID
    where Customers.CompanyName=@name
    group by Customers.CompanyName
end
else
    if (select count(quantity)
    from [Order Details]
    join Orders
    on [Order Details].orderid=orders.orderid
    join Customers
    on Customers.CustomerID=Orders.CustomerID
    where Customers.CompanyName=@name
    group by Customers.CompanyName)>500
begin
    select '二级客户'
    select count(quantity)  订购总量
    from [Order Details]
    join Orders
    on [Order Details].orderid=orders.orderid
    join Customers
    on Customers.CustomerID=Orders.CustomerID
    where Customers.CompanyName=@name
    group by Customers.CompanyName
end
else
begin
    select '三级客户' as 客户类型
    select count(quantity) 订购总量
    from [Order Details]
    join Orders
    on [Order Details].orderid=orders.orderid
    join Customers
    on Customers.CustomerID=Orders.CustomerID
    where Customers.CompanyName=@name
    group by Customers.CompanyName

end
go



use northwind
go
create proc p_bbb
@name nvarchar(40)
as
select 订购总量=
case
when (select count(quantity)
from [Order Details]
join Orders
on [Order Details].orderid=orders.orderid
join Customers
on Customers.CustomerID=Orders.CustomerID
where Customers.CompanyName=@name
group by Customers.CompanyName)>1000
then '一级客户'
when
(select count(quantity)
from [Order Details]
join Orders
on [Order Details].orderid=orders.orderid
join Customers
on Customers.CustomerID=Orders.CustomerID
where Customers.CompanyName=@name
group by Customers.CompanyName)>500
then '二级客户'
else '三级客户'
end
go


aaa 'Alfreds Futterkiste'

p_bbb 'Drachenblut Delikatessen'