sql server

---------------------------------------------------------------------------------------------------------------

4. Pivot, Unpivot (参考源)

---Pivot
SELECT * FROM [StudentScores] /*数据源*/
AS P
PIVOT 
(
    SUM(Score/*行转列后 列的值*/) FOR 
    p.Subject/*需要行转列的列*/ IN ([语文],[数学],[英语],[生物]/*列的值*/)
) AS T


--Unpivot
SELECT P.ProgrectName,P.Supplier,P.SupplyNum
FROM 
(
    SELECT ProgrectName, OverseaSupply, NativeSupply,
           SouthSupply, NorthSupply
     FROM ProgrectDetail
)T
UNPIVOT 
(
    SupplyNum FOR Supplier IN
    (OverseaSupply, NativeSupply, SouthSupply, NorthSupply )
) P

/**
OverseaSupply, NativeSupply, SouthSupply, NorthSupply不能有null的值
***/

View Code

---------------------------------------------------------------------------------------------------------------

3. 小数、整数

select 1/3 as col1, round(cast(1 as float) / 3, 2)  as col2

--结果：["col1": "0", "col2":"0.33"]
--操作数与结果保持一致

2. 定义变量并赋值

declare @x varchar(10);
declare @y varchar(10);

select @x = col1, @y = col2 from Table

1. 更新数据库表的某一字段为限制范围的随机数

--更新Table的col字段
DECLARE @id varchar(36)
DECLARE update_to_random_cursor CURSOR 
FOR 
SELECT sn 
FROM [dbo].[Table]
where col1 = '...' and col2 in ('...','..') --条件

OPEN update_to_random_cursor;
FETCH NEXT FROM update_to_random_cursor
INTO @id
WHILE @@FETCH_STATUS = 0
BEGIN

UPDATE [dbo].[Table]
SET [Table].col = cast( floor(rand()*70) as int)
WHERE sn = @id

FETCH NEXT FROM update_to_random_cursor INTO @id
END

CLOSE update_to_random_cursor;
DEALLOCATE update_to_random_cursor;

View Code