销售员表销售员编号sellid
销售员姓名sellname
-----------
产品表产品编号(pid)
产品名称(pname)
产品单价(pprice)
------------
交易表客户编号(cid)
产品编号(pprice)
销售员编号(sellid)
购买数量(buynum)
交易时间(buydate)
-------------------------------销售员小王卖给客户小夏的各类产品在2004-5-8日到2005-3-6日销售的产品名称和每个产品一共销售的金额,要求按照时间倒序排列。
销售员姓名sellname
-----------
产品表产品编号(pid)
产品名称(pname)
产品单价(pprice)
------------
交易表客户编号(cid)
产品编号(pprice)
销售员编号(sellid)
购买数量(buynum)
交易时间(buydate)
-------------------------------销售员小王卖给客户小夏的各类产品在2004-5-8日到2005-3-6日销售的产品名称和每个产品一共销售的金额,要求按照时间倒序排列。
FROM 产品表 INNER JOIN
交易表 ON 产品表.pid = 交易表.pprice
WHERE (交易表.cid = N'[小夏的编号]') AND (交易表.buydate < 2004-5-8) OR
(交易表.buydate > 2005-3-6)
ORDER BY 交易表.buydate DESC
(
sellid int primary key identity(1,1),
sellname varchar(20)
)create table product
(
productid int primary key identity(1,1),
productname varchar(20) not null,
productprice real not null
)create table transfer
(
transid int primary key identity(1,1),
customername varchar(20) not null,
sellid int not null,
productid int not null,
num int not null,
transtime datetime not null
)insert into seller([sellname]) values('小王')
insert into seller([sellname]) values('小刘')insert into product ([productname],[productprice]) values('电视',120.0)
insert into product ([productname],[productprice]) values('电脑',12.0)
insert into product ([productname],[productprice]) values('键盘',10.0)
insert into product ([productname],[productprice]) values('房屋',20.0)
insert into product ([productname],[productprice]) values('电扇',1.0)insert into transfer([customername],[sellid],[productid],[num],[transtime]) values('小夏',1,1,10,'2006-3-8')
insert into transfer([customername],[sellid],[productid],[num],[transtime]) values('',1,2,10,'006-3-7')
insert into transfer([customername],[sellid],[productid],[num],[transtime]) values('',2,3,10,'2006-3-6')
insert into transfer([customername],[sellid],[productid],[num],[transtime]) values('小夏',1,4,10,'2006-3-5')
insert into transfer([customername],[sellid],[productid],[num],[transtime]) values('',1,5,10,'2006-3-4')
insert into transfer([customername],[sellid],[productid],[num],[transtime]) values('',1,4,10,'2006-3-3')
insert into transfer([customername],[sellid],[productid],[num],[transtime]) values('',2,3,10,'2006-3-2')
insert into transfer([customername],[sellid],[productid],[num],[transtime]) values('小夏',1,2,10,'2006-3-1')
insert into transfer([customername],[sellid],[productid],[num],[transtime]) values('',1,1,10,'2006-2-20')
insert into transfer([customername],[sellid],[productid],[num],[transtime]) values('小夏',1,2,10,'2006-2-19')
insert into transfer([customername],[sellid],[productid],[num],[transtime]) values('',1,3,10,'2006-2-18')
insert into transfer([customername],[sellid],[productid],[num],[transtime]) values('小夏',2,4,10,'2006-2-17')
insert into transfer([customername],[sellid],[productid],[num],[transtime]) values('',1,5,10,'2006-2-16')
insert into transfer([customername],[sellid],[productid],[num],[transtime]) values('',1,4,10,'2006-2-15')
insert into transfer([customername],[sellid],[productid],[num],[transtime]) values('小夏',1,3,10,'2006-2-14')
insert into transfer([customername],[sellid],[productid],[num],[transtime]) values('',1,2,10,'2006-2-13')
insert into transfer([customername],[sellid],[productid],[num],[transtime]) values('小夏',2,1,10,'2006-2-12')
insert into transfer([customername],[sellid],[productid],[num],[transtime]) values('',1,2,10,'2006-2-11')
insert into transfer([customername],[sellid],[productid],[num],[transtime]) values('',1,3,10,'2006-2-10')
insert into transfer([customername],[sellid],[productid],[num],[transtime]) values('小夏',1,4,10,'2006-2-9')
insert into transfer([customername],[sellid],[productid],[num],[transtime]) values('',2,5,10,'2006-2-8')
insert into transfer([customername],[sellid],[productid],[num],[transtime]) values('小夏',1,4,10,'2006-2-7')
insert into transfer([customername],[sellid],[productid],[num],[transtime]) values('',1,3,10,'2006-2-6')
insert into transfer([customername],[sellid],[productid],[num],[transtime]) values('',1,2,10,'2006-2-5')
insert into transfer([customername],[sellid],[productid],[num],[transtime]) values('',2,1,10,'2006-2-4')
insert into transfer([customername],[sellid],[productid],[num],[transtime]) values('小夏',1,2,10,'2006-2-3')
insert into transfer([customername],[sellid],[productid],[num],[transtime]) values('',1,3,10,'2006-2-2')
insert into transfer([customername],[sellid],[productid],[num],[transtime]) values('',1,4,10,'2006-2-1')
select seller.sellname as 销售员,transfer.transtime as 日期,transfer.customername as 客户,
product.productname as 产品名称,product.productprice*transfer.num as 销售金额
from product,seller,transfer
where product.productid = transfer.productid and seller.sellid = 1
and transfer.customername = '小夏' and transtime>='2005-5-8' and transtime <= '2006-3-6'
order by transfer.transtime [email protected]
桂林电子科技大学计算机系
信息管理与信息系统
2006-4-1
inner join product on
product.productid =transfer.productid where
customername ='小夏' and sellid=(select sellid from seller where sellname ='小王') and transtime <'2005/3/7 '
and transtime >'2004/5/7'
group by transfer.productid ,productname
产品=product.productname,
transtime,
金额=sum(product.productprice*transfer.num)
into #table
from
seller,
product,
transfer
where
seller.sellid=1 and
seller.sellid=transfer.sellid and
product.productid=transfer.productid and
transtime>='2005-5-8' and
transtime <= '2006-3-6'group by
product.productname,
transtimeselect
*
from
#table
order by
transtime desc
inner join product on
product.productid =transfer.productid where
customername ='小夏' and sellid=(select sellid from seller where sellname ='小王') and transtime <'2005/3/7 '
and transtime >'2004/5/7'
group by transfer.productid ,productname