SET ROWCOUNT (Transact-SQL)
使 SQL Server 在返回指定的行数之后停止处理查询。
SET ROWCOUNT { number | @number_var }
参数
number | @number_var
在停止特定查询之前要处理的行数(整数)。USE AdventureWorks;
GO
SELECT count(*) AS Count
FROM Production.ProductInventory
WHERE Quantity < 300;
GO
Count
-----------
537 (1 row(s) affected)
现在,将 ROWCOUNT 设置为 4,并以 Quantity 小于 300 的条件更新所有行。
SET ROWCOUNT 4;
UPDATE Production.ProductInventory
SET Quantity = 400
WHERE Quantity < 300;
GO
(4 row(s) affected)
使 SQL Server 在返回指定的行数之后停止处理查询。
SET ROWCOUNT { number | @number_var }
参数
number | @number_var
在停止特定查询之前要处理的行数(整数)。USE AdventureWorks;
GO
SELECT count(*) AS Count
FROM Production.ProductInventory
WHERE Quantity < 300;
GO
Count
-----------
537 (1 row(s) affected)
现在,将 ROWCOUNT 设置为 4,并以 Quantity 小于 300 的条件更新所有行。
SET ROWCOUNT 4;
UPDATE Production.ProductInventory
SET Quantity = 400
WHERE Quantity < 300;
GO
(4 row(s) affected)
这样的话,查找结果最多只有四条记录。你更新的当然只有4行了
Inserted string (where Fld is the selected field)
Float [6-digit]
CONVERT(VARCHAR, Fld, 0)
Float [8-digit Scientific]
CONVERT(VARCHAR, Fld, 1)
Float [16-digit Scientific]
CONVERT(VARCHAR, Fld, 2)
Other Numeric
CONVERT(VARCHAR, Fld)
Currency [9999.99]
CONVERT(VARCHAR, Fld, 0)
Currency [9,999.99]
CONVERT(VARCHAR, Fld, 1)
Currency [9999.9999]
CONVERT(VARCHAR, Fld, 2)
Date-Time [mm/dd/yy]
CONVERT(VARCHAR, Fld, 1)
Date-Time [mm/dd/yyyy]
CONVERT(VARCHAR, Fld, 101 )
Date-Time [yy.mm.dd]
CONVERT(VARCHAR, Fld, 2)
Date-Time [yyyy.mm.dd]
CONVERT(VARCHAR, Fld, 102)
Date-Time [dd/mm/yy]
CONVERT(VARCHAR, Fld, 3)
Date-Time [dd/mm/yyyy]
CONVERT(VARCHAR, Fld, 103 )
Date-Time [dd.mm.yy]
CONVERT(VARCHAR, Fld, 4)
Date-Time [dd.mm.yyyy]
CONVERT(VARCHAR, Fld, 104 )
Date-Time [dd-mm-yy]
CONVERT(VARCHAR, Fld, 5)
Date-Time [dd-mm-yyyy]
CONVERT(VARCHAR, Fld, 105)
Date-Time [dd mon yy]
CONVERT(VARCHAR, Fld, 6)
Date-Time [dd mon yyyy]
CONVERT(VARCHAR, Fld, 106)
Date-Time [Mon dd, yy]
CONVERT(VARCHAR, Fld, 7)
Date-Time [Mon dd, yyyy]
CONVERT(VARCHAR, Fld, 107)
Date-Time [mm-dd-yy]
CONVERT(VARCHAR, Fld, 10)
Date-Time [mm-dd-yyyy]
CONVERT(VARCHAR, Fld, 110 )
Date-Time [yy/mm/dd]
CONVERT(VARCHAR, Fld, 11)
Date-Time [yyyy/mm/dd]
CONVERT(VARCHAR, Fld, 111 )
Date-Time [yymmdd]
CONVERT(VARCHAR, Fld, 12)
Date-Time [yyyymmdd]
CONVERT(VARCHAR, Fld, 112)
Date-Time [mon dd yyyy hh:miAM]
CONVERT(VARCHAR, Fld, 0)
Date-Time [mon dd yyyy hh:mi:ss:mmmAM]
CONVERT(VARCHAR, Fld, 9)
Date-Time [dd mon yyyy hh:mm:ss:mmm]
CONVERT(VARCHAR, Fld, 13)
Date-Time [hh:mm:ss]
CONVERT(VARCHAR, Fld, 8)
Date-Time [hh:mi:ss:mmm]
CONVERT(VARCHAR, Fld, 14)
Date-Time [yyyy-mm-dd hh:mi:ss]
CONVERT(VARCHAR, Fld, 20)
Date-Time [yyyy-mm-dd hh:mi:ss.mmm]
CONVERT(VARCHAR, Fld, 21)
Date-Time [yyyy-mm-ddThh:mm:ss:mmm]
CONVERT(VARCHAR, Fld, 126 )
ex:
declare @aa numeric(18,2)
set @aa=12000212.00
select convert(char(20),convert(money,@aa),1)/*
--------------------
12,000,212.00(1 行受影响)*/