将一个表两个列相加插入到另一个表上 SQL语句 - 编程论坛

#2

Aviva_Wang2013-05-19 20:48

这个不是一条sql就能解决的问题吧，你一个函数吧

#3

华丽之词2013-05-20 11:52

--创建存储过程
create procedure ping_proc
as
    declare @name varchar(20)
    select @name = name from score
    declare @ping int
    select @ping = yuwen+shuxu from score
    if(@ping >= 90)
        insert into pingyu(name,pingyu) values(@name,'优秀')
    if(@ping >= 80)
        insert into pingyu(name,pingyu) values(@name,'良')
    if(@ping >= 60)
        insert into pingyu(name,pingyu) values(@name,'普通')
    else
        insert into pingyu(name,pingyu) values (@name,'差')
go
--执行存储过程
exec ping_proc

你用这个存储过程试试看

#4

czq09042013-05-21 15:32

回复 3楼华丽之词

用了这个怎么就只能插入一条呢?

#5

czq09042013-05-21 15:32

回复 2楼 Aviva_Wang

那怎么写!?

#6

何时是月明2013-05-23 14:19

用 case语句！

#7

chenpengfei12013-06-18 16:59

可以架一个循环嘛，用变量的形式来增加
create procedure ping_proc
as
begin
    declare @iint，
    set@i=0，
    while i<10
     @i=@i+1，
    declare @name varchar(20)
    select @name = name from score
    declare @ping int
    select @ping = yuwen+shuxu from score
    if(@ping >= 90)
        insert into pingyu(name,pingyu) values(@name,'优秀')
    if(@ping >= 80)
        insert into pingyu(name,pingyu) values(@name,'良')
    if(@ping >= 60)
        insert into pingyu(name,pingyu) values(@name,'普通')
    else
        insert into pingyu(name,pingyu) values (@name,'差')
end
go
--执行存储过程
exec ping_proc

#8

3037709572013-06-26 15:57

*****************************************************
The following is the complete code that can be
directly executed to get results what you wanted.
If you have any questions please send me a message.
Here is an exeample for this question，Good Luck!
*****************************************************

程序代码：

---------------------Create table named YourSourceTable-------------------------------------------------------
IF EXISTS (SELECT * FROM sys.tables WHERE object_id = OBJECT_ID(N'[dbo].[YourSourceTable]') AND type in (N'U'))
DROP TABLE [dbo].[YourSourceTable]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create Table YourSourceTable
(
  Name varchar(50),
  English int,
  Chinese int
)
-----------------Insert data into table named YourSourceTable---------
delete from YourSourceTable
Insert into YourSourceTable(Name,English,Chinese) values('赵钱',98,89)
Insert into YourSourceTable(Name,English,Chinese) values('孙李',78,98)
Insert into YourSourceTable(Name,English,Chinese) values('张三',56,58)
Insert into YourSourceTable(Name,English,Chinese) values('李四',75,68)
Insert into YourSourceTable(Name,English,Chinese) values('周五',86,79)
Insert into YourSourceTable(Name,English,Chinese) values('郑王',89,76)
-----------------Create table and Insert data into YourSourceTable ---
IF EXISTS (SELECT * FROM sys.tables WHERE object_id = OBJECT_ID(N'[dbo].[YourAnotherSourceTable]') AND type in (N'U'))
DROP TABLE [dbo].[YourAnotherSourceTable]
GO
select Name,English+Chinese as Totals into YourAnotherSourceTable
from YourSourceTable
Go
------------------To see data from YourSourceTable and YourAnotherSourceTable--------
select * from YourSourceTable
select * from YourAnotherSourceTable
---------------------------------The  End--------------------------------------------

Result:

只有本站会员才能查看附件，请登录

[ 本帖最后由 303770957 于 2013-6-26 16:05 编辑 ]

#9

3037709572013-06-26 16:24

程序代码：

