有两张表产品表product,销售表proout
--------
产品表productprono proname price
00001 电视 3000.00
00002 空调 2000.00
00003 床 1000.00
00004 餐桌 1500.00
00005 音响 5000.00
00006 沙发 6000.00 销售表proout
saledate cusno prono quantity
2002-01-01 00:00:00.000 001 00001 10
2002-01-01 00:00:00.000 001 00002 5
2002-01-01 00:00:00.000 002 00001 5
2002-01-01 00:00:00.000 002 00003 10
2002-01-02 00:00:00.000 001 00001 10
2002-01-02 00:00:00.000 001 00003 20
2002-01-02 00:00:00.000 001 00001 10
2002-01-02 00:00:00.000 003 00004 30
2002-01-03 00:00:00.000 003 00004 10
prono为外键
要求 创建视图view2,要求能统计汇总每种产品的销售量和销售金额
我写的:
select proname as '产品名',quantity as '销售量',(price*quantity) as '销售金额'
from product,proout
where product.prono=proout.prono
order by proname产品名 销售量 销售金额
餐桌 30 45000.00
餐桌 10 15000.00
床 10 10000.00
床 20 20000.00
电视 10 30000.00
电视 5 15000.00
电视 10 30000.00
电视 10 30000.00
空调 5 10000.00这个结果有重复值,如何用group by 或compute实现呢???
要求能统计汇总每种产品的销售量和销售金额
--------
产品表productprono proname price
00001 电视 3000.00
00002 空调 2000.00
00003 床 1000.00
00004 餐桌 1500.00
00005 音响 5000.00
00006 沙发 6000.00 销售表proout
saledate cusno prono quantity
2002-01-01 00:00:00.000 001 00001 10
2002-01-01 00:00:00.000 001 00002 5
2002-01-01 00:00:00.000 002 00001 5
2002-01-01 00:00:00.000 002 00003 10
2002-01-02 00:00:00.000 001 00001 10
2002-01-02 00:00:00.000 001 00003 20
2002-01-02 00:00:00.000 001 00001 10
2002-01-02 00:00:00.000 003 00004 30
2002-01-03 00:00:00.000 003 00004 10
prono为外键
要求 创建视图view2,要求能统计汇总每种产品的销售量和销售金额
我写的:
select proname as '产品名',quantity as '销售量',(price*quantity) as '销售金额'
from product,proout
where product.prono=proout.prono
order by proname产品名 销售量 销售金额
餐桌 30 45000.00
餐桌 10 15000.00
床 10 10000.00
床 20 20000.00
电视 10 30000.00
电视 5 15000.00
电视 10 30000.00
电视 10 30000.00
空调 5 10000.00这个结果有重复值,如何用group by 或compute实现呢???
要求能统计汇总每种产品的销售量和销售金额
declare @product table([prono] varchar(5),[proname] varchar(4),[price] numeric(6,2))
insert @product
select '00001','电视',3000.00 union all
select '00002','空调',2000.00 union all
select '00003','床',1000.00 union all
select '00004','餐桌',1500.00 union all
select '00005','音响',5000.00 union all
select '00006','沙发',6000.00--> 测试数据:@proout
declare @proout table([saledate] datetime,[cusno] varchar(3),[prono] varchar(5),[quantity] int)
insert @proout
select '2002-01-01 00:00:00.000','001','00001',10 union all
select '2002-01-01 00:00:00.000','001','00002',5 union all
select '2002-01-01 00:00:00.000','002','00001',5 union all
select '2002-01-01 00:00:00.000','002','00003',10 union all
select '2002-01-02 00:00:00.000','001','00001',10 union all
select '2002-01-02 00:00:00.000','001','00003',20 union all
select '2002-01-02 00:00:00.000','001','00001',10 union all
select '2002-01-02 00:00:00.000','003','00004',30 union all
select '2002-01-03 00:00:00.000','003','00004',10--------------------------------查询开始------------------------------select a.proname,sum(quantity),sum(quantity*price) from @product a left join @proout b on a.prono=b.prono
group by a.proname
/*
proname
------- ----------- ---------------------------------------
餐桌 40 60000.00
床 30 30000.00
电视 35 105000.00
空调 5 10000.00
沙发 NULL NULL
音响 NULL NULL
警告: 聚合或其他 SET 操作消除了空值。(6 行受影响)
*/
-- Author :fredrickhu(小F,向高手学习)
-- Date :2010-04-27 20:14:54
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[product]
if object_id('[product]') is not null drop table [product]
go
create table [product]([prono] varchar(5),[proname] varchar(4),[price] numeric(6,2))
insert [product]
select '00001','电视',3000.00 union all
select '00002','空调',2000.00 union all
select '00003','床',1000.00 union all
select '00004','餐桌',1500.00 union all
select '00005','音响',5000.00 union all
select '00006','沙发',6000.00
--> 测试数据:[proout]
if object_id('[proout]') is not null drop table [proout]
go
create table [proout]([saledate] datetime,[cusno] varchar(3),[prono] varchar(5),[quantity] int)
insert [proout]
select '2002-01-01 00:00:00.000','001','00001',10 union all
select '2002-01-01 00:00:00.000','001','00002',5 union all
select '2002-01-01 00:00:00.000','002','00001',5 union all
select '2002-01-01 00:00:00.000','002','00003',10 union all
select '2002-01-02 00:00:00.000','001','00001',10 union all
select '2002-01-02 00:00:00.000','001','00003',20 union all
select '2002-01-02 00:00:00.000','001','00001',10 union all
select '2002-01-02 00:00:00.000','003','00004',30 union all
select '2002-01-03 00:00:00.000','003','00004',10
--------------开始查询--------------------------
select
a.proname,isnull(sum(quantity),0),isnull(sum(quantity*price),0)
from
product a
left join
proout b
on
a.prono=b.prono
group by
a.proname
----------------结果----------------------------
/* proname
------- ----------- ---------------------------------------
餐桌 40 60000.00
床 30 30000.00
电视 35 105000.00
空调 5 10000.00
沙发 0 0.00
音响 0 0.00
警告: 聚合或其他 SET 操作消除了空值。(6 行受影响)*/
insert [product]
select '00001','电视',3000.00 union all
select '00002','空调',2000.00 union all
select '00003','床',1000.00 union all
select '00004','餐桌',1500.00 union all
select '00005','音响',5000.00 union all
select '00006','沙发',6000.00
create table [proout]([saledate] datetime,[cusno] varchar(3),[prono] varchar(5),[quantity] int)
insert [proout]
select '2002-01-01 00:00:00.000','001','00001',10 union all
select '2002-01-01 00:00:00.000','001','00002',5 union all
select '2002-01-01 00:00:00.000','002','00001',5 union all
select '2002-01-01 00:00:00.000','002','00003',10 union all
select '2002-01-02 00:00:00.000','001','00001',10 union all
select '2002-01-02 00:00:00.000','001','00003',20 union all
select '2002-01-02 00:00:00.000','001','00001',10 union all
select '2002-01-02 00:00:00.000','003','00004',30 union all
select '2002-01-03 00:00:00.000','003','00004',10
goselect m.proname 产品名, sum(n.quantity) 销售量 , sum(m.price) 销售金额 from product m , proout n
where m.prono = n.prono
group by m.proname
/*
产品名 销售量 销售金额
---- ----------- ----------------------------------------
餐桌 40 3000.00
床 30 2000.00
电视 35 12000.00
空调 5 2000.00(所影响的行数为 4 行)
*/select m.proname 产品名, isnull(sum(n.quantity),0) 销售量 , sum(m.price) 销售金额 from product m left join proout n
on m.prono = n.prono
group by m.proname
/*
产品名 销售量 销售金额
---- ----------- ----------------------------------------
餐桌 40 3000.00
床 30 2000.00
电视 35 12000.00
空调 5 2000.00
沙发 0 6000.00
音响 0 5000.00(所影响的行数为 6 行)*/
drop table product, proout
insert [product]
select '00001','电视',3000.00 union all
select '00002','空调',2000.00 union all
select '00003','床',1000.00 union all
select '00004','餐桌',1500.00 union all
select '00005','音响',5000.00 union all
select '00006','沙发',6000.00
create table [proout]([saledate] datetime,[cusno] varchar(3),[prono] varchar(5),[quantity] int)
insert [proout]
select '2002-01-01 00:00:00.000','001','00001',10 union all
select '2002-01-01 00:00:00.000','001','00002',5 union all
select '2002-01-01 00:00:00.000','002','00001',5 union all
select '2002-01-01 00:00:00.000','002','00003',10 union all
select '2002-01-02 00:00:00.000','001','00001',10 union all
select '2002-01-02 00:00:00.000','001','00003',20 union all
select '2002-01-02 00:00:00.000','001','00001',10 union all
select '2002-01-02 00:00:00.000','003','00004',30 union all
select '2002-01-03 00:00:00.000','003','00004',10
goselect m.proname 产品名, sum(n.quantity) 销售量 , sum(n.quantity*m.price) 销售金额 from product m , proout n
where m.prono = n.prono
group by m.proname
/*产品名 销售量 销售金额
---- ----------- ----------------------------------------
餐桌 40 60000.00
床 30 30000.00
电视 35 105000.00
空调 5 10000.00(所影响的行数为 4 行)
*/select m.proname 产品名, isnull(sum(n.quantity),0) 销售量 , isnull(sum(n.quantity*m.price),0) 销售金额 from product m left join proout n
on m.prono = n.prono
group by m.proname
/*
产品名 销售量 销售金额
---- ----------- ----------------------------------------
餐桌 40 60000.00
床 30 30000.00
电视 35 105000.00
空调 5 10000.00
沙发 0 .00
音响 0 .00(所影响的行数为 6 行)*/
drop table product, proout