有表如下
a b c
001 收入 5000
001 费用 2000
001 合计 3000
002 收入 6000
002 费用 3000
002 合计 3000
003 收入 4000
003 费用 1000
003 合计 2000
...
合计=收入-费用
求一语句 查找出当b=收入时c字段不正确的记录
a b c
001 收入 5000
001 费用 2000
001 合计 3000
002 收入 6000
002 费用 3000
002 合计 3000
003 收入 4000
003 费用 1000
003 合计 2000
...
合计=收入-费用
求一语句 查找出当b=收入时c字段不正确的记录
select a,sum(case b when '收入' then c else -c end)
from your_table
group by a
having sum(case b when '收入' then c else -c end) <> 0
use PracticeDB
go
if exists (select 1 from sysobjects where name='tb_a')
drop table tb_a
go
create table tb_a (a varchar(10), b varchar(10),c numeric(10))
go
insert into tb_a
select '001', '收入' ,5000 union all
select '001', '费用' ,2000 union all
select '001', '合计' ,3000 union all
select '002', '收入' ,6000 union all
select '002', '费用' ,3000 union all
select '002', '合计' ,3000 union all
select '003', '收入' ,4000 union all
select '003', '费用' ,1000 union all
select '003', '合计' ,2000
合计=收入-费用
求一语句 查找出当b=收入时c字段不正确的记录
select * from tb_a;with t
as
(
select a,sum(case b when '收入' then c else 0 end) [收入],
sum(case b when '费用' then c else 0 end) [费用],
sum(case b when '合计' then c else 0 end) [合计]
from tb_a
group by a
)
select a.*
from tb_a a join t on a.a=t.a
where t.收入<>t.费用+t.合计
a b c
003 收入 4000
003 费用 1000
003 合计 2000;with t
as
(
select a,sum(case b when '收入' then c else 0 end) [收入],
sum(case b when '费用' then c else 0 end) [费用],
sum(case b when '合计' then c else 0 end) [合计]
from tb_a
group by a
)
select a.*
from tb_a a join t on a.a=t.a
where t.收入<>t.费用+t.合计 and a.b='收入'a b c
003 收入 4000
当b=合计时 c=(c在b=收入时的值)-(c在b=费用时的值)找出不满足此条件的记录
--if exists (select 1 from sysobjects where name='ta')
if object_id('ta') is not null drop table ta
go
create table ta (a varchar(10), b varchar(10),c numeric(10))
go
insert into ta
select '001', '收入' ,5000 union all
select '001', '费用' ,2000 union all
select '001', '合计' ,3000 union all
select '002', '收入' ,6000 union all
select '002', '费用' ,3000 union all
select '002', '合计' ,3000 union all
select '003', '收入' ,4000 union all
select '003', '费用' ,1000 union all
select '003', '合计' ,2000select * from ta x
join ta y on x.a=y.a
join ta z on x.a=z.a
where x.b='收入' and y.b='费用' and z.b='合计' and z.c<>x.c-y.c
001 收入 5000
001 费用 2000
001 合计 3000
002 收入 6000
002 费用 3000
002 合计 3000
003 收入 4000
003 费用 1000
003 合计 2000
...
合计=收入-费用
求一语句 查找出当b=收入时c字段不正确的记录
select tmp.a,[合计]=(select sum(c) from tb where a=tmp.a and b='收入')-(select sum(c) from tb where a=tmp.a and b='费用'),tmp.c from tb as tmp where rtrim(ltrim(tmp.b))='合计' and [合计]<>tmp.c
drop table tb_a
go
create table tb_a (a varchar(10), b varchar(10),c numeric(10))
go
insert into tb_a
select '001', '收入' ,5000 union all
select '001', '费用' ,2000 union all
select '001', '合计' ,3000 union all
select '002', '收入' ,6000 union all
select '002', '费用' ,3000 union all
select '002', '合计' ,3000 union all
select '003', '收入' ,4000 union all
select '003', '费用' ,1000 union all
select '003', '合计' ,2000select a.a, a.b, a.c from tb_a a where a.a=(select a from
(select a, sum(case b when '收入' then c else -c end) total
from tb_a group by a having sum(case b when '收入' then c else -c end)<>0)as t) and a.b='收入'/*
a b c
------ ------- --------
003 收入 4000
*/