sql查询写下来 [英] sql queries write down

问题描述

我有一张桌子

I have have a table

 name  mark
u      23   
h      38   
j      20   
p      83 

对于输出，我们需要

and for output we need

name  mark
u      23   38
h      38   20
j      20   83
p      83    0

如何在SQL中编写查询以执行此操作?

how do I write a quries in SQL to do this?

推荐答案

我在sql中创建函数

I create a function in sql

CREATE FUNCTION UpdateNameMark(@name varchar(50)) 
returns int
as

   BEGIN 
   declare @No int
   if (@name=''u'')
   select @No= 38
   else if (@name=''h'')
   select @No =20
   else if (@name=''j'')
   select @No= 83
   else if (@name=''p'')
   select @No= 0
   
   return @No
      
 END

如果要这个

if want this

select name , mark , NameMark(name) from <your table="" name=""> </your>

如果你想要这个

if you want this

updaye <tablename> set mark= NameMark(name)</tablename>

我创建了一个表，该表具有[ID] int列，[name] varchar，[marks] int列，并在其中选择了表名称[tbl_Rep]，并添加了您拥有的记录假如.使用下面的脚本，我得到的结果与您要求的相同.

因此，请检查以下脚本:

I have created a table with column [ID] int, [name] varchar, [marks] int where table name [tbl_Rep]is choosen, and add the records you have provided. Using the script below i am getting the same result you have asked for.

So, Check this script:

select * into #temp from tbl_Rep

alter table #temp add [marks2] int NULL

    DECLARE @count INT;
    Set @count = 0;
    DECLARE @max INT;
    Set @max = (Select Count(*) from tbl_Rep);
    print @count;
    WHILE (@count < @max)
    BEGIN
       update #temp Set [Marks2]=(select [Marks] from #temp where ID=@count+1) where ID=@count
       SET @count = (@count + 1)
    END
update #temp Set [Marks2]=0 where ID= (select MAX(ID) from #temp)
select * from #temp

drop table #temp

，一种更优化的方法是将表"A"的ID + 1连接到表"B"的ID而不是循环:

As an alternative, a more optimized way is to join ID+1 of table ''A'' to ID of table ''B'' instead of looping:

WITH
Test (ID, Name, Marks) AS
(
SELECT
ID,[name],[Marks]
FROM tbl_Rep
)

SELECT
A.[ID],A.[Name],A.[Marks],ISNULL(B.[Marks],0) as [Marks2]
FROM Test A
Left Join [tbl_Rep] B On A.[ID]+1=B.[ID]

这篇关于sql查询写下来的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！