表owner
xm bh xsrq
张三 1 2004-1-1
李四 2 2004-2-1
王五 3 2004-3-1表money
fzbh kxmc fs je 日期
1 轿车 现金 10000 2004-1-1
1 卡车 现金 20000 2004-2-1
2 货车 按揭 30000 2004-3-1
2 货车 欠款 40000 2004-4-13 火车 现金 50000 2004-5-1
3 飞机 按揭 60000 2004-6-1
3 卡车 欠款 70000 2004-7-1要求得到以下结果,该如何写SQL
2004-01-01 到 2004-04-01的数据xm bh 轿车 卡车 货车 火车 飞机 现金 按揭 欠款
张三 1 10000 20000 30000
李四 2 70000 30000 40000
合计 10000 20000 70000 30000 30000 40000
xm bh xsrq
张三 1 2004-1-1
李四 2 2004-2-1
王五 3 2004-3-1表money
fzbh kxmc fs je 日期
1 轿车 现金 10000 2004-1-1
1 卡车 现金 20000 2004-2-1
2 货车 按揭 30000 2004-3-1
2 货车 欠款 40000 2004-4-13 火车 现金 50000 2004-5-1
3 飞机 按揭 60000 2004-6-1
3 卡车 欠款 70000 2004-7-1要求得到以下结果,该如何写SQL
2004-01-01 到 2004-04-01的数据xm bh 轿车 卡车 货车 火车 飞机 现金 按揭 欠款
张三 1 10000 20000 30000
李四 2 70000 30000 40000
合计 10000 20000 70000 30000 30000 40000
sum(casen when kxmc='轿车' then je end) as 轿车,
sum(casen when kxmc='卡车' then je end) as 卡车,
sum(casen when kxmc='货车' then je end) as 货车,
sum(casen when kxmc='火车' then je end) as 火车,
sum(casen when kxmc='飞机' then je end) as 飞机,
sum(casen when kxmc='现金' then je end) as 现金,
sum(casen when kxmc='按揭' then je end) as 按揭,
sum(casen when kxmc='欠款' then je end) as 欠款,
from owner A,money B
where A.bh=B.fzbh and B.日期 between XXXX and XXXX
group by A.xm,A.bh with rollup
go
create table [owner]([xm] varchar(4),[bh] int,[xsrq] datetime)
insert [owner]
select '张三',1,'2004-1-1' union all
select '李四',2,'2004-2-1' union all
select '王五',3,'2004-3-1'
if object_id('[money]') is not null drop table [money]
go
create table [money]([fzbh] int,[kxmc] varchar(4),[fs] varchar(4),[je] int,[日期] datetime)
insert [money]
select 1,'轿车','现金',10000,'2004-1-1' union all
select 1,'卡车','现金',20000,'2004-2-1' union all
select 2,'货车','按揭',30000,'2004-3-1' union all
select 2,'货车','欠款',40000,'2004-4-1' union all
select 3,'火车','现金',50000,'2004-5-1' union all
select 3,'飞机','按揭',60000,'2004-6-1' union all
select 3,'卡车','欠款',70000,'2004-7-1'
godeclare @start varchar(10),@end varchar(10)
select @start='2004-01-01',
@end='2004-04-01'
select A.xm,A.bh,
sum(case when kxmc='轿车' then je else 0 end) as 轿车,
sum(case when kxmc='卡车' then je else 0 end) as 卡车,
sum(case when kxmc='货车' then je else 0 end) as 货车,
sum(case when kxmc='火车' then je else 0 end) as 火车,
sum(case when kxmc='飞机' then je else 0 end) as 飞机,
sum(case when fs='现金' then je else 0 end) as 现金,
sum(case when fs='按揭' then je else 0 end) as 按揭,
sum(case when fs='欠款' then je else 0 end) as 欠款
from owner A,money B
where A.bh=B.fzbh and convert(varchar(10),B.日期,120) between @start and @end
group by A.xm,A.bh
union all
select '合计',null,
sum(case when kxmc='轿车' then je else 0 end) as 轿车,
sum(case when kxmc='卡车' then je else 0 end) as 卡车,
sum(case when kxmc='货车' then je else 0 end) as 货车,
sum(case when kxmc='火车' then je else 0 end) as 火车,
sum(case when kxmc='飞机' then je else 0 end) as 飞机,
sum(case when fs='现金' then je else 0 end) as 现金,
sum(case when fs='按揭' then je else 0 end) as 按揭,
sum(case when fs='欠款' then je else 0 end) as 欠款
from owner A,money B
where A.bh=B.fzbh and convert(varchar(10),B.日期,120) between @start and @end/**
xm bh 轿车 卡车 货车 火车 飞机 现金 按揭 欠款
---- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
李四 2 0 0 70000 0 0 0 30000 40000
张三 1 10000 20000 0 0 0 30000 0 0
合计 NULL 10000 20000 70000 0 0 30000 30000 40000(3 行受影响)
**/
use PracticeDB
go
create table owner(xm varchar(5),bh int,xsrq date)
insert into owner
select '张三', 1, '2004-1-1' union all
select '李四', 2, '2004-2-1' union all
select '王五', 3, '2004-3-1'
go
create table money(fzbh int,kxmc varchar(5),fs varchar(5),js numeric(7),日期 date)
insert into money
select 1, '轿车', '现金', 10000, '2004-1-1' union all
select 1, '卡车', '现金', 20000, '2004-2-1' union all
select 2, '货车', '按揭', 30000, '2004-3-1' union all
select 2, '货车', '欠款', 40000, '2004-4-1' union all
select 3, '火车', '现金', 50000, '2004-5-1' union all
select 3, '飞机', '按揭', 60000, '2004-6-1' union all
select 3, '卡车', '欠款', 70000, '2004-7-1'
goselect * from owner
select * from money;with t
as
(
select o.xm,o.bh,轿车=sum( case kxmc when '轿车' then js else 0 end),
卡车=sum( case kxmc when '卡车' then js else 0 end),
货车=sum( case kxmc when '货车' then js else 0 end),
火车=sum( case kxmc when '火车' then js else 0 end),
飞机=sum( case kxmc when '飞机' then js else 0 end),
现金=sum( case fs when '现金' then js else 0 end),
按揭=sum( case fs when '按揭' then js else 0 end),
欠款=sum( case fs when '欠款' then js else 0 end)
from owner o join money m on o.bh=m.fzbh and o.xm<>'王五'
group by o.xm,o.bh
),t1
as
(
select top 9999999 * from t order by bh
union all
select '合计','',SUM(轿车),SUM(卡车),SUM(货车),SUM(火车),
SUM(飞机),SUM(现金),SUM(按揭),SUM(欠款)
from t
)
select * from t1xm bh 轿车 卡车 货车 火车 飞机 现金 按揭 欠款
张三 1 10000 20000 0 0 0 30000 0 0
李四 2 0 0 70000 0 0 0 30000 40000
合计 0 10000 20000 70000 0 0 30000 30000 40000