表1
ID MC JE
1 车费 50
1 餐费 30
1 车费 20
2 车费 10
2 通讯费 100
2 车费 50
2 书报费 100
3 车费 60表2
ID MC JE
1 车费 70
1 加班费 100
1 车费 10
2 车费 30
2 通讯费 100
2 车费 70
3 车费 60得到结果ID MC JE-1 JE-2 JE差(JE-1 减 JE-2,且不等于0)
1 车费 70 80 -10
2 车费 60 100 40
ID MC JE
1 车费 50
1 餐费 30
1 车费 20
2 车费 10
2 通讯费 100
2 车费 50
2 书报费 100
3 车费 60表2
ID MC JE
1 车费 70
1 加班费 100
1 车费 10
2 车费 30
2 通讯费 100
2 车费 70
3 车费 60得到结果ID MC JE-1 JE-2 JE差(JE-1 减 JE-2,且不等于0)
1 车费 70 80 -10
2 车费 60 100 40
解决方案 »
- 请教VisualStudio2010中附带的SQL如何使用链接语句
- 文本数据导入到数据库
- 数据库备份的SQL怎么写?我不想备份成.bak文件,而是备份成cyylsystem_Data和cyylsystem_log的sql怎么写
- 关于网站管理员权限的问题(详细请点击):
- 菜鸟问题:怎么判断 存储设备 是否已经存在???
- 如何进行这样的查询,三个表.谢谢!
- 排序规则冲突,如何解决?
- 优化
- Help ME!
- 请问那位大吓知道如何在installshield中通过odbc连接SQL SERVER的数据源
- 一般情况下,逻辑上的外键加索引,可以大大优化join和in还有where查询吗?
- 求写一存储过程-高手进
(
select id,mc,je as je1,0 as je2 from ta where mc='车费'
union all
select id,mc,0 as je1,je as je2 from tb where mc='车费'
) t
group by id,mc
having sum(je1-je2)!=0
insert into tb1 select 1,'车费',50
insert into tb1 select 1,'餐费',30
insert into tb1 select 1,'车费',20
insert into tb1 select 2,'车费',10
insert into tb1 select 2,'通讯费',100
insert into tb1 select 2,'车费',50
insert into tb1 select 2,'书报费',100
insert into tb1 select 3,'车费',60
create table tb2(ID int,MC nvarchar(10),JE int)
insert into tb2 select 1,'车费',70
insert into tb2 select 1,'加班费',100
insert into tb2 select 1,'车费',10
insert into tb2 select 2,'车费',30
insert into tb2 select 2,'通讯费',100
insert into tb2 select 2,'车费',70
insert into tb2 select 3,'车费',60
go
select a.id,a.mc,a.je1,b.je2,a.je1-b.je2 as je差 from(
select id,mc,sum(je)as je1 from tb1 where mc='车费' group by id,mc
)a inner join (
select id,mc,sum(je)as je2 from tb2 where mc='车费' group by id,mc
)b on a.id=b.id
go
drop table tb1,tb2
/*
id mc je1 je2 je差
----------- ---------- ----------- ----------- -----------
1 车费 70 80 -10
2 车费 60 100 -40
3 车费 60 60 0(3 行受影响)*/
insert into tb1 select 1,'车费',50
insert into tb1 select 1,'餐费',30
insert into tb1 select 1,'车费',20
insert into tb1 select 2,'车费',10
insert into tb1 select 2,'通讯费',100
insert into tb1 select 2,'车费',50
insert into tb1 select 2,'书报费',100
insert into tb1 select 3,'车费',60
create table tb2(ID int,MC nvarchar(10),JE int)
insert into tb2 select 1,'车费',70
insert into tb2 select 1,'加班费',100
insert into tb2 select 1,'车费',10
insert into tb2 select 2,'车费',30
insert into tb2 select 2,'通讯费',100
insert into tb2 select 2,'车费',70
insert into tb2 select 3,'车费',60
go
select a.id,a.mc,a.je1,b.je2,a.je1-b.je2 as je差 from(
select id,mc,sum(je)as je1 from tb1 where mc='车费' group by id,mc
)a inner join (
select id,mc,sum(je)as je2 from tb2 where mc='车费' group by id,mc
)b on a.id=b.id where a.je1-b.je2<>0
go
drop table tb1,tb2
/*
id mc je1 je2 je差
----------- ---------- ----------- ----------- -----------
1 车费 70 80 -10
2 车费 60 100 -40(2 行受影响)*/
use City
go
set nocount on
if object_id(N'tb1',N'U') is not null drop table tb1
go
if object_id(N'tb2',N'U') is not null drop table tb2
gocreate table tb1(ID int,MC nvarchar(10),JE int)
insert into tb1 select 1,'车费',50
insert into tb1 select 1,'餐费',30
insert into tb1 select 1,'车费',20
insert into tb1 select 2,'车费',10
insert into tb1 select 2,'通讯费',100
insert into tb1 select 2,'车费',50
insert into tb1 select 2,'书报费',100
insert into tb1 select 3,'车费',60
create table tb2(ID int,MC nvarchar(10),JE int)
insert into tb2 select 1,'车费',70
insert into tb2 select 1,'加班费',100
insert into tb2 select 1,'车费',10
insert into tb2 select 2,'车费',30
insert into tb2 select 2,'通讯费',100
insert into tb2 select 2,'车费',70
insert into tb2 select 3,'车费',60
goselect ID,MC,sum(je1) as [je1-1],sum(je2) as [je-2],sum(je1-je2) as [je-差] from
(
select ID,MC,JE as je1,0 as je2 from tb1 where mc='车费'
union all
select ID,MC,0 as je1,je as je2 from tb2 where mc='车费'
) t
group by ID,MC
having sum(je1-je2)!=0
drop table tb1
drop table tb2
ID MC je1-1 je-2 je-差
----------- ---------- ----------- ----------- -----------
1 车费 70 80 -10
2 车费 60 100 -40
insert into tb1 select 1,'车费',50
insert into tb1 select 1,'餐费',30
insert into tb1 select 1,'车费',20
insert into tb1 select 2,'车费',10
insert into tb1 select 2,'通讯费',100
insert into tb1 select 2,'车费',50
insert into tb1 select 2,'书报费',100
insert into tb1 select 3,'车费',60
create table tb2(ID int,MC nvarchar(10),JE int)
insert into tb2 select 1,'车费',70
insert into tb2 select 1,'加班费',100
insert into tb2 select 1,'车费',10
insert into tb2 select 2,'车费',30
insert into tb2 select 2,'通讯费',100
insert into tb2 select 2,'车费',70
insert into tb2 select 3,'车费',60
goselect id , mc,
sum(case px when 1 then je else 0 end) [JE-1],
sum(case px when 2 then je else 0 end) [JE-2],
sum(case px when 1 then je else -je end) [JE差]
from
(
select id , mc , je , px = 1 from tb1 where mc = '车费'
union all
select id , mc , je , px = 2 from tb2 where mc = '车费'
) t
group by id , mc
having sum(case px when 1 then je else -je end) <> 0drop table tb1 , tb2/*
id mc JE-1 JE-2 JE差
----------- ---------- ----------- ----------- -----------
1 车费 70 80 -10
2 车费 60 100 -40(所影响的行数为 2 行)
*/