高分求一条SQL语句的实现!敬请帮忙,谢谢!

2008-01-14 06:48:05 123 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

ESP32 Arduino学习篇 (一) 引脚和串口的基本使用简单来讲arduino就是通过源代码控制开发板的信号输入输出从而实现对一些电子元件的控制，例如LED灯，蜂鸣器，按键arduino程序首先来讲arduino程序是基于C语言，而实现类似对于LED灯和蜂鸣器的简单控制只需简单C语言知识即可实现。arduino程序是基于函数的，而在arduino的库有很多函数，我们只需要调用即可。arduino的程序主体是**setup()函数和loop()**函数以及其它在使用过程中需要构建的函数：setup()函数。阅读详情

请问,以下要求,在SQL Server2000里可不可以使用一条SQL语句实现?要是可以,如何实现呢?谢谢!

表A的数据结构:
A_Code,A_Name,A_Amount1,A_Amount2,A_Amount3
1001,张三,1000,2000,3000
2001,李四,4000,5000,6000

表B的数据结构:
A_Code,B_Code,B_Amount
1001,10011,1000
1001,10012,2000
2001,20011,2000
2001,20012,3000
2001,20013,5000

要想通过SQL语句把表A和表B的数据合成,得出结果的表C数据结构:
A_Code,A_Name,C_TatolNum,C_Amount,C_Per
1001,张三,2,3000,50.00%
2001,李四,3,10000,66.67%

其中表C的数据来源:
C_TotalNum=Select count(*) From B Where B_Amount>0
C_Amount=Select Sum(B_Amount) From B
C_Per=B_Amount/(A_Amount1+A_Amount2+A_Amount3) //得出数据格式为50.05%

...全文

KEIL MDK 生成bin文件实战：C51与ARM双平台配置指南本文详细介绍了在KEIL MDK开发环境中，为C51和ARM双平台自动生成BIN文件的实战方法。针对C51平台，通过srec_cat.exe工具编写批处理脚本并集成到KEIL；针对ARM平台，则利用fromelf.exe工具直接配置。文章还提供了通用脚本方案和路径、变量、版本兼容性等常见问题的避坑指南，帮助开发者高效完成嵌入式程序部署。阅读详情

Sammo

疑难问题 22,298 成员 121,727 内容

+ 关注 + 加入社区

切换为时间正序

liangCK 2008-01-14

打赏
举报

我用do熊的数据



create table A(A_Code int, A_Name varchar(16), A_Amount1 int, A_Amount2 int, A_Amount3 int) 

insert A select 1001, '张三',1000,2000,3000 

union all select 2001,'李四',4000,5000,6000 



go

create table B(A_Code int, B_Code int, B_Amount int) 

insert B select 1001,10011,1000 

union all select 1001,10012,2000 

union all select 2001,20011,2000 

union all select 2001,20012,3000 

union all select 2001,20013,5000 

go



create view v_test

as 

select A.A_Code, A_Name

    ,C_TatolNum=Num

    ,C_Amount=Amount

    ,C_Per=rtrim(cast(Amount*100.0/(A_Amount1+A_Amount2+A_Amount3) as decimal(5,2)))+'%'

from A join 

(

    select A_Code, Num=sum(case when B_Amount>0 then 1 else 0 end), Amount=sum(B_Amount) from B group by A_Code

) B on A.A_Code=B.A_Code



go

Sammo 2008-01-14

打赏
举报

我想把这个查询语句做成视图来调用:
select A.A_Code, A_Name
,C_TatolNum=Num
,C_Amount=Amount
,C_Per=rtrim(cast(Amount*100.0/(A_Amount1+A_Amount2+A_Amount3) as decimal(5,2)))+'%'
from A join
(
select A_Code, Num=sum(case when B_Amount>0 then 1 else 0 end), Amount=sum(B_Amount) from B group by A_Code
) B on A.A_Code=B.A_Code

提示错误:查询设计器不支持 CASE SQL 构造。
怎么办呢?

Sammo 2008-01-14

打赏
举报

呵呵.因为我觉得给50分给dobear_0922不够,因为他写的确实很精简明了,所以就按七三分了.呵呵...不好意思,LiangCK,我再补分给你好吗?

liangCK 2008-01-14

打赏
举报

这分给得不好哦..
平分就好嘛.

liangCK 2008-01-14

打赏
举报

rtrim(cast(Amount*100.0/(A_Amount1+A_Amount2+A_Amount3) as decimal(5,2)))+'%'

忘了可以这样子的..害我绕了个大圈..看来得好好学习.

Sammo 2008-01-14

打赏
举报

