现有两个表表A:
empid empname
1 张三
2 李四
3 王五表B:
id empid 销售额 销售日期
1 2 150 2004-11-6
2 1 100 2005-2-11
3 3 80 2005-8-12
4 1 50 2004-12-1
5 2 30 2005-5-18
6 3 120 2006-2-25
7 1 300 2006-3-10要求统计出:对于表A中的每一名职工在startdate到enddate这段时间内的消费记录。结果形式要求如下:empname 销售额 销售日期
张三
50 2004-12-1
100 2005-2-11
300 2006-3-10
小计 450 李四
150 2004-11-6
30 2005-5-18
小计 180 王五
80 2005-8-12
120 2006-2-25
小计 200
empid empname
1 张三
2 李四
3 王五表B:
id empid 销售额 销售日期
1 2 150 2004-11-6
2 1 100 2005-2-11
3 3 80 2005-8-12
4 1 50 2004-12-1
5 2 30 2005-5-18
6 3 120 2006-2-25
7 1 300 2006-3-10要求统计出:对于表A中的每一名职工在startdate到enddate这段时间内的消费记录。结果形式要求如下:empname 销售额 销售日期
张三
50 2004-12-1
100 2005-2-11
300 2006-3-10
小计 450 李四
150 2004-11-6
30 2005-5-18
小计 180 王五
80 2005-8-12
120 2006-2-25
小计 200
(select empid,empname = (select empname from 表A where 表A.empid = 表B.empid),销售额,销售日期 from 表B
union all
select empid,'小计',sum(销售额),null from 表B group by empid)s
order by s.empid,销售额
empname,
销售额,
销售日期
from
A left join B on A.empid=B.empid
group by A.empid,
empname,
销售额,
销售日期
order by A.empid
compute sum(purchaseQty) by A.empid
from
(select
A.empid,
empname,
(select 销售额 from B where A.empid=B.empid and B.id=C.id) as 销售额,
(select 销售日期from B where B.empid=B.empid and B.id=C.id) as 销售日期
from
A left join (select empid,id from B) as C
on A.empid=C.empid
)as D
order by D.empid
compute sum(销售额) by D.empid
(select empname from A where A.empid=B.empid) as empname,
销售额,
销售日期from
B
order by empname
compute sum(销售额) by empname
因为要找出‘表A中的每一名职工’的信息,所以from后用B表是不可靠的,当A中有B中未出现的纪录时,该方法是无法买足要求的。而直接以A表在from后,又会产生子查询返回值多元的问题,所以如法二A表和B表中的信息连接。
。不过较好的做法是法一用A left join B。
insert into @a select 1,'张三'
insert into @a select 2,'李四'
insert into @a select 3,'王五'
select * from @adeclare @b table(id int, empid int,pay int, paydate varchar(20))
insert into @b select 1,2,150,'2004-11-0 6'
insert into @b select 2,1,100,'2005-02-11'
insert into @b select 3,3,80,'2005-08-12'
insert into @b select 4,1,50,'2004-12-01'
insert into @b select 5,2,30,'2005-05-18'
insert into @b select 6,3,120,'2006-02-25'
insert into @b select 7,1,300,'2006-03-10'
select* from @bselect empname=(case when empname is null then '合计' when paydate is null then '小计' else empname end ),日期=(case when paydate is null then '' else paydate end),销售金额=sum(pay)
from (select a.empname as empname,b.pay as pay ,b.paydate as paydate from @a a,@b b where a.empid=b.empid )as ab
where empname is not null
group by empname,paydate with rollup
left join 表A as b on a.Empid=b.empid
where a.销售日期 between '2006-01-01' and '2006-03-01'
compute sum(销售额) By a.empid
use tempdb
go
create table a(aid int,aname varchar(10))
insert a select 1,'张三'
union all select 2,'李四'
union all select 3,'王五'
go
create table b (id int,bid int,value int,fdate datetime)
insert b select 1,2,150,'2004-11-6'
union all select 2,1,100,'2005-2-11'
union all select 3,3,80,'2005-8-12'
union all select 4,1,50,'2004-12-1'
union all select 5,2,30,'2005-5-18'
union all select 6,3,120,'2006-2-25'
union all select 7,1,300,'2006-3-10'
----------------------------------------------
--SQL语句
select case when grouping(fdate)=1 and grouping(aname)=1 then '合计:' else
case when grouping(fdate)=1 then '小计:' else aname end
end,
sum(value),
case when grouping(fdate)=1 then '' else fdate end
from a,b where aid=bid group by aname,fdate with rollup
case when grouping(fdate)=1 then '小计:' else aname end
end, sum(value),fdate
from a,b where aid=bid group by aname,fdate with rollup
(
empid int ,
empname varchar(20)
constraint PK_A primary key(empid)
)
insert into A
select 1 as empid,'張三' as empname
union select 2,'李四'
union select 3,'王五'select * from Acreate table B
(
id int ,
empid int ,
purchaseQty int,
purchaseDay datetime
constraint PK_B primary key
(id)
)
insert into B
select 1 as id, 2 as empid,150 as 'purchaseQty' , '2004-11-6' as 'purchaseDay'
union select 2 , 1, 100, '2005-2-11'
union select 3 , 3 , 80 , '2005-8-12'
union select 4 , 1 , 50 , '2004-12-1'
union select 5 , 2 , 30 , '2005-5-18'
union select 6 , 3 , 120 , '2006-2-25'
union select 7 , 1 , 300 , '2006-3-10'
select * from B-----------------------------------------------------------------------------------------------------------
-- 方法一:
select
empname,
purchaseQty,
purchaseDay
from
A left join B on A.empid=B.empid
group by A.empid,
empname,
purchaseQty,
purchaseDay
order by A.empid
compute sum(purchaseQty) by A.empid
---------------------------------------------------------------------------------
-- 法二:
select *
from
(select
A.empid,
empname,
(select purchaseQty from B where A.empid=B.empid and B.id=C.id) as purchaseQty,
(select purchaseDay from B where B.empid=B.empid and B.id=C.id) as purchaseDay
from
A left join (select empid,id from B) as C
on A.empid=C.empid
)as D
order by D.empid
compute sum(purchaseQty) by D.empid
结果如下:empid empname
----------- --------------------
1 張三
2 李四
3 王五(3 件処理されました)
(7 件処理されました)id empid purchaseQty purchaseDay
----------- ----------- ----------- ------------------------------------------------------
1 2 150 2004-11-06 00:00:00.000
2 1 100 2005-02-11 00:00:00.000
3 3 80 2005-08-12 00:00:00.000
4 1 50 2004-12-01 00:00:00.000
5 2 30 2005-05-18 00:00:00.000
6 3 120 2006-02-25 00:00:00.000
7 1 300 2006-03-10 00:00:00.000(7 件処理されました)empname purchaseQty purchaseDay
-------------------- ----------- ------------------------------------------------------
張三 50 2004-12-01 00:00:00.000
張三 100 2005-02-11 00:00:00.000
張三 300 2006-03-10 00:00:00.000 sum
===========
450
empname purchaseQty purchaseDay
-------------------- ----------- ------------------------------------------------------
李四 30 2005-05-18 00:00:00.000
李四 150 2004-11-06 00:00:00.000 sum
===========
180
empname purchaseQty purchaseDay
-------------------- ----------- ------------------------------------------------------
王五 80 2005-08-12 00:00:00.000
王五 120 2006-02-25 00:00:00.000 sum
===========
200
(10 件処理されました)empid empname purchaseQty purchaseDay
----------- -------------------- ----------- ------------------------------------------------------
1 張三 100 2005-02-11 00:00:00.000
1 張三 50 2004-12-01 00:00:00.000
1 張三 300 2006-03-10 00:00:00.000 sum
===========
450
empid empname purchaseQty purchaseDay
----------- -------------------- ----------- ------------------------------------------------------
2 李四 150 2004-11-06 00:00:00.000
2 李四 30 2005-05-18 00:00:00.000 sum
===========
180
empid empname purchaseQty purchaseDay
----------- -------------------- ----------- ------------------------------------------------------
3 王五 80 2005-08-12 00:00:00.000
3 王五 120 2006-02-25 00:00:00.000 sum
===========
200
(10 件処理されました)empname purchaseQty purchaseDay