字段[WValue]里有35行记录,如果语句如下
UPDATE [DLETou].[dbo].[WMNumber]
SET [WValue] =' 1 7’
where WNO=WID
则字段[WValue]里有35行记录全更新为17。这不是我想要的!而问题是这35行的数据是‘1 7 10 30 3 1 2 2 2 1 12 6 6 7 1 2 4 7 17 7 5 8 2 6 1 2 3 5 5 3 1 15 21’我想把这35行数据更新如下
[WValue]
1
7
10
30
3
1
2
2
2
1
12
6
6
7
1
2
4
7
17
7
5
8
2
6
1
2
3
5
5
3
1
15
21我写了语句,错误!
UPDATE [DLETou].[dbo].[WMNumber]
SET [WValue] =‘1 7 10 30 3 1 2 2 2 1 12 6 6 7 1 2 4 7 17 7 5 8 2 6 1 2 3 5 5 3 1 15 21’
where WNO=WID写了语句却执行错误,如何修改SQL语句
UPDATE [DLETou].[dbo].[WMNumber]
SET [WValue] =' 1 7’
where WNO=WID
则字段[WValue]里有35行记录全更新为17。这不是我想要的!而问题是这35行的数据是‘1 7 10 30 3 1 2 2 2 1 12 6 6 7 1 2 4 7 17 7 5 8 2 6 1 2 3 5 5 3 1 15 21’我想把这35行数据更新如下
[WValue]
1
7
10
30
3
1
2
2
2
1
12
6
6
7
1
2
4
7
17
7
5
8
2
6
1
2
3
5
5
3
1
15
21我写了语句,错误!
UPDATE [DLETou].[dbo].[WMNumber]
SET [WValue] =‘1 7 10 30 3 1 2 2 2 1 12 6 6 7 1 2 4 7 17 7 5 8 2 6 1 2 3 5 5 3 1 15 21’
where WNO=WID写了语句却执行错误,如何修改SQL语句
解决方案 »
- 这样的SQL如何来写?
- 用SQL 设计一个储存历史指标的框架.表分成总表 当前表 历史表 总表记录有多
- 请问如何使用大对象字段:ntext,text,image等?我好惭愧呀!
- sql server2005数据查询范围越小时间越慢,求解析
- 存储过程中怎样使用for 循环语句
- 问一下:paradox数据库,这是什么环境下开发所用的数据库?
- 求高手进!!sqlserver+jdbc连接的错误
- SQL表中获取时间段里每一天的数据,如果表里没有这一天的记录数据就为零
- 我的SQL2000用了混合认证模式,为什么使用程序之前还要登录服务器才能连接?
- 嵌套查询中多字段作为主键 如何查询?
- 如何求出表TB1里所有号码(有111W条记录)的奇偶数比,在线急等解决!
- SQLTIMESTAMP与COleDateTime
SET [WValue] =‘1 7 10 30 3 1 2 2 2 1 12 6 6 7 1 2 4 7 17 7 5 8 2 6 1 2 3 5 5 3 1 15 21’
where WNO=WID
我想把35行数据用语句一次全更新完,有办法吗?
写了语句却执行错误,如何修改SQL语句
WID**WNO**WValue**WBlue
1**1**3**14
2**2**6**4
3**3**9**2
4**4**29**43
5**5**2**29
6**6**2**32
7**7**1**16
8**8**1**1
9**9**1**9
10**10**6**27
11**11**11**17
12**12**5**3
13**13**5**18
14**14**6**11
15**15**2**13
16**16**1**6
17**17**3**0
18**18**6**0
19**19**16**0
20**20**6**0
21**21**4**0
22**22**7**0
23**23**1**0
24**24**5**0
25**25**3**0
26**26**1**0
27**27**2**0
28**28**4**0
29**29**4**0
30**30**2**0
31**31**4**0
32**32**14**0
33**33**20**0
NULL**NULL**NULL**NULL
--> 测试数据:#
if object_id('tempdb.dbo.#') is not null drop table #
create table #(WID int, WNO int, WValue int, WBlue int)
insert into #
select 1, 1, 3, 14 union all
select 2, 2, 6, 4 union all
select 3, 3, 9, 2 union all
select 4, 4, 29, 43 union all
select 5, 5, 2, 29 union all
select 6, 6, 2, 32 union all
select 7, 7, 1, 16 union all
select 8, 8, 1, 1 union all
select 9, 9, 1, 9 union all
select 10, 10, 6, 27 union all
select 11, 11, 11, 17 union all
select 12, 12, 5, 3 union all
select 13, 13, 5, 18 union all
select 14, 14, 6, 11 union all
select 15, 15, 2, 13 union all
select 16, 16, 1, 6 union all
select 17, 17, 3, 0 union all
select 18, 18, 6, 0 union all
select 19, 19, 16, 0 union all
select 20, 20, 6, 0 union all
select 21, 21, 4, 0 union all
select 22, 22, 7, 0 union all
select 23, 23, 1, 0 union all
select 24, 24, 5, 0 union all
select 25, 25, 3, 0 union all
select 26, 26, 1, 0 union all
select 27, 27, 2, 0 union all
select 28, 28, 4, 0 union all
select 29, 29, 4, 0 union all
select 30, 30, 2, 0 union all
select 31, 31, 4, 0 union all
select 32, 32, 14, 0 union all
select 33, 33, 20, 0 union all
select null, null, null, null;with Val as
(
select vid = row_number()over(order by getdate()), * from (values (1),(7),(10),(30),(3),(1),(2),(2),(2),(1),(12),(6),(6),(7),(1),(2),(4),(7),(17),(7),(5),(8),(2),(6),(1),(2),(3),(5),(5),(3),(1),(15),(21)) as t(Val)
),
Tmp as
(
select rid = row_number()over(order by WID), * from # where WID = WNO
)
update t set t.WValue = v.Val from Tmp t join Val v on t.WID = v.vidselect * from #/*
WID WNO WValue WBlue
----------- ----------- ----------- -----------
1 1 1 14
2 2 7 4
3 3 10 2
4 4 30 43
5 5 3 29
6 6 1 32
7 7 2 16
8 8 2 1
9 9 2 9
10 10 1 27
11 11 12 17
12 12 6 3
13 13 6 18
14 14 7 11
15 15 1 13
16 16 2 6
17 17 4 0
18 18 7 0
19 19 17 0
20 20 7 0
21 21 5 0
22 22 8 0
23 23 2 0
24 24 6 0
25 25 1 0
26 26 2 0
27 27 3 0
28 28 5 0
29 29 5 0
30 30 3 0
31 31 1 0
32 32 15 0
33 33 21 0
NULL NULL NULL NULL
*/
关键字 'values' 附近有语法错误。
go
create table #(WID int, WNO int, WValue int, WBlue int)
go
insert into #
select 1, 1, 3, 14 union all
select 2, 2, 6, 4 union all
select 3, 3, 9, 2 union all
select 4, 4, 29, 43 union all
select 5, 5, 2, 29 union all
select 6, 6, 2, 32 union all
select 7, 7, 1, 16 union all
select 8, 8, 1, 1 union all
select 9, 9, 1, 9 union all
select 10, 10, 6, 27 union all
select 11, 11, 11, 17 union all
select 12, 12, 5, 3 union all
select 13, 13, 5, 18 union all
select 14, 14, 6, 11 union all
select 15, 15, 2, 13 union all
select 16, 16, 1, 6 union all
select 17, 17, 3, 0 union all
select 18, 18, 6, 0 union all
select 19, 19, 16, 0 union all
select 20, 20, 6, 0 union all
select 21, 21, 4, 0 union all
select 22, 22, 7, 0 union all
select 23, 23, 1, 0 union all
select 24, 24, 5, 0 union all
select 25, 25, 3, 0 union all
select 26, 26, 1, 0 union all
select 27, 27, 2, 0 union all
select 28, 28, 4, 0 union all
select 29, 29, 4, 0 union all
select 30, 30, 2, 0 union all
select 31, 31, 4, 0 union all
select 32, 32, 14, 0 union all
select 33, 33, 20, 0 union all
select null, null, null, null;
go
create index idx1 on #(wid)
go
declare @s varchar(1000),@i int,@cnt int
select @s='1 7 10 30 3 1 2 2 2 1 12 6 6 7 1 2 4 7 17 7 5 8 2 6 1 2 3 5 5 3 1 15 21',@cnt = len(@s) -len(replace(@s,' ',''))
set rowcount @cnt
update a set @i=charindex(' ',@s),wvalue = left(@s,@i-1),@s=stuff(@s,1,@i,'')
from # a with(index = idx1)
select * from #
go
WID WNO WValue WBlue
----------- ----------- ----------- -----------
1 1 11 14
2 2 71 4
3 3 101 2
4 4 301 43
5 5 31 29
6 6 11 32
7 7 21 16
8 8 21 1
9 9 21 9
10 10 11 27
11 11 121 17
12 12 621 3
13 13 61 18
14 14 711 11
15 15 11 13
16 16 21 6
17 17 41 0
18 18 71 0
19 19 17 0
20 20 71 0
21 21 51 0
22 22 81 0
23 23 21 0
24 24 61 0
25 25 11 0
26 26 21 0
27 27 31 0
28 28 51 0
29 29 51 0
30 30 31 0
31 31 11 0
32 32 115 0
33 33 211 0
NULL NULL NULL NULL
原来数据是这样
WID WNO WValue WBlue
----------- ----------- ----------- -----------
1 1 1 14
2 2 7 4
3 3 10 2
4 4 30 43
5 5 3 29
6 6 1 32
7 7 2 16
8 8 2 1
9 9 2 9
10 10 1 27
11 11 12 17
12 12 6 3
13 13 6 18
14 14 7 11
15 15 1 13
16 16 2 6
17 17 4 0
18 18 7 0
19 19 17 0
20 20 7 0
21 21 5 0
22 22 8 0
23 23 2 0
24 24 6 0
25 25 1 0
26 26 2 0
27 27 3 0
28 28 5 0
29 29 5 0
30 30 3 0
31 31 1 0
32 32 15 0
33 33 21 0
NULL NULL NULL NULL
/*
WID WNO WValue WBlue
----------- ----------- ----------- -----------
1 1 1 14
2 2 7 4
3 3 10 2
4 4 30 43
5 5 3 29
6 6 1 32
7 7 2 16
8 8 2 1
9 9 2 9
10 10 1 27
11 11 12 17
12 12 6 3
13 13 6 18
14 14 7 11
15 15 1 13
16 16 2 6
17 17 4 0
18 18 7 0
19 19 17 0
20 20 7 0
21 21 5 0
22 22 8 0
23 23 2 0
24 24 6 0
25 25 1 0
26 26 2 0
27 27 3 0
28 28 5 0
29 29 5 0
30 30 3 0
31 31 1 0
32 32 15 0
33 33 21 0
NULL NULL NULL NULL
*/
11 17 110 130 13 11 12 12 12 11 112 16 16 17 11 12 14 17 原来结果是
[WValue]
1
7
10
30
3
1
2
2
2
1
12
6
6
7
1
2
4
7 执行SQL语句后,可以把这列数据更新
[WValue]
11
17
110
130
13
11
12
12
12
11
112
16
16
17
11
12
14
17
如何用SQL语句完成?
((Case when NO1 in ('02','20','21','22','23','31') then 1 else 0 end)+
(Case when NO2 in ('02','20','21','22','23','31') then 1 else 0 end)+
(Case when NO3 in ('02','20','21','22','23','31') then 1 else 0 end)+
(Case when NO4 in ('02','20','21','22','23','31') then 1 else 0 end)+
(Case when NO5 in ('02','20','21','22','23','31') then 1 else 0 end)+
(Case when NO6 in ('02','20','21','22','23','31') then 1 else 0 end)) as RpNumber Into #RepTable
FROM [SSBallDB].[dbo].[tb2]
Select count (*) as R0
FROM [SSBallDB].[dbo].[tb2]
Where 1=1
and ID in(Select ID From #RepTable left join [tb2] on #RepTable.RID=[tb2].ID Where 1=1
and (RpNumber=0) ) Select count (*) as R1
FROM [SSBallDB].[dbo].[tb2]
Where 1=1
and ID in(Select ID From #RepTable left join [tb2] on #RepTable.RID=[tb2].ID Where 1=1
and (RpNumber = 1) ) Select count (*) as R2
FROM [SSBallDB].[dbo].[tb2]
Where 1=1
and ID in(Select ID From #RepTable left join [tb2] on #RepTable.RID=[tb2].ID Where 1=1
and (RpNumber =2) ) Select count (*) as R3
FROM [SSBallDB].[dbo].[tb2]
Where 1=1
and ID in(Select ID From #RepTable left join [tb2] on #RepTable.RID=[tb2].ID Where 1=1
and (RpNumber =3) )
Select count (*) as R4
FROM [SSBallDB].[dbo].[tb2]
Where 1=1
and ID in(Select ID From #RepTable left join [tb2] on #RepTable.RID=[tb2].ID Where 1=1
and (RpNumber =4) ) Select count (*) as R5
FROM [SSBallDB].[dbo].[tb2]
Where 1=1
and ID in(Select ID From #RepTable left join [tb2] on #RepTable.RID=[tb2].ID Where 1=1
and (RpNumber =5) )
Select count (*) as R6
FROM [SSBallDB].[dbo].[tb2]
Where 1=1
and ID in(Select ID From #RepTable left join [tb2] on #RepTable.RID=[tb2].ID Where 1=1
and (RpNumber =6) )
Drop Table #RepTable