create table owner
(
xm varchar(20),
bh int,
xsrq datetime
); insert into owner values('张三',1,'2004-1-1');
insert into owner values('李四',2,'2004-2-1');
insert into owner values('王五',3,'2004-3-1');
create table money
(
fzbh int,
kxmc varchar(20),
fs varchar(10),
je int,
rq datetime
); insert into money values(1,'轿车','现金',10000,'2004-1-1');
insert into money values(1,'卡车','现金',20000,'2004-2-1');
insert into money values(2,'货车','按揭',30000,'2004-3-1');
insert into money values(2,'货车','欠款',40000,'2004-4-1');
insert into money values(3,'火车','现金',50000,'2004-5-1');
insert into money values(3,'飞机','按揭',60000,'2004-6-1');
insert into money values(3,'卡车','欠款',70000,'2004-7-1');select
XM,
bh,
轿车=SUM(case when kxmc='轿车' then je else 0 end),
卡车=SUM(case when kxmc='卡车' then je else 0 end),
货车=SUM(case when kxmc='货车' then je else 0 end),
火车=SUM(case when kxmc='火车' then je else 0 end),
飞机=SUM(case when kxmc='飞机' then je else 0 end),
现金=SUM(case when fs='现金' then je else 0 end),
按揭=SUM(case when fs='按揭' then je else 0 end),
欠款=SUM(case when fs='欠款' then je else 0 end)
from owner o join money m on o.bh=m.fzbh
group by XM,BHXM bh 轿车 卡车 货车 火车 飞机 现金 按揭 欠款
-------------------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
李四 2 0 0 70000 0 0 0 30000 40000
王五 3 0 70000 0 50000 60000 50000 60000 70000
张三 1 10000 20000 0 0 0 30000 0 0(3 行受影响)
(
xm varchar(20),
bh int,
xsrq datetime
); insert into owner values('张三',1,'2004-1-1');
insert into owner values('李四',2,'2004-2-1');
insert into owner values('王五',3,'2004-3-1');
create table money
(
fzbh int,
kxmc varchar(20),
fs varchar(10),
je int,
rq datetime
); insert into money values(1,'轿车','现金',10000,'2004-1-1');
insert into money values(1,'卡车','现金',20000,'2004-2-1');
insert into money values(2,'货车','按揭',30000,'2004-3-1');
insert into money values(2,'货车','欠款',40000,'2004-4-1');
insert into money values(3,'火车','现金',50000,'2004-5-1');
insert into money values(3,'飞机','按揭',60000,'2004-6-1');
insert into money values(3,'卡车','欠款',70000,'2004-7-1');select
XM,
bh,
轿车=SUM(case when kxmc='轿车' then je else 0 end),
卡车=SUM(case when kxmc='卡车' then je else 0 end),
货车=SUM(case when kxmc='货车' then je else 0 end),
火车=SUM(case when kxmc='火车' then je else 0 end),
飞机=SUM(case when kxmc='飞机' then je else 0 end),
现金=SUM(case when fs='现金' then je else 0 end),
按揭=SUM(case when fs='按揭' then je else 0 end),
欠款=SUM(case when fs='欠款' then je else 0 end)
from owner o join money m on o.bh=m.fzbh
group by XM,BHXM bh 轿车 卡车 货车 火车 飞机 现金 按揭 欠款
-------------------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
李四 2 0 0 70000 0 0 0 30000 40000
王五 3 0 70000 0 50000 60000 50000 60000 70000
张三 1 10000 20000 0 0 0 30000 0 0(3 行受影响)
(
xm varchar(20),
bh int,
xsrq datetime
); insert into owner values('张三',1,'2004-1-1');
insert into owner values('李四',2,'2004-2-1');
insert into owner values('王五',3,'2004-3-1');
create table money
(
fzbh int,
kxmc varchar(20),
fs varchar(10),
je int,
rq datetime
); insert into money values(1,'轿车','现金',10000,'2004-1-1');
insert into money values(1,'卡车','现金',20000,'2004-2-1');
insert into money values(2,'货车','按揭',30000,'2004-3-1');
insert into money values(2,'货车','欠款',40000,'2004-4-1');
insert into money values(3,'火车','现金',50000,'2004-5-1');
insert into money values(3,'飞机','按揭',60000,'2004-6-1');
insert into money values(3,'卡车','欠款',70000,'2004-7-1');select
XM,
bh,
轿车=case when SUM(case when kxmc='轿车' then je else 0 end)=0 then '' else rtrim(SUM(case when kxmc='轿车' then je else 0 end)) end,
卡车=case when SUM(case when kxmc='卡车' then je else 0 end)=0 then '' else rtrim(SUM(case when kxmc='卡车' then je else 0 end)) end ,
货车=case when SUM(case when kxmc='货车' then je else 0 end)=0 then '' else rtrim(SUM(case when kxmc='货车' then je else 0 end)) end ,
火车=case when SUM(case when kxmc='火车' then je else 0 end)=0 then '' else rtrim(SUM(case when kxmc='火车' then je else 0 end)) end,
飞机=case when SUM(case when kxmc='飞机' then je else 0 end)=0 then '' else rtrim(SUM(case when kxmc='飞机' then je else 0 end))end,
现金=case when SUM(case when fs='现金' then je else 0 end)=0 then '' else rtrim(SUM(case when fs='现金' then je else 0 end))end,
按揭=case when SUM(case when fs='按揭' then je else 0 end)=0 then '' else rtrim(SUM(case when fs='按揭' then je else 0 end))end,
欠款=case when SUM(case when fs='欠款' then je else 0 end)=0 then '' else rtrim(SUM(case when fs='欠款' then je else 0 end)) end
from owner o join money m on o.bh=m.fzbh
group by XM,BHXM bh 轿车 卡车 货车 火车 飞机 现金 按揭 欠款
-------------------- ----------- ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------
李四 2 70000 30000 40000
王五 3 70000 50000 60000 50000 60000 70000
张三 1 10000 20000 30000 (3 行受影响)
(3 行受影响)
(
xh varchar(20),
Bh int,
xsrq datetime
); insert into owner values('张三',1,'2004-1-1');
insert into owner values('李四',2,'2004-2-1');
insert into owner values('王五',3,'2004-3-1');
create table money
(
fzbh int,
kxmc varchar(20),
fs varchar(10),
je int,
rq datetime
); insert into money values(1,'轿车','现金',10000,'2004-1-1');
insert into money values(1,'卡车','现金',20000,'2004-2-1');
insert into money values(2,'货车','按揭',30000,'2004-3-1');
insert into money values(2,'货车','欠款',40000,'2004-4-1');
insert into money values(3,'火车','现金',50000,'2004-5-1');
insert into money values(3,'飞机','按揭',60000,'2004-6-1');
insert into money values(3,'卡车','欠款',70000,'2004-7-1');
SELECT XH,fzbh ,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 T JOIN [money] T1 ON T.BH=T1.fzbh
GROUP BY T.XH,T1.FZBHSELECT * FROM OWNER
SELECT * FROM MONEYDROP TABLE MONEYXH fzbh 轿车 卡车 货车 火车 飞机 现金 按揭 欠款
-------------------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
张三 1 10000 20000 0 0 0 30000 0 0
李四 2 0 0 70000 0 0 0 30000 40000
王五 3 0 70000 0 50000 60000 50000 60000 70000(所影响的行数为 3 行)