求一个sql 语句

[welcome403]

主表t1

id name
1 张三
2 李四

表s1
id号 tid 表示主表对应的id号 value1贷款1 value2 余额贷款2
id tid value1 value2
1 1 20 10
2 2 10 5

表s2
id号 tid 表示主表对应的id号 value1存款
id tid value1
1 1 30
2 2 2

表s3
id号 tid 表示主表对应的id号 value1股票
id tid value1
1 1 20
2 2 10

==========
求2个sql

1\ 求所有贷款和余额之差在1-30之间的所有客户
2\ 求股票+存款-贷款1之和在1-30之间所有的客户

[百年树人]

先join 然后用between and 判断

[--小F--]

select 
   a.name 
from
   t1 a,s1 b,s2 b,s3 c
where 
   a.id=b.tid
and
   a.id=c.tid
and 
   a.id=d.tid
having
   sum(a.value1)-sum(a.value2) between 1 and 30

[dawugui]

--1\ 求所有贷款和余额之差在1-30之间的所有客户
select t1.* from t1 , s1 where t1.id = s1.tid and s1.value1 - s1.value2 between 1 and 30

--2\ 求股票+存款-贷款1之和在1-30之间所有的客户

select t1.* from t1,
(
select tid , sum(value1) value1 from
(
select tid , -value1 from s1
union all
select tid , value1 from s2
union all
select tid , value1 from s3
) t group by tid
) m
where t1.id = m.tid and m.value1 between 1 and 30

[dawugui]

create table t1(id int, name varchar(10))
insert into t1 values(1 , '张三') 
insert into t1 values(2 , '李四') 
create table s1(id int, tid int, value1 int,value2 int)
insert into s1 values(1 , 1 , 20 , 10 )
insert into s1 values(2 , 2 , 10 , 5) 
create table s2(id int, tid int, value1 int)
insert into s2 values(1 , 1 , 30) 
insert into s2 values(2 , 2 , 2 )
create table s3(id int, tid int, value1 int)
insert into s3 values(1 , 1 , 20 )
insert into s3 values(2 , 2 , 10 )
go

--1\ 求所有贷款和余额之差在1-30之间的所有客户
select t1.* , s1.value1 - s1.value2 from t1 , s1 where t1.id = s1.tid and s1.value1 - s1.value2 between 1 and 30
/*
id          name                   
----------- ---------- ----------- 
1           张三         10
2           李四         5

（所影响的行数为 2 行）
*/

--2\ 求股票+存款-贷款1之和在1-30之间所有的客户

select t1.* , value1 from t1,
(
select tid , sum(value1) value1 from
(
select tid , -value1 value1 from s1
union all
select tid , value1 from s2
union all
select tid , value1 from s3
) t group by tid
) m
where t1.id = m.tid and m.value1 between 1 and 30
/*
id          name       value1      
----------- ---------- ----------- 
1           张三         30
2           李四         2

（所影响的行数为 2 行）
*/

drop table t1 , s1 , s2 , s3

[dao007]

good!

[--小F--]

----------------------------------------------------------------
-- Author  :fredrickhu(我是小F，向高手学习)
-- Date    :2009-12-11 09:32:56
-- Version:
--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 
--	Nov 24 2008 13:01:59 
--	Copyright (c) 1988-2005 Microsoft Corporation
--	Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[t1]
if object_id('[t1]') is not null drop table [t1]
go 
create table [t1]([id] int,[name] varchar(4))
insert [t1]
select 1,'张三' union all
select 2,'李四'
--> 测试数据：[s1]
if object_id('[s1]') is not null drop table [s1]
go 
create table [s1]([id] int,[tid] int,[value1] int,[value2] int)
insert [s1]
select 1,1,20,10 union all
select 2,2,10,5
--> 测试数据：[s2]
if object_id('[s2]') is not null drop table [s2]
go 
create table [s2]([id] int,[tid] int,[value1] int)
insert [s2]
select 1,1,30 union all
select 2,2,2
--> 测试数据：[s3]
if object_id('[s3]') is not null drop table [s3]
go 
create table [s3]([id] int,[tid] int,[value1] int)
insert [s3]
select 1,1,20 union all
select 2,2,10
--------------开始查询--------------------------
select 
   a.name 
from
   t1 a,s1 b,s2 c,s3 d
where 
   a.id=b.tid
and
   a.id=c.tid
and 
   a.id=d.tid
group by
   a.name
having
   sum(b.value1)-sum(b.value2) between 1 and 30
----------------结果----------------------------
/* name
----
李四
张三

(2 行受影响)

*/

[--小F--]

----------------------------------------------------------------
-- Author  :fredrickhu(我是小F，向高手学习)
-- Date    :2009-12-11 09:32:56
-- Version:
--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 
--	Nov 24 2008 13:01:59 
--	Copyright (c) 1988-2005 Microsoft Corporation
--	Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[t1]
if object_id('[t1]') is not null drop table [t1]
go 
create table [t1]([id] int,[name] varchar(4))
insert [t1]
select 1,'张三' union all
select 2,'李四'
--> 测试数据：[s1]
if object_id('[s1]') is not null drop table [s1]
go 
create table [s1]([id] int,[tid] int,[value1] int,[value2] int)
insert [s1]
select 1,1,20,10 union all
select 2,2,10,5
--> 测试数据：[s2]
if object_id('[s2]') is not null drop table [s2]
go 
create table [s2]([id] int,[tid] int,[value1] int)
insert [s2]
select 1,1,30 union all
select 2,2,2
--> 测试数据：[s3]
if object_id('[s3]') is not null drop table [s3]
go 
create table [s3]([id] int,[tid] int,[value1] int)
insert [s3]
select 1,1,20 union all
select 2,2,10
--------------开始查询--------------------------
select 
   a.name 
from
   t1 a,s1 b,s2 c,s3 d
where 
   a.id=b.tid
and
   a.id=c.tid
and 
   a.id=d.tid
group by
   a.name
having
   sum(d.value1)+sum(c.value1)-sum(b.value2) between 1 and 30
----------------结果----------------------------
/*name
----
李四

(1 行受影响)
*/

[--小F--]

难道是我理解错误?

[Mr_Nice]

路过学习！
dawugui
fredrickhu 你们都好快啊！