update 语句该如何写

2010-04-28 11:15:59 178 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

LLC谐振变换器中的软开关艺术：ZVS与ZCS的协同设计本文深入解析了LLC谐振变换器如何通过巧妙的电路设计，协同实现零电压开关（ZVS）与零电流开关（ZCS），从而大幅降低开关损耗与电磁干扰，提升电源效率。文章结合设计实战，详细阐述了谐振参数计算、器件选型及调试避坑指南，为高效、高功率密度电源设计提供了核心思路与解决方案。阅读详情

各位好！

我有两张表 tableA ,tableB 其中都有id 字段关联，tableA中的signA字段根据tableB中的signB字段倒序排列更新。
例如 tableB表中三条记录
ID signB
1001 1
1002 2
1003 3
1007 4

则tableA 表的要求更新为
ID signA
1007 1
1003 2
1002 3
1001 4

实现 tableB表中signB字段最大的数字，在tableA表中signA字段为最小数字

我用的是sql server 2000

...全文

AlphaFold-multimer 复合物结构预测是DeepMind开发的AlphaFold项目的一个扩展，旨在预测蛋白质多聚体的三维结构。蛋白质多聚体是由多个蛋白质亚单位相互组装而成的结构，如酶、膜蛋白复合物和病毒颗粒。理解多聚体的结构对于揭示蛋白质的功能和相互作用非常重要。阅读详情

easyboot

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

+ 关注 + 加入社区

切换为时间正序

好帅的一条鱼 2010-04-28

打赏
举报

学习00

yongnan_03 2010-04-28

打赏
举报

declare @TableA table (

	ID varchar(20),

	signA int)

declare @TableB table (

	ID varchar(20),

	signB int)	



Insert @TableA(ID, signA)

select '1001', 1

union all

select '1002', 2

union all

select '1003', 3

union all

select '1007', 4



Insert @TableB(ID, signB)

select '1001', 1

union all

select '1002', 2

union all

select '1003', 3

union all

select '1007', 4



Update a

Set a.signA = t.SignB

From

(Select m.ID, n.SignB

From (Select *, row_number() Over (Order by SignB) As SNo

	From @TableB) m,

	(Select *, row_number() Over (Order by SignB Desc) As SNo

	From @TableB) n

Where m.SNo = n.SNo) t, @TableA a

Where t.ID = a.ID



Select * From @TableA

Order By signA

nianran520 2010-04-28

打赏
举报

错了。。

nianran520 2010-04-28

打赏
举报

--> 测试数据：[tableA]

if object_id('[tableA]') is not null drop table [tableA]

create table [tableA]([ID] int,[signA] int)

go

insert [tableA]

select 1007,null union all

select 1003,null union all

select 1002,null union all

select 1001,null

--> 测试数据：[tableB]

if object_id('[tableB]') is not null drop table [tableB]

create table [tableB]([ID] int,[signB] int)

go

insert [tableB]

select 1001,1 union all 

select 1002,2 union all

select 1003,3 union all

select 1007,4



update t

set [signA] = (select top 1 [signB] from (select t.id - id as id,[signB] from [tableB]

where id >= t.id) r

order by id desc)

from [tableA] t 



select * from [tableA]





ID          signA

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

1007        4

1003        3

1002        2

1001        1



(4 行受影响)

--小F-- 2010-04-28

打赏
举报

多写了个top 100 percent



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

-- Author  :fredrickhu(小F，向高手学习)

-- Date    :2010-04-28 11:22:04

-- 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.1 (Build 2600: Service Pack 3)

--

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

--> 测试数据：[a]

if object_id('[a]') is not null drop table [a]

go 

create table [a]([ID] sql_variant,[signA] int)

insert [a]

select null,1 union all

select null,2 union all

select null,3 union all

select null,4

--> 测试数据：[b]

if object_id('[b]') is not null drop table [b]

go 

create table [b]([ID] int,[signB] int)

insert [b]

select 1001,1 union all

select 1002,2 union all

select 1003,3 union all

select 1007,4

--------------开始查询--------------------------

update 

  a

set

  id=b.id

from

  a,

  (select id,signB=(select count(1)+1 from b where id>t.id)  from b t )b

where

  a.signa=b.signB



select * from a

----------------结果----------------------------

/* ID                                                                                                                                                                                                                                                               signA

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

1007                                                                                                                                                                                                                                                             1

1003                                                                                                                                                                                                                                                             2

1002                                                                                                                                                                                                                                                             3

1001                                                                                                                                                                                                                                                             4



(4 行受影响)

*/

--小F-- 2010-04-28

打赏
举报

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

-- Author  :fredrickhu(小F，向高手学习)

-- Date    :2010-04-28 11:22:04

-- 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.1 (Build 2600: Service Pack 3)

--

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

--> 测试数据：[a]

if object_id('[a]') is not null drop table [a]

go 

create table [a]([ID] sql_variant,[signA] int)

insert [a]

select null,1 union all

select null,2 union all

select null,3 union all

select null,4

--> 测试数据：[b]

if object_id('[b]') is not null drop table [b]

go 

create table [b]([ID] int,[signB] int)

insert [b]

select 1001,1 union all

select 1002,2 union all

select 1003,3 union all

select 1007,4

--------------开始查询--------------------------

update 

  a

set

  id=b.id

from

  a,

  (select top 100 percent id,signB=(select count(1)+1 from b where id>t.id)  from b t )b

where

  a.signa=b.signB



select * from a

----------------结果----------------------------

/* ID                                                                                                                                                                                                                                                               signA

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

1007                                                                                                                                                                                                                                                             1

1003                                                                                                                                                                                                                                                             2

1002                                                                                                                                                                                                                                                             3

1001                                                                                                                                                                                                                                                             4



(4 行受影响)

*/

update 语句该如何写

相关推荐