create table pro (Pno varchar(10), typeName varchar(20),Pname varchar(20),Sdate datetime,price int)
insert into pro select '01','生活用户','大米','1999-10-11',100
insert into pro select '02','家用电器','电视机','1997-10-11',1000
insert into pro select '03','儿童用户','电玩车','1998-10-11',300
insert into pro select '04','办公用户','打印机','1996-10-11',100
insert into pro select '01','生活用户','大米','1999-10-11',100
insert into pro select '02','家用电器','电视机','997-10-11',1000
insert into pro select '03','儿童用户','电玩车','1998-10-11',400
insert into pro select '04','办公用户','打印机','2004-10-11',50
insert into pro select '04','办公用户','打印机','2005-10-11',150 select typename,pName,[2000-1-1年之前平均价格]=cast(avg(总数) as decimal(10,2)),
[2000-1-1至2004-1-1年平均价格]=cast(avg(总数2) as decimal(10,2))
from
(
select typename,pName,总数=sum(case when Sdate<'2000-1-1' then price else 0 end),
总数2=sum(case when Sdate between '2000-1-1' and '2004-1-1' then price else 0 end)
from pro
group by typename,pname
) a
group by typename,pNamedrop table pro/*
typename pName 2000-1-1年之前平均价格 2000-1-1至2004-1-1年平均价格
-------------------- -------------------- --------------- ----------------------
办公用户 打印机 100.00 .00
生活用户 大米 200.00 .00
家用电器 电视机 1000.00 .00
儿童用户 电玩车 700.00 .00(所影响的行数为 4 行)
*/
insert into pro select '01','生活用户','大米','1999-10-11',100
insert into pro select '02','家用电器','电视机','1997-10-11',1000
insert into pro select '03','儿童用户','电玩车','1998-10-11',300
insert into pro select '04','办公用户','打印机','1996-10-11',100
insert into pro select '01','生活用户','大米','1999-10-11',100
insert into pro select '02','家用电器','电视机','997-10-11',1000
insert into pro select '03','儿童用户','电玩车','1998-10-11',400
insert into pro select '04','办公用户','打印机','2004-10-11',50
insert into pro select '04','办公用户','打印机','2005-10-11',150 select typename,pName,[2000-1-1年之前平均价格]=cast(avg(总数) as decimal(10,2)),
[2000-1-1至2004-1-1年平均价格]=cast(avg(总数2) as decimal(10,2))
from
(
select typename,pName,总数=sum(case when Sdate<'2000-1-1' then price else 0 end),
总数2=sum(case when Sdate between '2000-1-1' and '2004-1-1' then price else 0 end)
from pro
group by typename,pname
) a
group by typename,pNamedrop table pro/*
typename pName 2000-1-1年之前平均价格 2000-1-1至2004-1-1年平均价格
-------------------- -------------------- --------------- ----------------------
办公用户 打印机 100.00 .00
生活用户 大米 200.00 .00
家用电器 电视机 1000.00 .00
儿童用户 电玩车 700.00 .00(所影响的行数为 4 行)
*/
insert into pro select '01','生活用户','大米','1999-10-11',100
insert into pro select '02','家用电器','电视机','1997-10-11',1000
insert into pro select '03','儿童用户','电玩车','1998-10-11',300
insert into pro select '04','办公用户','打印机','1996-10-11',100
insert into pro select '01','生活用户','大米','1999-10-11',100
insert into pro select '02','家用电器','电视机','1997-10-11',1000
insert into pro select '03','儿童用户','电玩车','1998-10-11',400
insert into pro select '04','办公用户','打印机','2004-10-11',50
insert into pro select '04','办公用户','打印机','2005-10-11',150
go
select typename,pname,
before20000101=avg(case when Sdate<'2000-1-1' then price else null end),
before20040101=avg(case when Sdate between '2000-1-1' and dateadd(ms,-1,'2004-1-1') then price else null end)
from pro group by typename,pname
--表中没有2000-1-1到2004-1-1之间的数据
/*办公用户 打印机 100 NULL
生活用户 大米 100 NULL
家用电器 电视机 1000 NULL
儿童用户 电玩车 350 NULL
*/
go
drop table pro
go
insert into pro select '01','生活用户','大米','1999-10-11',100
insert into pro select '02','家用电器','电视机','1997-10-11',1000
insert into pro select '03','儿童用户','电玩车','1998-10-11',300
insert into pro select '04','办公用户','打印机','1996-10-11',100
insert into pro select '01','生活用户','大米','1999-10-11',100
insert into pro select '02','家用电器','电视机','997-10-11',1000
insert into pro select '03','儿童用户','电玩车','1998-10-11',400
insert into pro select '04','办公用户','打印机','2004-10-11',50
insert into pro select '04','办公用户','打印机','2005-10-11',150 select typename,pName,[2000-1-1年之前平均价格]=cast(avg(总数) as varchar),
[2000-1-1至2004-1-1年平均价格]=cast(avg(总数2) as varchar),
[2004-1-1年以后平均价格]=cast(avg(总数3) as varchar)
from
(
select typename,pName,总数=sum(case when Sdate<'2000-1-1' then price else 0 end),
总数2=sum(case when Sdate between '2000-1-1' and '2004-1-1' then price else 0 end),
总数3=sum(case when Sdate>'2004-1-1' then price else 0 end)
from pro
group by typename,pname
) a
group by typename,pNamedrop table pro/*
typename pName 2000-1-1年之前平均价格 2000-1-1至2004-1-1年平均价格 2004-1-1年以后平均价格
-------------------- -------------------- ------------------------------ ------------------------------ ------------------------------
办公用户 打印机 100 0 200
生活用户 大米 200 0 0
家用电器 电视机 1000 0 0
儿童用户 电玩车 700 0 0(所影响的行数为 4 行)*/
当直接给 avg 的一行值为 null 时, sum ,count均忽略计数,所以直接 avg (case when ... else null end)即可. 不会受到不满足条件的个数的影响.
create table pro (Pno varchar(10), typeName varchar(20),Pname varchar(20),Sdate datetime,price int)
insert into pro select '01','生活用户','大米','1999-10-11',100
insert into pro select '02','家用电器','电视机','1997-10-11',1000
insert into pro select '03','儿童用户','电玩车','1998-10-11',300
insert into pro select '04','办公用户','打印机','1996-10-11',100
insert into pro select '01','生活用户','大米','1999-10-11',100
insert into pro select '02','家用电器','电视机','1997-10-11',1000
insert into pro select '03','儿童用户','电玩车','1998-10-11',400
insert into pro select '04','办公用户','打印机','2004-10-11',50
insert into pro select '04','办公用户','打印机','2005-10-11',150/*
--求:以产品类别分类typeName求产品的平均价格。
--2000-1-1年之前的产品平均价格为一列。2000-1-1至2004-1-1年为一列。
--即将产品平均价格根据情况分2列来显示。类别名称,产品名称,2000-1-1年之前平均价格,2000-1-1至2004-1-1年平均价格
生活用户 大米 avg() null
办公用户 打印机 null avg()
*/
select a.*,b.b,c.c from
(select distinct typeName, Pname from pro) a
left join
(select typeName, Pname, b = avg(price) from pro where sdate < '2001-01-01' group by typeName, pname) b
on a.typeName = b.typeName and a.Pname = b.Pname
left join
(select typeName, Pname, c = avg(price) from pro where sdate >= '2001-01-01' group by typeName, pname) c
on a.typeName = c.typeName and a.Pname = c.Pname/*
办公用户 打印机 100 100
儿童用户 电玩车 350 NULL
家用电器 电视机 1000 NULL
生活用户 大米 100 NULL
*/
insert into tb select '01','生活用户','大米','1999-10-11',100
insert into tb select '02','家用电器','电视机','1997-10-11',1000
insert into tb select '03','儿童用户','电玩车','1998-10-11',300
insert into tb select '04','办公用户','打印机','1996-10-11',100
insert into tb select '01','生活用户','大米','1999-10-11',100
insert into tb select '02','家用电器','电视机','1997-10-11',1000
insert into tb select '03','儿童用户','电玩车','1998-10-11',400
insert into tb select '04','办公用户','打印机','2004-10-11',50
insert into tb select '04','办公用户','打印机','2005-10-11',150
goselect isnull(t1.typeName,t2.typeName) typeName,
isnull(t1.Pname , t2.Pname) Pname,
isnull(t1.price,0) '2000-1-1年之前平均价格',
isnull(t2.price,0) '2000-1-1至2004-1-1年平均价格'
from
(
select typeName,Pname,price=avg(price) from tb where sdate < '2000-01-01' group by typeName,Pname
) t1
full join
(
select typeName,Pname,price=avg(price) from tb where sdate between '2000-01-01' and '2004-01-01' group by typeName,Pname
) t2
on t1.typeName = t2.typeName and t1.typeName = t2.typeNamedrop table tb/*
typeName Pname 2000-1-1年之前平均价格 2000-1-1至2004-1-1年平均价格
-------------------- -------------------- --------------------------------------- ---------------------------------------
办公用户 打印机 100.000000 0.000000
儿童用户 电玩车 350.000000 0.000000
家用电器 电视机 1000.000000 0.000000
生活用户 大米 100.000000 0.000000(4 行受影响)
*/
insert into tb select '01','生活用户','大米','1999-10-11',100
insert into tb select '02','家用电器','电视机','1997-10-11',1000
insert into tb select '03','儿童用户','电玩车','1998-10-11',300
insert into tb select '04','办公用户','打印机','1996-10-11',100
insert into tb select '01','生活用户','大米','1999-10-11',100
insert into tb select '02','家用电器','电视机','1997-10-11',1000
insert into tb select '03','儿童用户','电玩车','1998-10-11',400
insert into tb select '04','办公用户','打印机','2004-10-11',50
insert into tb select '04','办公用户','打印机','2005-10-11',150
goselect isnull(t1.typeName,t2.typeName) typeName,
isnull(t1.Pname , t2.Pname) Pname,
isnull(t1.price,0) '2000-1-1年之前平均价格',
isnull(t2.price,0) '2000-1-1至2004-1-1年平均价格'
from
(
select typeName,Pname,price=cast(avg(price) as decimal(18,2)) from tb where sdate < '2000-01-01' group by typeName,Pname
) t1
full join
(
select typeName,Pname,price=cast(avg(price) as decimal(18,2)) from tb where sdate between '2000-01-01' and '2004-01-01' group by typeName,Pname
) t2
on t1.typeName = t2.typeName and t1.typeName = t2.typeNamedrop table tb/*
typeName Pname 2000-1-1年之前平均价格 2000-1-1至2004-1-1年平均价格
-------------------- -------------------- --------------------------------------- ---------------------------------------
办公用户 打印机 100.00 0.00
儿童用户 电玩车 350.00 0.00
家用电器 电视机 1000.00 0.00
生活用户 大米 100.00 0.00(4 行受影响)
*/
用forum.csdn.net
不知道在搞什么.
before20040101=avg(case when Sdate between '2000-1-1' and dateadd(ms,-1,'2004-1-1') then price else null end) from pro group by typename,pname是在分组前就根据case when中的筛选条件进行筛选,然后再分组吗?