SQL语句

--利用子查询指定返回范围的查询
USE AdventureWorks
select * from (
SELECT PSC.Name Category,p.Name Product,p.ListPrice, 
ROW_NUMBER() OVER(ORDER BY PSC.Name,P.ListPrice DESC) AS RowNum
FROM Production.Product p
JOIN Production.ProductSubCategory PSC
ON p.ProductSubCategoryID=PSC.ProductSubCategoryID
) orderdata
where RowNum between 25 and 30
--通过ROW_NUMBER函数搭配子查询完成数据分页
CREATE PROC spGetPages2 @iRowCount INT,@iPageNo INT
AS
SELECT * FROM (
SELECT ROW_NUMBER() OVER(ORDER BY ProductID ASC) RowNum,
		* FROM Production.Product ) OrderData
WHERE RowNum BETWEEN @iRowCount*(@iPageNo-1)+1 AND @iRowCount*@iPageNo
ORDER BY ProductID ASC
GO
EXEC spGetPages2 10,20
--删除表中重复行
SELECT ProductID+0 ProductID, Name 
	INTO myPD03
FROM AdventureWorks.Production.Product
--
SET NOCOUNT ON
DECLARE @CNT INT
SET @CNT =1
WHILE @CNT<2001
BEGIN
	INSERT myPD03
	SELECT ProductID, Name 
	FROM AdventureWorks.Production.Product
	SET @CNT =@CNT+1
END
-- 
SELECT * FROM myPD03
ORDER BY ProductID;
-- 
WITH Dups AS
(
  SELECT *, 
    ROW_NUMBER() OVER(PARTITION BY ProductID ORDER BY ProductID) AS RN
  FROM dbo.myPD03
)
DELETE FROM Dups WHERE RN > 1;