tableA
cus_no rp_no amt_bb bil_no sort (F代表收款,T代表预收款)
A rp001 100 null F
B rp002 300 rp003 F
B rp003 null null T
C rp004 500 null F
A rp005 800 rp006 F
A rp006 null null T
C rp007 200 null T
D rp008 350 null T
E rp009 400 null T
D rp010 150 null Ftableb
rp_no amt
rp003 -200
rp006 -500我想查出每个客户收款总和(不含预收的)
cus_no Amt
A 400
B 100
C 500
D 150
E 0/////////////////
说明A的400是由100+800-tableB表中的500得到的
B的100是由300-tableB中的200得到的
就是说如果表A的bil_no不为空,就到表B中找到对应得金额加上
cus_no rp_no amt_bb bil_no sort (F代表收款,T代表预收款)
A rp001 100 null F
B rp002 300 rp003 F
B rp003 null null T
C rp004 500 null F
A rp005 800 rp006 F
A rp006 null null T
C rp007 200 null T
D rp008 350 null T
E rp009 400 null T
D rp010 150 null Ftableb
rp_no amt
rp003 -200
rp006 -500我想查出每个客户收款总和(不含预收的)
cus_no Amt
A 400
B 100
C 500
D 150
E 0/////////////////
说明A的400是由100+800-tableB表中的500得到的
B的100是由300-tableB中的200得到的
就是说如果表A的bil_no不为空,就到表B中找到对应得金额加上
解决方案 »
- 使用SQL语句如何将txt数据导入表中
- 怎么用一段T-SQL语句实现下述功能;
- ??? 新开:现有两表:Tongji表、T1表,两表都为n行n列,请问如何更新T1表 ???
- 提示插入重复数据的语句该怎么写? C#.net+ sql 2000
- 这个查询该如何写
- sql 2000复写初始化时无法批量插入记录
- 问个SQL的简单问题
- setsqlselect()出错
- 救急!我的SQL6.5数据库损坏,情况是这样:系统数据库可以进去,但我自已建的数据库显示(suspect),数据库不能进去,我发现库和日志的
- 求救,请问众位高手怎样从powerdesigner中倒出数据库的结构,最好试文本格式的,谢谢了!
- 求一条HQL或者SQL,求count和sum
- 怪现象:com.microsoft.sqlserver.jdbc.SQLServerException: 用户 'sa' 登录失败。
if object_id('[ta]') is not null drop table [ta]
go
create table [ta]([cus_no] varchar(1),[rp_no] varchar(5),[amt_bb] int,[bil_no] varchar(5),[sort] varchar(1))
insert [ta]
select 'A','rp001',100,null,'F' union all
select 'B','rp002',300,'rp003','F' union all
select 'B','rp003',null,null,'T' union all
select 'C','rp004',500,null,'F' union all
select 'A','rp005',800,'rp006','F' union all
select 'A','rp006',null,null,'T' union all
select 'C','rp007',200,null,'T' union all
select 'D','rp008',350,null,'T' union all
select 'E','rp009',400,null,'T' union all
select 'D','rp010',150,null,'F'--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([rp_no] varchar(5),[amt] int)
insert [tb]
select 'rp003',-200 union all
select 'rp006',-500select [cus_no],sum(case a.[sort] when 'T' then 0 else a.[amt_bb] end +isnull(b.amt,0)) as Amt
from [ta] a
left join [tb] b on a.[bil_no]=b.[rp_no]
group by [cus_no]
/*
cus_no
------ -----------
A 400
B 100
C 500
D 150
E 0(5 行受影响)
*/
insert into tableA values('A', 'rp001', 100 ,null , 'F')
insert into tableA values('B', 'rp002', 300 ,'rp003', 'F')
insert into tableA values('B', 'rp003', null, null , 'T')
insert into tableA values('C', 'rp004', 500 ,null , 'F')
insert into tableA values('A', 'rp005', 800 ,'rp006', 'F')
insert into tableA values('A', 'rp006', null, null , 'T')
insert into tableA values('C', 'rp007', 200 ,null , 'T')
insert into tableA values('D', 'rp008', 350 ,null , 'T')
insert into tableA values('E', 'rp009', 400 ,null , 'T')
insert into tableA values('D', 'rp010', 150 ,null , 'F')
create table tableb(rp_no varchar(10),amt int)
insert into tableb values('rp003' ,-200)
insert into tableb values('rp006' ,-500)
goselect m.cus_no , m.amt_bb + isnull(n.amt,0) Amt from
(select cus_no , max(bil_no) bil_no, isnull(sum(amt_bb),0) amt_bb from tablea where sort = 'F' group by all cus_no) m
left join tableb n
on m.bil_no = n.rp_nodrop table tablea , tableb/*
cus_no Amt
---------- -----------
A 400
B 100
C 500
D 150
E 0(所影响的行数为 5 行)
*/
-- Author : htl258(Tony)
-- Date : 2010-04-03 11:36:22
-- Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)
--------------------------------------------------------------------------
--> 生成测试数据表:taIF NOT OBJECT_ID('[ta]') IS NULL
DROP TABLE [ta]
GO
CREATE TABLE [ta]([cus_no] NVARCHAR(10),[rp_no] NVARCHAR(10),[amt_bb] INT,[bil_no] NVARCHAR(10),[sort] NVARCHAR(10))
INSERT [ta]
SELECT 'A','rp001',100,NULL,'F' UNION ALL
SELECT 'B','rp002',300,'rp003','F' UNION ALL
SELECT 'B','rp003',NULL,NULL,'T' UNION ALL
SELECT 'C','rp004',500,NULL,'F' UNION ALL
SELECT 'A','rp005',800,'rp006','F' UNION ALL
SELECT 'A','rp006',NULL,NULL,'T' UNION ALL
SELECT 'C','rp007',200,NULL,'T' UNION ALL
SELECT 'D','rp008',350,NULL,'T' UNION ALL
SELECT 'E','rp009',400,NULL,'T' UNION ALL
SELECT 'D','rp010',150,NULL,'F'
GO
--SELECT * FROM [ta]--> 生成测试数据表:tbIF NOT OBJECT_ID('[tb]') IS NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([rp_no] NVARCHAR(10),[amt] INT)
INSERT [tb]
SELECT 'rp003',-200 UNION ALL
SELECT 'rp006',-500
GO
--SELECT * FROM [tb]-->SQL查询如下:select a.[cus_no],
sum(case [sort] when 'F' then a.amt_bb else 0 end +isnull(b.amt,0)) Amt
from ta a
left join tb b
on a.[bil_no]=b.[rp_no]
group by a.[cus_no]
/*
cus_no Amt
---------- -----------
A 400
B 100
C 500
D 150
E 0(5 行受影响)
*/