create table #test(key_id nvarchar(36),j_name nvarchar(36),j_dt datetime,j_id nvarchar(36))
insert into #test(key_id,j_name,j_dt)
select newid(), 'jack','2011-11-11' union all
select newid(), 'jack','2011-11-11' union all
select newid(), 'jack','2011-11-11' union all
select newid(), 'jack','2011-11-11' union all
select newid(), 'jack','2011-11-11' union all
select newid(), 'jack1','2011-11-12' union all
select newid(), 'jack1','2011-11-12' union all
select newid(), 'jack1','2011-11-12' union all
select newid(), 'jack1','2011-11-12' union all
select newid(), 'jack1','2011-11-12' union all
select newid(), 'jack1','2011-11-12' union all
select newid(), 'jack2','2011-11-11' union all
select newid(), 'jack2','2011-11-11' union all
select newid(), 'jack2','2011-11-11' union all
select newid(), 'jack2','2011-11-11' union all
select newid(), 'jack2','2011-11-11' union all
select newid(), 'jack2','2011-11-11' 要将j_name 和j_dt 相同的 数据更新j_id 为同样的newid()
insert into #test(key_id,j_name,j_dt)
select newid(), 'jack','2011-11-11' union all
select newid(), 'jack','2011-11-11' union all
select newid(), 'jack','2011-11-11' union all
select newid(), 'jack','2011-11-11' union all
select newid(), 'jack','2011-11-11' union all
select newid(), 'jack1','2011-11-12' union all
select newid(), 'jack1','2011-11-12' union all
select newid(), 'jack1','2011-11-12' union all
select newid(), 'jack1','2011-11-12' union all
select newid(), 'jack1','2011-11-12' union all
select newid(), 'jack1','2011-11-12' union all
select newid(), 'jack2','2011-11-11' union all
select newid(), 'jack2','2011-11-11' union all
select newid(), 'jack2','2011-11-11' union all
select newid(), 'jack2','2011-11-11' union all
select newid(), 'jack2','2011-11-11' union all
select newid(), 'jack2','2011-11-11' 要将j_name 和j_dt 相同的 数据更新j_id 为同样的newid()
解决方案 »
- SELECT 子句中包含一个保留字、拼写错误或丢失的参数,或标点符号
- 请教 帖子信息(用户名,发帖ID,标题,内容)这个关系模式属于第几范式呢?
- 关于存储过程的问题?急!
- 快让我崩溃的 树形结构 递归求和 问题。(盼邹建等高人)
- 急问,如何让Enterprise manager备份时候不显示所有硬盘上文件!
- 我现在再做一个发号系统
- !求解-------如何简单的插入有以下表结构的一条数据
- 用t-sql如何实现把一个sql server数据表导出成一个指定路径的foxpro表(或dbase表)
- 一个简单而奇怪的问题
- sql server 查找一段时间内每天零点附近的值
- 求将一个字段的多条记录,拼成一条记录的代码?【记录多,必须高效】
- sql
update a
set a.j_id = b.j_id
from tb a join (select j_name,j_dt,min(j_id) as j_id from tb group by j_name,j_dt)b
on a.j_name = b.j_name and a.j_dt = b.j_dt
insert into #test(key_id,j_name,j_dt)
select newid(), 'jack','2011-11-11' union all
select newid(), 'jack','2011-11-11' union all
select newid(), 'jack','2011-11-11' union all
select newid(), 'jack','2011-11-11' union all
select newid(), 'jack','2011-11-11' union all
select newid(), 'jack1','2011-11-12' union all
select newid(), 'jack1','2011-11-12' union all
select newid(), 'jack1','2011-11-12' union all
select newid(), 'jack1','2011-11-12' union all
select newid(), 'jack1','2011-11-12' union all
select newid(), 'jack1','2011-11-12' union all
select newid(), 'jack2','2011-11-11' union all
select newid(), 'jack2','2011-11-11' union all
select newid(), 'jack2','2011-11-11' union all
select newid(), 'jack2','2011-11-11' union all
select newid(), 'jack2','2011-11-11' union all
select newid(), 'jack2','2011-11-11' update a set j_id=(select MIN(key_id) from #test where a.j_name=j_name and j_dt=a.j_dt)
from #test aselect * from #test/*
key_id j_name j_dt j_id
------------------------------------ ------------------------------------ ----------------------- ------------------------------------
DDB21D6F-7BA5-4A1F-8025-69D78E45C22E jack 2011-11-11 00:00:00.000 067622F4-9FCA-49DF-BDAD-F0110E82473D
25FDD1E4-3A2A-4089-90B6-988F24521B11 jack 2011-11-11 00:00:00.000 067622F4-9FCA-49DF-BDAD-F0110E82473D
067622F4-9FCA-49DF-BDAD-F0110E82473D jack 2011-11-11 00:00:00.000 067622F4-9FCA-49DF-BDAD-F0110E82473D
E5D16DDD-BAD7-436D-B8EA-697E6431C69F jack 2011-11-11 00:00:00.000 067622F4-9FCA-49DF-BDAD-F0110E82473D
AE646B45-8E0C-402F-94D7-D5861072AFF3 jack 2011-11-11 00:00:00.000 067622F4-9FCA-49DF-BDAD-F0110E82473D
ACFFD553-70DB-40B8-A420-B943724AD21B jack1 2011-11-12 00:00:00.000 7CD517E0-7151-4A5B-B903-DBCC09EB0ABA
7CD517E0-7151-4A5B-B903-DBCC09EB0ABA jack1 2011-11-12 00:00:00.000 7CD517E0-7151-4A5B-B903-DBCC09EB0ABA
92EC89DD-3C97-4DA2-BFF0-09C83BD5D059 jack1 2011-11-12 00:00:00.000 7CD517E0-7151-4A5B-B903-DBCC09EB0ABA
8EBC4E5A-A25A-412C-9106-F080BBF5AB84 jack1 2011-11-12 00:00:00.000 7CD517E0-7151-4A5B-B903-DBCC09EB0ABA
844FC150-8DC3-49AC-9089-300D3BD46AA4 jack1 2011-11-12 00:00:00.000 7CD517E0-7151-4A5B-B903-DBCC09EB0ABA
E05A95F3-7510-48FD-BC13-FE7C7B5BAED1 jack1 2011-11-12 00:00:00.000 7CD517E0-7151-4A5B-B903-DBCC09EB0ABA
811A9D25-84AD-40B0-8455-3ADB62597197 jack2 2011-11-11 00:00:00.000 5A571B15-A7CA-4D0E-AE72-60E437E7B076
B932D2E1-0987-4D0E-9AAF-96B38ABCCA72 jack2 2011-11-11 00:00:00.000 5A571B15-A7CA-4D0E-AE72-60E437E7B076
65252588-2267-4E2E-8BED-FFBEB364F13B jack2 2011-11-11 00:00:00.000 5A571B15-A7CA-4D0E-AE72-60E437E7B076
A84C3C3B-FA05-492F-B9AC-8B91DB2902DD jack2 2011-11-11 00:00:00.000 5A571B15-A7CA-4D0E-AE72-60E437E7B076
5A571B15-A7CA-4D0E-AE72-60E437E7B076 jack2 2011-11-11 00:00:00.000 5A571B15-A7CA-4D0E-AE72-60E437E7B076
B4BBE113-EB7D-4BDC-8F6D-4D5EC761B17C jack2 2011-11-11 00:00:00.000 5A571B15-A7CA-4D0E-AE72-60E437E7B076(17 行受影响)
我将MIN(key_id) 替换为newid() 不行...还有没有其他方法...
create table #test(key_id nvarchar(36),j_name nvarchar(36),j_dt datetime,j_id nvarchar(36))
insert into #test(key_id,j_name,j_dt)
select newid(), 'jack','2011-11-11' union all
select newid(), 'jack','2011-11-11' union all
select newid(), 'jack','2011-11-11' union all
select newid(), 'jack','2011-11-11' union all
select newid(), 'jack','2011-11-11' union all
select newid(), 'jack1','2011-11-12' union all
select newid(), 'jack1','2011-11-12' union all
select newid(), 'jack1','2011-11-12' union all
select newid(), 'jack1','2011-11-12' union all
select newid(), 'jack1','2011-11-12' union all
select newid(), 'jack1','2011-11-12' union all
select newid(), 'jack2','2011-11-11' union all
select newid(), 'jack2','2011-11-11' union all
select newid(), 'jack2','2011-11-11' union all
select newid(), 'jack2','2011-11-11' union all
select newid(), 'jack2','2011-11-11' union all
select newid(), 'jack2','2011-11-11'
go
--取每组的第一条的key_id进行j_id的更新
update t1 set j_id=t2.key_id from #test t1 cross apply (select top 1 * from #test where j_name=t1.j_name and j_dt=t1.j_dt) t2
/*
key_id j_name j_dt j_id
------------------------------------ ------------------------------------ ----------------------- ------------------------------------
F5F99728-E9D4-4D21-849C-5DABCA85F226 jack 2011-11-11 00:00:00.000 F5F99728-E9D4-4D21-849C-5DABCA85F226
5831719C-00F6-4324-9398-2BCDE9C59F1D jack 2011-11-11 00:00:00.000 F5F99728-E9D4-4D21-849C-5DABCA85F226
17C5E20A-A830-4A79-A544-928E3138F40A jack 2011-11-11 00:00:00.000 F5F99728-E9D4-4D21-849C-5DABCA85F226
CFCACDDB-7C5D-4704-88A0-A82A3802D326 jack 2011-11-11 00:00:00.000 F5F99728-E9D4-4D21-849C-5DABCA85F226
1B0C5701-6CB3-4461-9894-F64B58F64394 jack 2011-11-11 00:00:00.000 F5F99728-E9D4-4D21-849C-5DABCA85F226
D6AF3DA0-1D4D-4A09-8D37-7D88638A1AA5 jack1 2011-11-12 00:00:00.000 D6AF3DA0-1D4D-4A09-8D37-7D88638A1AA5
3D843EED-33B3-47F6-B484-871704463378 jack1 2011-11-12 00:00:00.000 D6AF3DA0-1D4D-4A09-8D37-7D88638A1AA5
A5160270-DCB0-4015-B3E1-0BE8F57F2116 jack1 2011-11-12 00:00:00.000 D6AF3DA0-1D4D-4A09-8D37-7D88638A1AA5
05A6A97F-4ECB-47FA-A73D-946CD36A52EE jack1 2011-11-12 00:00:00.000 D6AF3DA0-1D4D-4A09-8D37-7D88638A1AA5
07C89D9D-D9C8-404D-ABBC-C1CD126749D8 jack1 2011-11-12 00:00:00.000 D6AF3DA0-1D4D-4A09-8D37-7D88638A1AA5
2FA9E633-594C-4BE5-A15A-0C5C95904B20 jack1 2011-11-12 00:00:00.000 D6AF3DA0-1D4D-4A09-8D37-7D88638A1AA5
704CE608-40EE-45D1-BADE-72934D039C4F jack2 2011-11-11 00:00:00.000 704CE608-40EE-45D1-BADE-72934D039C4F
6ED704DB-EE5E-48BA-B82C-C92C586DD188 jack2 2011-11-11 00:00:00.000 704CE608-40EE-45D1-BADE-72934D039C4F
588E324A-6F6D-4CFB-9704-3DB340FB3D0B jack2 2011-11-11 00:00:00.000 704CE608-40EE-45D1-BADE-72934D039C4F
CE2DCCD8-22BA-41FE-B0E9-8DE969894583 jack2 2011-11-11 00:00:00.000 704CE608-40EE-45D1-BADE-72934D039C4F
6EC566C8-3B66-4DD7-9801-CD5704603C0F jack2 2011-11-11 00:00:00.000 704CE608-40EE-45D1-BADE-72934D039C4F
D2AC59D8-728B-4B00-ABC6-0A2429C6D0B5 jack2 2011-11-11 00:00:00.000 704CE608-40EE-45D1-BADE-72934D039C4F(17 行受影响)
*/
O了update #test
set j_id=b.j_id
from #test a join
(select j_name,j_dt,newid() as j_id from #test group by j_name,j_dt) b on a.j_name=b.j_name and datediff(mi,a.j_dt,b.j_dt)=0谢谢