有表A和表B表A
bh aa data
=================
1 小红 2001-01-01
2 小花 2002-02-02
3 小狗 2003-03-03
...表B (fzbh对应A表的bh)
fzbh ba bb(数量)
===================
1 小刀 1
1 小刀 2
2 橡皮 3
3 小刀 2
3 西瓜 2
3 橡皮 3
...要得到如下的表用SQL怎么写?
即:A表的每条记录,对应B表的小刀、橡皮、西瓜的汇总。A.aa A.bh B.小刀 B.橡皮 B.西瓜
==============================
小红 1 3 0 0
小花 2 0 3 0
小狗 3 2 3 2本周小计 - 5 6 2
本月小计 - ? ? ?
本年小计 - ? ? ?
累 计 - ? ? ?
bh aa data
=================
1 小红 2001-01-01
2 小花 2002-02-02
3 小狗 2003-03-03
...表B (fzbh对应A表的bh)
fzbh ba bb(数量)
===================
1 小刀 1
1 小刀 2
2 橡皮 3
3 小刀 2
3 西瓜 2
3 橡皮 3
...要得到如下的表用SQL怎么写?
即:A表的每条记录,对应B表的小刀、橡皮、西瓜的汇总。A.aa A.bh B.小刀 B.橡皮 B.西瓜
==============================
小红 1 3 0 0
小花 2 0 3 0
小狗 3 2 3 2本周小计 - 5 6 2
本月小计 - ? ? ?
本年小计 - ? ? ?
累 计 - ? ? ?
(SELECT SUM(bb) FROM 表B WHERE fzbh=a.bh AND ba='小刀') AS '小刀',
(SELECT SUM(bb) FROM 表B WHERE fzbh=a.bh AND ba='橡皮') AS '橡皮',
(SELECT SUM(bb) FROM 表B WHERE fzbh=a.bh AND ba='西瓜') AS '西瓜'
FROM 表A a GROUP BY a.bh
UNION
SELECT '本周小计' AS aa,'' AS bh,
(SELECT SUM(bb) FROM 表A a,表B b WHERE a.bh=b.fzbh AND b.ba='小刀' AND a.data BETWEEN 本周),
(SELECT SUM(bb) FROM 表A a,表B b WHERE a.bh=b.fzbh AND b.ba='橡皮' AND a.data BETWEEN 本周),
(SELECT SUM(bb) FROM 表A a,表B b WHERE a.bh=b.fzbh AND b.ba='西瓜' AND a.data BETWEEN 本周)
UNION
SELECT '本周小计' AS aa,'' AS bh,
(SELECT SUM(bb) FROM 表A a,表B b WHERE a.bh=b.fzbh AND b.ba='小刀' AND a.data BETWEEN 本月),
(SELECT SUM(bb) FROM 表A a,表B b WHERE a.bh=b.fzbh AND b.ba='橡皮' AND a.data BETWEEN 本月),
(SELECT SUM(bb) FROM 表A a,表B b WHERE a.bh=b.fzbh AND b.ba='西瓜' AND a.data BETWEEN 本月)
UNION
SELECT '本周小计' AS aa,'' AS bh,
(SELECT SUM(bb) FROM 表A a,表B b WHERE a.bh=b.fzbh AND b.ba='小刀' AND a.data BETWEEN 本年),
(SELECT SUM(bb) FROM 表A a,表B b WHERE a.bh=b.fzbh AND b.ba='橡皮' AND a.data BETWEEN 本年),
(SELECT SUM(bb) FROM 表A a,表B b WHERE a.bh=b.fzbh AND b.ba='西瓜' AND a.data BETWEEN 本年)
UNION
SELECT '本周小计' AS aa,'' AS bh,
(SELECT SUM(bb) FROM 表A a,表B b WHERE a.bh=b.fzbh AND b.ba='小刀'),
(SELECT SUM(bb) FROM 表A a,表B b WHERE a.bh=b.fzbh AND b.ba='橡皮'),
(SELECT SUM(bb) FROM 表A a,表B b WHERE a.bh=b.fzbh AND b.ba='西瓜')
SELECT a.aa,a.bh,
(SELECT SUM(bb) FROM 表B WHERE fzbh=a.bh AND ba='小刀') AS '小刀',
(SELECT SUM(bb) FROM 表B WHERE fzbh=a.bh AND ba='橡皮') AS '橡皮',
(SELECT SUM(bb) FROM 表B WHERE fzbh=a.bh AND ba='西瓜') AS '西瓜'
FROM 表A a GROUP BY a.bh
UNION
SELECT '本周小计' AS aa,'' AS bh, //本周小计
(SELECT SUM(bb) FROM 表A a,表B b WHERE a.bh=b.fzbh AND b.ba='小刀' AND a.data BETWEEN 本周),
(SELECT SUM(bb) FROM 表A a,表B b WHERE a.bh=b.fzbh AND b.ba='橡皮' AND a.data BETWEEN 本周),
(SELECT SUM(bb) FROM 表A a,表B b WHERE a.bh=b.fzbh AND b.ba='西瓜' AND a.data BETWEEN 本周)
UNION
SELECT '本月小计' AS aa,'' AS bh, //本月小计
(SELECT SUM(bb) FROM 表A a,表B b WHERE a.bh=b.fzbh AND b.ba='小刀' AND a.data BETWEEN 本月),
(SELECT SUM(bb) FROM 表A a,表B b WHERE a.bh=b.fzbh AND b.ba='橡皮' AND a.data BETWEEN 本月),
(SELECT SUM(bb) FROM 表A a,表B b WHERE a.bh=b.fzbh AND b.ba='西瓜' AND a.data BETWEEN 本月)
UNION
SELECT '本年小计' AS aa,'' AS bh, //本年小计
(SELECT SUM(bb) FROM 表A a,表B b WHERE a.bh=b.fzbh AND b.ba='小刀' AND a.data BETWEEN 本年),
(SELECT SUM(bb) FROM 表A a,表B b WHERE a.bh=b.fzbh AND b.ba='橡皮' AND a.data BETWEEN 本年),
(SELECT SUM(bb) FROM 表A a,表B b WHERE a.bh=b.fzbh AND b.ba='西瓜' AND a.data BETWEEN 本年)
UNION
SELECT '累计' AS aa,'' AS bh, //累计
(SELECT SUM(bb) FROM 表A a,表B b WHERE a.bh=b.fzbh AND b.ba='小刀'),
(SELECT SUM(bb) FROM 表A a,表B b WHERE a.bh=b.fzbh AND b.ba='橡皮'),
(SELECT SUM(bb) FROM 表A a,表B b WHERE a.bh=b.fzbh AND b.ba='西瓜')
create table A(bh int ,aa varchar(20),data datetime
)
create table B(fzbh int, ba varchar(20), bb int )
--插入数据
insert into A select 1, '小红', '2001-01-01'
union select 2, '小花' , '2002-02-02'
union select 3, '小狗', '2003-03-03'insert into B select 1, '小刀', 1
union select 1, '小刀', 2
union select 2, '橡皮', 3
union select 3, '小刀', 2
union select 3, '西瓜', 2
union select 3, '橡皮', 3--测试declare @sql varchar(8000)
set @sql = 'select max(aa)[aa],fzbh'
select @sql = @sql + ',isnull(sum(case ba when '''+ba+''' then bb end ),0)['+ba+']'
from (select distinct ba from B) as a
select @sql = @sql+' from B inner join A on B.fzbh=A.bh group by fzbh '
print(@sql)
exec(@sql)/*--结果
aa fzbh 西瓜 橡皮 小刀
-------------------- ----------- ----------- ----------- -----------
小红 1 0 0 3
小花 2 0 3 0
小狗 3 2 3 2 */
不妨把上面的结果集放到临时表中
然后对临时表按日期分组统计union连接就行
月和年的都好统计
本周的区间这样可以达到要求
同时等待更好的方法
select getdate()-DATEPART ( weekday , getdate() )+1 --本周第一天
select getdate()+7-DATEPART ( weekday , getdate() ) --本周最后一天
的方法很通用的
出现“1行被上次查询影响”是怎么回事啊
select * from 表 where datediff(week,时间字段,getdate())=0
回qizhanfeng(glacier)
出现“1行被上次查询影响”是怎么回事啊没关系,空值列sum时自动以0计算
然后通过存储过程,利用游标循环进行进一步的统计分析。这种方式通用性强,适合很复杂的统计分析