[本代码可以直接执行]
---------------------Create table named [成绩表]------------------------------------------------------
IF EXISTS (SELECT * FROM sys.tables WHERE object_id = OBJECT_ID(N'[dbo].[成绩表]') AND type in (N'U'))
DROP TABLE [dbo].[成绩表]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create Table [成绩表]
(
  [姓名] varchar(20),
  [语文成绩] int,
  [数学成绩] int
)
-----------------Insert data into table named [成绩表]---------
delete from [成绩表]
Insert into [成绩表]([姓名],[语文成绩],[数学成绩]) values('赵钱',38,69)
Insert into [成绩表]([姓名],[语文成绩],[数学成绩]) values('孙李',29,46)
Insert into [成绩表]([姓名],[语文成绩],[数学成绩]) values('张三',36,48)
Insert into [成绩表]([姓名],[语文成绩],[数学成绩]) values('李四',35,28)
Insert into [成绩表]([姓名],[语文成绩],[数学成绩]) values('周五',56,39)
Insert into [成绩表]([姓名],[语文成绩],[数学成绩]) values('郑王',48,40)
-----------------Create table and Insert data into [评语表]---
IF EXISTS (SELECT * FROM sys.tables WHERE object_id = OBJECT_ID(N'[dbo].[评语表]') AND type in (N'U'))
DROP TABLE [dbo].[评语表]
GO
select [姓名],[语文成绩]+[数学成绩] as [总成绩],
Case
When [语文成绩]+[数学成绩] >=90 then '优秀'
when 90>[语文成绩]+[数学成绩] and [语文成绩]+[数学成绩]>=80 then '良'
when 80>[语文成绩]+[数学成绩] and [语文成绩]+[数学成绩]>=60 then '普通'
else '差'
end as [评语]
into [评语表] from [成绩表]
Go
------------------To see data from [成绩表] and [评语表]--------
select * from [成绩表]
select * from [评语表]
---------------------------------The  End--------------------------------------------

Result:
[attach]71729[/attach]

[ 本帖最后由 303770957 于 2013-9-10 10:20 编辑 ]

#10

yaner0032013-08-27 21:38

好难啊！十六浦娱|乐|城同乐城

#11

西风独自凉2013-08-31 10:13

一句

insert into 评语表
select 姓名，case when 语文+数学 >=90 then '优秀' when 语文+数学 >=80
when 语文+数学 >=60 then '普通' else '差' end
from 成绩表

#12

李呆呆2013-09-07 18:26

看完之后，我凌乱了，都不知道怎么学这数据库，老师平时都不教怎么做，也不讲为什么那么做。。。。。

#13

zszs2013-09-10 09:50

不用这么复杂的
一个语句搞定
假定成绩表是CheJi 字段有Name,Yu,Shu 评语表为Comment ，字段有Name,Ping
符合提问者的答案为:
insert into [Comment]([Name],[Ping])
select [Name],case when Yu+Shu>=90 then '优秀'
when Yu+Shu>=80 then '良'
when Yu+Shu>=60 then '普通'
else '差' end
from ChengJi

#14

3037709572013-09-10 10:14

回复 13楼 zszs

我不知道楼上的是怎么看帖子的，没有准备工作，你的SQL语句能直接运行吗？
你有没有看清我帖子里面是怎么说的，我的SQL是可以直接运行的，你一个假定就可以了吗？
实际最核心的代码是下面这句，你看看和你写的SQL有多大出处？但是区别就不一样了，
你写的SQL在sql server能直接运行吗？我的就行！我是从建表，插入数据，开始做起的。你呢？
没有这些准备工作你的查询怎么能得出结果来？
-----------------------------------------------------------------
select [姓名],[语文成绩]+[数学成绩] as [总成绩],
Case
When [语文成绩]+[数学成绩] >=90 then '优秀'
when 90>[语文成绩]+[数学成绩] and [语文成绩]+[数学成绩]>=80 then '良'
when 80>[语文成绩]+[数学成绩] and [语文成绩]+[数学成绩]>=60 then '普通'
else ‘差’
end as [评语]
into [评语表] from [成绩表]
Go
------------------------------------------------------------------

[ 本帖最后由 303770957 于 2013-9-10 10:16 编辑 ]

#15

hbyjw2013-09-13 10:06

insert into XX_XX (姓名 ,评语)
case when sum(语文成绩+数学成绩)>90 then '优秀'

#16

hbyjw2013-09-13 10:06

insert into XX_XX (姓名 ,评语)
case when sum(语文成绩+数学成绩)>90 then '优秀'

#17

hbyjw2013-09-13 10:06

insert into XX_XX (姓名 ,评语)
case when sum(语文成绩+数学成绩)>90 then '优秀'

#18

hbyjw2013-09-13 10:06

insert into XX_XX (姓名 ,评语)
case when sum(语文成绩+数学成绩)>90 then '优秀'

#19

清风幽闲2014-03-04 15:56

UPDATE 评语 SET 评语=
CASE
WHEN A.语文+A.数学 >180 THEN '优秀'
WHEN A.语文+A.数学>160 THEN '良好'
WHEN A.语文+A.数学>140 THEN '一般'
ELSE '差'
END
FROM 成绩表 A JOIN 评语 B ON A.学生编号=B.学生编号