表1:
mid mcode
101 A-001
102 B-001
103表2:
meid mid mecode
1 101
2 101
3 101
4 102
5 102
6 103结果:
meid mid mecode
1 101 A-101-0001
2 101 A-101-0002
3 101 A-101-0003
4 102 B-102-0001
5 102 B-102-0001
6 103如何根据表2与表1的外键约束(mid)分组然后按顺序更新表2的mecode,SQL应该如何写...
mid mcode
101 A-001
102 B-001
103表2:
meid mid mecode
1 101
2 101
3 101
4 102
5 102
6 103结果:
meid mid mecode
1 101 A-101-0001
2 101 A-101-0002
3 101 A-101-0003
4 102 B-102-0001
5 102 B-102-0001
6 103如何根据表2与表1的外键约束(mid)分组然后按顺序更新表2的mecode,SQL应该如何写...
meid mid mecode
1 101 A-101-0001
2 101 A-101-0002
3 101 A-101-0003
4 102 B-102-0001
5 102 B-102-0002
6 103
--> 测试数据:[TA]
if object_id('[TA]') is not null drop table [TA]
create table [TA]([mid] int,[mcode] varchar(5))
insert [TA]
select 101,'A-001' union all
select 102,'B-001' union all
select 103,null
--> 测试数据:[TB]
if object_id('[TB]') is not null drop table [TB]
create table [TB]([meid] int,[mid] int,[mecode] sql_variant)
insert [TB]
select 1,101,null union all
select 2,101,null union all
select 3,101,null union all
select 4,102,null union all
select 5,102,null union all
select 6,103,null
select meid,
mid=A.mid,
mecode=isnull(mcode+'-'+right('00000'+rtrim((select count(1) from TB where mid=B.mid and meid<=B.meid)),4),'') from [TA] A join TB B
on A.mid=B.mid
/*
meid mid mecode
----------- ----------- --------------
1 101 A-001-0001
2 101 A-001-0002
3 101 A-001-0003
4 102 B-001-0001
5 102 B-001-0002
6 103 (所影响的行数为 6 行)*/drop table TA,TB
if object_id('[ta]') is not null drop table [ta]
go
create table [ta]([mid] int,[mcode] varchar(5))
insert [ta]
select 101,'A-001' union all
select 102,'B-001' union all
select 103,null
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([meid] int,[mid] int,[mecode] varchar(50))
insert [tb]
select 1,101,null union all
select 2,101,null union all
select 3,101,null union all
select 4,102,null union all
select 5,102,null union all
select 6,103,null
---更新---
update b
set mecode=a.mcode+right('0000'+ltrim((select count(1)+1 from tb where mid=b.mid and meid<b.meid)),4)
from ta a,tb b
where a.mid=b.mid and a.mcode is not null---查询---
select * from tb---结果---
meid mid mecode
----------- ----------- --------------------------------------------------
1 101 A-0010001
2 101 A-0010002
3 101 A-0010003
4 102 B-0010001
5 102 B-0010002
6 103 NULL(所影响的行数为 6 行)
update b
set mecode=a.mcode+'-'+right('0000'+ltrim((select count(1)+1 from tb where mid=b.mid and meid<b.meid)),4)
from ta a,tb b
where a.mid=b.mid and a.mcode is not null
select * from tb/**
meid mid mecode
----------- ----------- --------------------------------------------------
1 101 A-001-0001
2 101 A-001-0002
3 101 A-001-0003
4 102 B-001-0001
5 102 B-001-0002
6 103 NULL(所影响的行数为 6 行)
**/
insert into tb1 values('101', 'A-001')
insert into tb1 values('102', 'B-001')
insert into tb1 values('103', 'C-001')
create table tb2(meid int,mid varchar(10),mecode varchar(20))
insert into tb2 values(1 , '101',null)
insert into tb2 values(2 , '101',null)
insert into tb2 values(3 , '101',null)
insert into tb2 values(4 , '102',null)
insert into tb2 values(5 , '102',null)
insert into tb2 values(6 , '103',null)
goupdate tb2
set mecode = tb1.mcode + '-' + right('000'+cast((select count(1) from tb2 where mid = t.mid and meid < t.meid) + 1 as varchar),4)
from tb2 t , tb1
where t.mid = tb1.midselect * from tb2
drop table tb1 , tb2 /*
meid mid mecode
----------- ---------- --------------------
1 101 A-001-0001
2 101 A-001-0002
3 101 A-001-0003
4 102 B-001-0001
5 102 B-001-0002
6 103 C-001-0001(所影响的行数为 6 行)
*/
--> Author : js_szy
--> Target : 各位大大,小卒就是想要一朵花
--> Date : 2009-12-02 16:33:02
--> Version: SQL Server 2005
---------------------------------------------
--> 测试数据: @ta
declare @ta table (mid int,mcode varchar(5))
insert into @ta
select 101,'A-001' union all
select 102,'B-001' union all
select 103,null
--> 测试数据: @tb
declare @tb table (meid int,mid int,mecode sql_variant)
insert into @tb
select 1,101,null union all
select 2,101,null union all
select 3,101,null union all
select 4,102,null union all
select 5,102,null union all
select 6,103,null
select meid,b.mid,mecode=mcode+'-'+right('0000'+ltrim((select count(1) from @tb where mid=b.mid and meid<=b.meid)),4)
from @ta a,
@tb b
where a.mid=b.midmeid mid mecode
----------- ----------- --------------
1 101 A-001-0001
2 101 A-001-0002
3 101 A-001-0003
4 102 B-001-0001
5 102 B-001-0002
6 103 NULL(6 行受影响)
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-12-02 16:33: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.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[a]
if object_id('[a]') is not null drop table [a]
go
create table [a]([mid] int,[mcode] varchar(5))
insert [a]
select 101,'A-001' union all
select 102,'B-001' union all
select 103,null
--> 测试数据:[b]
if object_id('[b]') is not null drop table [b]
go
create table [b]([meid] int,[mid] int,[mecode] sql_variant)
insert [b]
select 1,101,null union all
select 2,101,null union all
select 3,101,null union all
select 4,102,null union all
select 5,102,null union all
select 6,103,null
--------------开始查询--------------------------
select
b.meid,b.mid,
mecode=left(a.mcode,charindex('-',a.mcode))+ltrim(b.mid)+'000'+ltrim(row_number()over(partition by b.mid order by getdate()))
from
a,b
where
a.mid=b.mid
----------------结果----------------------------
/* meid mid mecode
----------- ----------- --------------------------------------------
1 101 A-1010001
2 101 A-1010002
3 101 A-1010003
4 102 B-1020001
5 102 B-1020002
6 103 NULL(6 行受影响)
*/
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-12-02 16:33: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.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[a]
if object_id('[a]') is not null drop table [a]
go
create table [a]([mid] int,[mcode] varchar(5))
insert [a]
select 101,'A-001' union all
select 102,'B-001' union all
select 103,null
--> 测试数据:[b]
if object_id('[b]') is not null drop table [b]
go
create table [b]([meid] int,[mid] int,[mecode] sql_variant)
insert [b]
select 1,101,null union all
select 2,101,null union all
select 3,101,null union all
select 4,102,null union all
select 5,102,null union all
select 6,103,null
--------------开始查询--------------------------
select
b.meid,b.mid,
mecode=left(a.mcode,charindex('-',a.mcode))+ltrim(b.mid)+'-'+right('0000'+ltrim(row_number()over(partition by b.mid order by getdate())),3)
from
a,b
where
a.mid=b.mid
----------------结果----------------------------
/* meid mid mecode
----------- ----------- ------------------------
1 101 A-101-001
2 101 A-101-002
3 101 A-101-003
4 102 B-102-001
5 102 B-102-002
6 103 NULL(6 行受影响)
*/
-- Author : HappyFlyStone
-- Date : 2009-12-02 16:35:27
-- Version: Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86)
-- Apr 14 2006 01:12:25
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
--
-------------------------------------------------------------------------- Test Data: ta
IF OBJECT_ID('[ta]') IS NOT NULL
DROP TABLE [ta]
Go
CREATE TABLE ta([mid] INT,[mcode] NVARCHAR(5))
Go
INSERT INTO ta
SELECT 101,'A-001' UNION ALL
SELECT 102,'B-001' UNION ALL
SELECT 103,''
GO
-- Test Data: tb
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
Go
CREATE TABLE tb([meid] INT,[mid] INT ,mecode varchar(10))
Go
INSERT INTO tb([meid],[mid])
SELECT 1,101 UNION ALL
SELECT 2,101 UNION ALL
SELECT 3,101 UNION ALL
SELECT 4,102 UNION ALL
SELECT 5,102 UNION ALL
SELECT 6,103
GO
--Start
declare @i int ,@j int
update tb
set mecode = case when ta.[mcode] = '' then ''
else ta.[mcode]+'-'+right('0000'+ltrim(@i),4) end ,
@i = case when @j = tb.mid then @i+1 else 1 end,@j = tb.mid
from ta
where ta.[mid] = tb.[mid]
select * from tb
--Result:
/*meid mid mecode
----------- ----------- ----------
1 101 A-001-0001
2 101 A-001-0002
3 101 A-001-0003
4 102 B-001-0001
5 102 B-001-0002
6 103
*/
--End
--> 测试数据:@table1
declare @table1 table([mid] varchar(10),[mcode] varchar(5))
insert @table1
select 101,'A-001' union all
select 102,'B-001' union all
select 103,null
--> 测试数据:@table2
declare @table2 table([meid] int,[mid] varchar(10),[mecode] sql_variant)
insert @table2
select 1,101,'' union all
select 2,101,'' union all
select 3,101,'' union all
select 4,102,'' union all
select 5,102,'' union all
select 6,103,''select meid, mid ,
(select substring(mcode,1,charindex('-',mcode)-1)
from @table1 where mid = t.mid)+'-'+mid+'-'+
right('0000'+cast(meid-(select count(1) from @table2 where mid < t.mid) as varchar(10)),4) as mecode
from @table2 t---------------------------------
1 101 A-101-0001
2 101 A-101-0002
3 101 A-101-0003
4 102 B-102-0001
5 102 B-102-0002
6 103 NULL