如今有两个表,一个是成绩表,字段如下:姓名varchar(20),语文成绩number,数学成绩number;另一个表是评语表,字段如下:姓名varchar(2),评语varchar(10),现在需要根据成绩表的成绩插入评语到评语表,根据语文成绩和数学成绩相加,如果总成绩>=90即插入"优秀",如果90>总成绩>=80即插入"良",如果80>总成绩>=60即插入"普通",如果总成绩<60即插入"差",如何写SQL语句

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

--创建存储过程
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

你用这个存储过程试试看

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

那怎么写!?

用 case语句！

可以架一个循环嘛，用变量的形式来增加
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

*****************************************************
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 编辑 ]

[本代码可以直接执行]
---------------------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 编辑 ]

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