22,209
社区成员
发帖
与我相关
我的任务
分享
--客户表
create table TB_Customer
(
ID int identity primary key,
Name nvarchar(10),
Mobile nvarchar(10)
)
--销售单表
create table TB_Sale
(
ID int identity primary key,
Year int,--年份
CustomerID int,--客户表外键
IsCard bit default(0),--有卡片销售
IsProduct bit default(0),--有零售
SalePrice decimal(10,2)--销售额
)
--卡片表
create table TB_Card
(
ID int identity primary key,
Year int,--年份
Statu int, --1、不可以2、可用3、已用
CardNumber nvarchar(20),
CardPrice int,--卡片额度
BuyDiscount decimal(3,2),--购买折扣
CustomerID int,--客户表外键
SaleID int,--销售单表外键
)
--查询所有客户的购买额度和卡片的购买额度
select c.ID,C.Name,c.Mobile,
(select ISNULL(sum(s.SalePrice),0) from TB_Sale s where s.CustomerID=c.ID) as 'SalePrice',
(select ISNULL(sum(case when d.BuyDiscount>0 d.CardPrice*d.BuyDiscount then else d.CardPrice end),0) from TB_Card d where d.CustomerID=c.ID) as 'CardPrice'
from TB_Customer c
--查询销售单中不同状态卡片的集合
select * from TB_Card where Statu=1 and SaleID=@SaleID
select * from TB_Card where Statu=2 and SaleID=@SaleID
select * from TB_Card where Statu=3 and SaleID=@SaleID