This website requires Javascript to function properly. Please go to the setting of your web browser and enable Javascript for this website.

Ad by

推荐 OXIO 加拿大高速网络，最低月费仅$40. 使用推荐码 RCR37MB 可获得一个月的免费服务

Ad by

推荐 OXIO 加拿大高速网络，最低月费仅$40. 使用推荐码 RCR37MB 可获得一个月的免费服务

求MS-SQLSERVER SQL 语句

bobo123(bobo)

在一张表里(BRANCHMASTER) 有两个主字段(CHARTERNUMBER int,BRANCHID int)
执行 select charterNumber,branchId from branchMaster where charterNumber=21
结果如下
(21,1) (21,2),(21,3)

执行 select charterNumber,branchId from branchMaster where charterNumber=22
结果如下
(22,1) (22,2),(22,3)

那么现在想把CHARTERNUMBER=21 的记录合并到 CHARTERNUMBER=22 的记录中去.合并的规则如下:
1:CHARTERNUMBER 值为21 更新为22
2:BRANCHID 要重新排序,从1开始

应用上边的规则那么系统中的记录变化如下:
(22,1)->(22,1)
(22,2)->(22,2)
(22,3)->(22,3)
(21,1)->(22,4)
(21,2)->(22,5)
(21,3)->(22,6)

(#2552732@0)
Last Updated: 2005-10-14
This post has been archived. It cannot be replied.

Report

Replies, comments and Discussions:

工作学习 / 专业技术讨论 / 求MS-SQLSERVER SQL 语句 -bobo123(bobo); 2005-10-14 {584} (#2552732@0)
在一张表里(BRANCHMASTER) 有两个主字段(CHARTERNUMBER int,BRANCHID int)
执行 select charterNumber,branchId from branchMaster where charterNumber=21
结果如下
(21,1) (21,2),(21,3)

执行 select charterNumber,branchId from branchMaster where charterNumber=22
结果如下
(22,1) (22,2),(22,3)

那么现在想把CHARTERNUMBER=21 的记录合并到 CHARTERNUMBER=22 的记录中去.合并的规则如下:
1:CHARTERNUMBER 值为21 更新为22
2:BRANCHID 要重新排序,从1开始

应用上边的规则那么系统中的记录变化如下:
(22,1)->(22,1)
(22,2)->(22,2)
(22,3)->(22,3)
(21,1)->(22,4)
(21,2)->(22,5)
(21,3)->(22,6)

UPDATE branchMaster set charterNumber = 22, branchId = branchId + (SELECT MAX(branchId) FROM branchMaster WHERE charterNumber = 22) WHERE charterNumber = 21 -guestagain(guest again); 2005-10-14 (#2553132@0)

对不起，我提的问题不清楚，导致误解 -bobo123(bobo); 2005-10-14 {334} (#2553740@0)
其实我是想写个存储过程
MergeBranchMaster
(@From_CharterNumber int ,@To_CharterNumber int)

在上贴的例子中，@From_CharterNumber=21,@To_CharterNumber=22

另外除了知道CHARTERNUMBER 和BRANCHID 是键值外，BRANCHID 的值事先是不可知的，有可能其他的BRANCHID 原来就没按顺序排列，但希望合并后，能够对BRANCHID 重新排序，值为1，2，3，。。。

用cursor肯定可以。不过我觉得用带identity的临时表更简单。 -holdon(again); 2005-10-14 {12} (#2553878@0)
create table
well,temp table will help for sure. Since CHARTERNUMBER 和BRANCHID 是键值, then under one CHARTERNUMBER, all the BRANCHID is unique. You can use 2 statements to do it. It's for fun only, not a good solution. -guestagain(guest again); 2005-10-14 {628} (#2553937@0)
First, combine data

UPDATE branchMaster set charterNumber = @To_CharterNumber, branchID = branchId + (SELECT MAX(branchID) FROM branchMaster WHERE charterNumber = @To_CharterNumber) WHERE charterNumber = @From_CharterNumber

Second, sort branchID

UPDATE branchMaster SET branchID = t2.id
FROM branchMaster t1,
(SELECT a.branchID, id = COUNT(a.branchID)
FROM branchMaster a,branchMaster b
WHERE a.charterNumber = b.charterNumber
AND a.branchID >= b.branchID
AND a.charterNumber = @To_CharterNumber
GROUP BY a.branchID) t2
WHERE t1.branchID = t2.branchID
AND t1.charterNumber = @To_CharterNumber

@Calgary

求MS-SQLSERVER SQL 语句

Replies, comments and Discussions:

More Topics