id 单号 产品号 固定 数量
1 X0501010123001 203950019 375 0012 -1
1 X0101010618024 207100015 370 0012 -1
1 X0501010123001 205640016 380 0012 -1
1 X0501010123001 986510033 365 0012 -1
1 X0501010123001 208950019 380 0012 -1
1 X0501010123001 986510015 370 0012 -1
1 X0501010123001 982290021 360 0012 -1
1 X0501010123001 203850032 370 0012 -1
1 X0501010123001 993910016 380 0012 -1
1 X0501010123001 982457017 385 0012 -1
同一单号下,产品号不同id自动增加,不同单号时从1开始又自动增加.
sql 语句如何写.
1 X0501010123001 203950019 375 0012 -1
1 X0101010618024 207100015 370 0012 -1
1 X0501010123001 205640016 380 0012 -1
1 X0501010123001 986510033 365 0012 -1
1 X0501010123001 208950019 380 0012 -1
1 X0501010123001 986510015 370 0012 -1
1 X0501010123001 982290021 360 0012 -1
1 X0501010123001 203850032 370 0012 -1
1 X0501010123001 993910016 380 0012 -1
1 X0501010123001 982457017 385 0012 -1
同一单号下,产品号不同id自动增加,不同单号时从1开始又自动增加.
sql 语句如何写.
解决方案 »
- 【翻译】成为SQL Server专家的10个秘密
- sqlserver 自定义主键
- DTS包导入成功,但作业显示失败?????????????
- 两个存储过程可以同时执行吗?
- 如何在SQL SERVER 中连接INTERBASE数据库?
- 表格数量查找???请问,SQL SERVER中查找数据库中表格数量的语句怎么写?
- Warning:NULL value is eliminated by an aggregate or other SET operation
- Oracle 組件問題!!!SOS!SOS!SOS!
- 有关双数据库服务器的问题(续)????
- 如何做明细帐?
- EXECUTE 后的事务计数指出缺少了 COMMIT 或 ROLLBACK TRANSACTION 语句。原计数 = 0,当前计数 = 1。
- 非邹老大才能解决的登陆账户问题?
大家帮我看看,看哪里写得不对,它就是更改得不对.
update xhmx set xhmx_id=(select count(10) from xhmx where xhmx_glm=a.xhmx_glm and xhmx_cc=a.xhmx_cc and xhmx_dh<=a.xhmx_dh)
from xhmx a
where xhmx_glm=a.xhmx_glm
and xhmx_cc=a.xhmx_cc
declare @t table(id int,单号 varchar(20),产品号 bigint,固定 varchar(20),数量 int)
insert into @T select 1 ,'X0501010123001' ,203950019375 ,'0012' ,-1
union all select 1 ,'X0101010618024' ,207100015370 ,'0012' ,-1
union all select 1 ,'X0501010123001' ,205640016380 ,'0012' ,-1
union all select 1 ,'X0501010123001' ,986510033365 ,'0012' ,-1
union all select 1 ,'X0501010123001' ,208950019380 ,'0012' ,-1
union all select 1 ,'X0501010123001' ,986510015370 ,'0012' ,-1
union all select 1 ,'X0501010123001' ,982290021360 ,'0012' ,-1
union all select 1 ,'X0501010123001' ,203850032370 ,'0012' ,-1
union all select 1 ,'X0501010123001' ,993910016380 ,'0012' ,-1
union all select 1 ,'X0501010123001' ,982457017385 ,'0012' ,-1select id=(select count(*)+1 from @t where 单号=a.单号 and 产品号<a.产品号),单号,产品号,固定,数量 from @t a
declare @t table(id int,单号 varchar(20),产品号 bigint,固定 varchar(20),数量 int)
insert into @T select 1 ,'X0501010123001' ,203950019375 ,'0012' ,-1
union all select 1 ,'X0101010618024' ,207100015370 ,'0012' ,-1
union all select 1 ,'X0501010123001' ,205640016380 ,'0012' ,-1
union all select 1 ,'X0501010123001' ,986510033365 ,'0012' ,-1
union all select 1 ,'X0501010123001' ,208950019380 ,'0012' ,-1
union all select 1 ,'X0501010123001' ,986510015370 ,'0012' ,-1
union all select 1 ,'X0501010123001' ,982290021360 ,'0012' ,-1
union all select 1 ,'X0501010123001' ,203850032370 ,'0012' ,-1
union all select 1 ,'X0501010123001' ,993910016380 ,'0012' ,-1
union all select 1 ,'X0501010123001' ,982457017385 ,'0012' ,-1update @t set id=(select count(*)+1 from @t where 单号=a.单号 and 产品号<a.产品号) from @t aselect * from @t
update #temp_xhmx set xhmx_id=(select count(*) from #temp_xhmx where xhmx_glm<>a.xhmx_glm and xhmx_cc<>a.xhmx_cc and xhmx_dh=a.xhmx_dh)
from #temp_xhmx a
where xhmx_glm=a.xhmx_glm
and xhmx_cc=a.xhmx_cc
and xhmx_dh=a.xhmx_dh
我用上面代码执行后,结果变成下面的了.不知错在哪了.id xhmx_dh xhmx_glm xhmx_cc xhmx_sl
0 X0101010618024 207100015 370 0012 -1
8 X0501010123001 203950019 375 0012 -1
6 X0501010123001 205640016 380 0012 -1
8 X0501010123001 986510033 365 0012 -1
6 X0501010123001 208950019 380 0012 -1
7 X0501010123001 986510015 370 0012 -1
8 X0501010123001 982290021 360 0012 -1
7 X0501010123001 203850032 370 0012 -1
6 X0501010123001 993910016 380 0012 -1
8 X0501010123001 982457017 385 0012 -1
insert into tb select 0 ,'X0101010618024' ,207100015370 ,'0012' ,-1
union all select 0 ,'X0501010123001' ,203950019375 ,'0012' ,-1
union all select 0 ,'X0501010123001' ,205640016380 ,'0012' ,-1
union all select 0 ,'X0501010123001' ,986510033365 ,'0012' ,-1
union all select 0 ,'X0501010123001' ,208950019380 ,'0012' ,-1
union all select 0 ,'X0501010123001' ,986510015370 ,'0012' ,-1
union all select 0 ,'X0501010123001' ,982290021360 ,'0012' ,-1
union all select 0 ,'X0501010123001' ,203850032370 ,'0012' ,-1
union all select 0 ,'X0501010123001' ,993910016380 ,'0012' ,-1
union all select 0 ,'X0501010123001' ,982457017385 ,'0012' ,-1update tb set xhmx_id=(select count(*)+1 from tb where xhmx_glm<a.xhmx_glm and xhmx_dh=a.xhmx_dh)
from tb a
--where xhmx_glm=a.xhmx_glm
--and xhmx_cc=a.xhmx_cc
--and xhmx_dh=a.xhmx_dhselect * from tb order by xhmx_iddrop table tb--这是你想要的结果吗?
如何批量update.
如何批量update.
----------------------
这个跟前面的应该一样啊。你举个例子,不是很明白。
如下面这样,不用判断记录的内容.
1 X0101010618024 207100015 370 0012 -1
1 X0501010123001 203950019 375 0012 -1
2 X0501010123001 205640016 380 0012 -1
3 X0501010123001 986510033 365 0012 -1
4 X0501010123001 208950019 380 0012 -1
5 X0501010123001 986510015 370 0012 -1
6 X0501010123001 982290021 360 0012 -1
7 X0501010123001 203850032 370 0012 -1
8 X0501010123001 993910016 380 0012 -1
9 X0501010123001 982457017 385 0012 -1
1 X0501010123002 203850032 370 0012 -1
2 X0501010123002 993910016 380 0012 -1
3 X0501010123003 982457017 385 0012 -1
create table tb(xhmx_id int,xhmx_dh varchar(20),xhmx_glm bigint,xhmx_cc varchar(10),xhmx_sl int)
insert into tb select 0 ,'X0101010618024' ,207100015370 ,'0012' ,-1
union all select 0 ,'X0501010123001' ,203950019375 ,'0012' ,-1
union all select 0 ,'X0501010123001' ,205640016380 ,'0012' ,-1
union all select 0 ,'X0501010123001' ,986510033365 ,'0012' ,-1
union all select 0 ,'X0501010123001' ,208950019380 ,'0012' ,-1
union all select 0 ,'X0501010123001' ,986510015370 ,'0012' ,-1
union all select 0 ,'X0501010123001' ,982290021360 ,'0012' ,-1
union all select 0 ,'X0501010123001' ,203850032370 ,'0012' ,-1
union all select 0 ,'X0501010123001' ,993910016380 ,'0012' ,-1
union all select 0 ,'X0501010123001' ,982457017385 ,'0012' ,-1
goalter table tb add id_t int identity(1,1)
goupdate tb set xhmx_id=(select count(*)+1 from tb where xhmx_dh=a.xhmx_dh and id_t<a.id_t)
from tb a
goalter table tb drop column id_t
goselect * from tb order by xhmx_id
drop table tb
但那些ID,单号之类的,没个间隔,看得头昏