求一个sql 语句

2009-12-11 09:22:17 142 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

SQL学习笔记 | 02 SQL语句结构 SQL学习笔记 | 02 SQL语句结构一、表的导入1、表的命名2、导入步骤3、导入需注意二、标准SQL语法1、语句结构2、数据表的其他关键词3、SQL语句的分类一、表的导入 1、表的命名 2、导入步骤双击数据库后，点击导入字段名行：列名在第一行日期排序：选择年月日YMD 月份-int型价格-decimal型长度-整个字符串的比例-小数点后几位点击下一步后，点击开始，导入成... 阅读详情

主表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之间所有的客户

...全文

LVGL 实战：虚拟列表与对象复用机制在嵌入式GUI中的性能优化本文深入探讨LVGL虚拟列表与对象复用机制在嵌入式GUI中的性能优化实践。通过复用机制动态管理UI对象，大幅降低内存占用，实现无限滚动列表的流畅体验。文章详细解析核心算法、内存优化策略及跨平台适配技巧，帮助开发者在资源受限设备上高效处理大量数据展示。阅读详情

welcome403

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

Mr_Nice 2009-12-11

打赏
举报

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

--小F-- 2009-12-11

打赏
举报

难道是我理解错误?

--小F-- 2009-12-11

打赏
举报

----------------------------------------------------------------

-- 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-- 2009-12-11

打赏
举报

----------------------------------------------------------------

-- 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 行受影响)



*/

dao007 2009-12-11

打赏
举报

good!

dawugui 2009-12-11

打赏
举报

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

dawugui 2009-12-11

打赏
举报

--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

--小F-- 2009-12-11

打赏
举报

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

百年树人 2009-12-11

打赏
举报

先join 然后用between and 判断

求一个sql 语句

相关推荐