强,谢谢两位!SQL使用得很精通哦.羡慕,请问哪里有SQL学习的好书啊?请推荐两本,谢谢!
结贴.

liangCK 2008-01-14

打赏
举报

是哦..
那case判断一下就好了.

..学习一下.

dobear_0922 2008-01-14

打赏
举报

其中表C的数据来源:
C_TotalNum=Select count(*) From B Where B_Amount> 0

-----------
小梁，你好像没用到这个条件，，，

liangCK 2008-01-14

打赏
举报

学习一下do熊的.的搞得太复杂了..

liangCK 2008-01-14

打赏
举报

create table ta(A_Code int,A_Name varchar(20),A_Amount1 int,A_Amount2 int,A_Amount3 int)

insert into ta select 1001,'张三',1000,2000,3000 

insert into ta select 2001,'李四',4000,5000,6000



create table tb(A_Code int,B_Code int,B_Amount int)

insert into tb select 1001,10011,1000 

insert into tb select 1001,10012,2000 

insert into tb select 2001,20011,2000 

insert into tb select 2001,20012,3000 

insert into tb select 2001,20013,5000



select a.A_Code,a.A_Name,b.C_TotalNum,b.B_Amount,

            cast(cast(b.B_Amount*100.0/c.num as decimal(10,2)) as varchar)+'%' C_Per

from ta a,(select A_Code,sum(B_Amount) B_Amount,count(*) C_TotalNum from tb group by A_Code) b

      ,(select A_Code,sum(num) num

        from(select A_Code,A_Amount1 as num from ta

           union all select A_Code,A_Amount2 from ta

           union all select A_Code,A_Amount3 from ta) d

         group by A_Code) c

where a.A_Code=b.A_Code and a.A_Code=c.A_Code



drop table ta,tb



/*

A_Code      A_Name               C_TotalNum  B_Amount    C_Per                           

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

1001        张三                   2           3000        50.00%

2001        李四                   3           10000       66.67%



（所影响的行数为 2 行）

*/

dobear_0922 2008-01-14

打赏
举报

create table A(A_Code int, A_Name varchar(16), A_Amount1 int, A_Amount2 int, A_Amount3 int) 

insert A select 1001, '张三',1000,2000,3000 

union all select 2001,'李四',4000,5000,6000 



go

create table B(A_Code int, B_Code int, B_Amount int) 

insert B select 1001,10011,1000 

union all select 1001,10012,2000 

union all select 2001,20011,2000 

union all select 2001,20012,3000 

union all select 2001,20013,5000 



go

select A.A_Code, A_Name

	,C_TatolNum=Num

	,C_Amount=Amount

	,C_Per=rtrim(cast(Amount*100.0/(A_Amount1+A_Amount2+A_Amount3) as decimal(5,2)))+'%'

from A join 

(

	select A_Code, Num=sum(case when B_Amount>0 then 1 else 0 end), Amount=sum(B_Amount) from B group by A_Code

) B on A.A_Code=B.A_Code



/*

A_Code      A_Name           C_TatolNum  C_Amount    C_Per

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

1001        张三               2           3000        50.00%

2001        李四               3           10000       66.67%



(2 row(s) affected)

*/



drop table A,B

liangCK 2008-01-14

打赏
举报

create table ta(A_Code int,A_Name varchar(20),A_Amount1 int,A_Amount2 int,A_Amount3 int)

insert into ta select 1001,'张三',1000,2000,3000 

insert into ta select 2001,'李四',4000,5000,6000



create table tb(A_Code int,B_Code int,B_Amount int)

insert into tb select 1001,10011,1000 

insert into tb select 1001,10012,2000 

insert into tb select 2001,20011,2000 

insert into tb select 2001,20012,3000 

insert into tb select 2001,20013,5000



select a.A_Code,a.A_Name,b.C_TotalNum,b.B_Amount,cast(b.B_Amount*100.0/c.num as decimal(10,2)) C_Per

from ta a,(select A_Code,sum(B_Amount) B_Amount,count(*) C_TotalNum from tb group by A_Code) b

      ,(select A_Code,sum(num) num

        from(select A_Code,A_Amount1 as num from ta

           union all select A_Code,A_Amount2 from ta

           union all select A_Code,A_Amount3 from ta) d

         group by A_Code) c

where a.A_Code=b.A_Code and a.A_Code=c.A_Code



drop table ta,tb



/*

A_Code      A_Name               C_TotalNum  B_Amount    C_Per        

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

1001        张三                   2           3000        50.00

2001        李四                   3           10000       66.67



（所影响的行数为 2 行）

*/

高分求一条SQL语句的实现!敬请帮忙,谢谢!

相关推荐