求sql语句

Question

已知一张表,结构如下所示:
create table  tb222
(
num int identity(1,1) primary key,
qty1 int  default  0,
qty2 int default 0,
qty3 int default 0,
qty int
)
insert into  tb222(qty)
select   5
union all
select  3
union all
select 8
表内容如下:
num         qty1        qty2        qty3        qty         
----------- ----------- ----------- ----------- ----------- 
1           0           0           0           5
2           0           0           0           3
3           0           0           0           8(影響 3 個資料列)
现在我想把qty中的值随机分配到qty1,qty2,qty3中的一个
即想要的记过为:
num         qty1        qty2        qty3        qty         
----------- ----------- ----------- ----------- ----------- 
1           0           5           0           5
2           0           0           3           3
3           0           8           0           8
注意:表中可能有几千笔记录.不可能对每笔记录使用update进行逐条更新.

Answer 1

行转列，case when，精华里面一大堆，自己翻吧

Answer 2

---------------------------------
--  Author: liangCK 小梁
---------------------------------
 
--> 生成测试数据: @T
DECLARE @T TABLE (num INT,qty1 INT,qty2 INT,qty3 INT,qty INT)
INSERT INTO @T
SELECT 1,0,0,0,5 UNION ALL
SELECT 2,0,0,0,3 UNION ALL
SELECT 3,0,0,0,8--SQL查询如下:;WITH Liang AS
(
    SELECT num,qty1,qty2,qty3,qty,
           rnd=ABS(CHECKSUM(NEWID()))%3+1
    FROM @T
)
UPDATE Liang SET
    qty1=CASE WHEN rnd=1 THEN qty ELSE qty1 END,
    qty2=CASE WHEN rnd=2 THEN qty ELSE qty2 END,
    qty3=CASE WHEN rnd=3 THEN qty ELSE qty3 END
    
SELECT * FROM @T/*
num         qty1        qty2        qty3        qty
----------- ----------- ----------- ----------- -----------
1           0           0           5           5
2           3           0           0           3
3           8           0           0           8(3 行受影响)
*/

Answer 3

to 小梁:
请问这段sql脚本代码是在sqlserver2005还是2000上运行的?我直接COPY到我2000的查询分析器中执行,系统提示我关键字"with"附近的语法错误.why?

Answer 4

2005的，改一下就行了：--> 生成测试数据: @T
DECLARE @T TABLE (num INT,qty1 INT,qty2 INT,qty3 INT,qty INT)
INSERT INTO @T
SELECT 1,0,0,0,5 UNION ALL
SELECT 2,0,0,0,3 UNION ALL
SELECT 3,0,0,0,8--SQL查询如下:UPDATE Liang SET
    qty1=CASE WHEN rnd=1 THEN qty ELSE qty1 END,
    qty2=CASE WHEN rnd=2 THEN qty ELSE qty2 END,
    qty3=CASE WHEN rnd=3 THEN qty ELSE qty3 END
FROM
(
    SELECT num,qty1,qty2,qty3,qty,
           rnd=ABS(CHECKSUM(NEWID()))%3+1
    FROM @T
)  LiangSELECT * FROM @T
/*
num         qty1        qty2        qty3        qty
----------- ----------- ----------- ----------- -----------
1           5           0           0           5
2           0           0           3           3
3           0           8           0           8(3 行受影响)
*/

Answer 5

2005的，改一下就行了
--> 生成测试数据: @T
DECLARE @T TABLE (num INT,qty1 INT,qty2 INT,qty3 INT,qty INT)
INSERT INTO @T
SELECT 1,0,0,0,5 UNION ALL
SELECT 2,0,0,0,3 UNION ALL
SELECT 3,0,0,0,8--SQL查询如下:UPDATE Liang SET
    qty1=CASE WHEN rnd=1 THEN qty ELSE qty1 END,
    qty2=CASE WHEN rnd=2 THEN qty ELSE qty2 END,
    qty3=CASE WHEN rnd=3 THEN qty ELSE qty3 END
FROM
(
    SELECT num,qty1,qty2,qty3,qty,
           rnd=ABS(CHECKSUM(NEWID()))%3+1
    FROM @T
)  LiangSELECT * FROM @T
/*
num         qty1        qty2        qty3        qty
----------- ----------- ----------- ----------- -----------
1           5           0           0           5
2           0           0           3           3
3           0           8           0           8(3 行受影响)
*/

Answer 6

我也覺得怎么多了個with呢，是2005的呀