可怕的统计汇总之一
create table tb_car_sale(
CarID varchar(20) primary key,
TypID int,
Color varchar(10),
SellDate datetime)
insert tb_car_sale select '1001 ',1, '皓白 ', '2007-1-9 '
union all select '1002 ',2, '银 ', '2007-7-30 '
union all select '1003 ',1, '皓白 ', '2007-4-13 '
union all select '1004 ',1, '皓白 ', '2007-9-21 '
union all select '1005 ',4, '碳黑 ', '2007-2-17 '
union all select '1006 ',4, '水晶蓝 ', '2007-3-29 '
union all select '1007 ',7, '水晶蓝 ', '2007-7-9 '
---要统计的车型
create table tb_stat_type(
TypID int primary key,
TypNam varchar(20))
insert tb_stat_type select 1, 'L4 '
union all select 2, 'L4+ '
union all select 3, 'GL3.5 '
union all select 4, 'GL2.5 '
--要统计的颜色
create table tb_stat_color(
ColorID int primary key,
ColorNam varchar(10))
insert tb_stat_color select 1, '白 '
union all select 2, '银 '
union all select 3, '黑 '
union all select 4, '蓝 '
说明：1、销车表（tb_car_sale）里的车型、颜色可以是tb_stat_type、tb_stat_color之外的车型和颜色
2、只统计tb_stat_type、tb_stat_color中要求的车型和颜色，其中颜色用like规则匹配，比如水晶蓝=蓝
3、要统计某一天（输入参数）所在星期及前3个星期(共4个星期)每个星期每款车型每种颜色的销售数量，期望结果如下：
比如是输入是2007-8-1,则结果为:
车型 w-3白 w-3银 w-3黑 w-3蓝 w-2白 w-2银 w-2黑 w-2蓝...合计
L4
L4+ 统计结果略
GL3.5
GL2.5
总计
(除了车型、合计以外，应该还有16列:4个星期和4种颜色的组合为16列;w-3表示3个星期前的星期,一直到w-0:本周)
4.可以稍微变通...但大概的结构不能变
[解决办法]
繁
帮你顶一下先
[解决办法]
仔细看了你的题目,没什么难的,标题到是吓人,
这里点一下，
1、group by生成三个结果集
2、行列转换
[解决办法]
SELECT a.typNam,[w-0白],[w-0银],[w-0黑],[w-0蓝],[w-1白],[w-1银],[w-1黑],[w-1蓝],[w-2白],[w-2银],[w-2黑],[w-2蓝],[w-3白],[w-3银],[w-3黑],[w-3蓝],总计
FROM tb_stat_type a
left outer join (
SELECT typNam ,
Count(case when ColorID=1 then ColorID end )as N 'w-0白 ' ,
Count( case when ColorID=2 then ColorID end) as N 'w-0银 ' ,
Count( case when ColorID=3 then ColorID end) as N 'w-0黑 ' ,
Count( case when ColorID=4 then ColorID end) as N 'w-0蓝 '
FROM tb_car_sale inner join tb_stat_type on tb_car_sale.typID=tb_stat_type.TypID
inner join tb_stat_color on Color like ( '% '+ColorNam + '% ')
WHERE datediff(wk,SellDate , '2007-8-1 ')=0
group by typNam) w0 on a.typNam=w0.typNam
left outer join (
SELECT typNam ,
Count(case when ColorID=1 then ColorID end )as N 'w-1白 ' ,
Count( case when ColorID=2 then ColorID end) as N 'w-1银 ' ,
Count( case when ColorID=3 then ColorID end) as N 'w-1黑 ' ,
Count( case when ColorID=4 then ColorID end) as N 'w-1蓝 '
FROM tb_car_sale inner join tb_stat_type on tb_car_sale.typID=tb_stat_type.TypID
inner join tb_stat_color on Color like ( '% '+ColorNam + '% ')
WHERE datediff(wk,SellDate , '2007-8-1 ')=1
group by typNam) w1 on a.typNam=w1.typNam
left outer join (
SELECT typNam ,
Count(case when ColorID=1 then ColorID end )as N 'w-2白 ' ,
Count( case when ColorID=2 then ColorID end) as N 'w-2银 ' ,
Count( case when ColorID=3 then ColorID end) as N 'w-2黑 ' ,
Count( case when ColorID=4 then ColorID end) as N 'w-2蓝 '
FROM tb_car_sale inner join tb_stat_type on tb_car_sale.typID=tb_stat_type.TypID
inner join tb_stat_color on Color like ( '% '+ColorNam + '% ')
WHERE datediff(wk,SellDate , '2007-8-1 ')=2
group by typNam) w2 on a.typNam=w2.typNam
left outer join (
SELECT typNam ,
Count(case when ColorID=1 then ColorID end )as N 'w-3白 ' ,
Count( case when ColorID=2 then ColorID end) as N 'w-3银 ' ,
Count( case when ColorID=3 then ColorID end) as N 'w-3黑 ' ,
Count( case when ColorID=4 then ColorID end) as N 'w-3蓝 '
FROM tb_car_sale inner join tb_stat_type on tb_car_sale.typID=tb_stat_type.TypID
inner join tb_stat_color on Color like ( '% '+ColorNam + '% ')
WHERE datediff(wk,SellDate , '2007-8-1 ')=3
group by typNam) w3 on a.typNam=w3.typNam
left outer join (
SELECT typNam ,
Count( ColorID )as N '总计 '
FROM tb_car_sale inner join tb_stat_type on tb_car_sale.typID=tb_stat_type.TypID
inner join tb_stat_color on Color like ( '% '+ColorNam + '% ')
WHERE datediff(wk,SellDate , '2007-8-1 ') between 0 and 3
group by typNam) w4 on a.typNam=w4.typNam
