SQL Server 2008读书笔记（0）：实用SQL语句

#1：将一列的值设置另一列（相同表或者不同表）
相同表：

update Table1
set Table1.Column1 = Table1.Column2 from Table1

不同表：

UPDATE Table1
SET Table1.Column1 = Table2.Column2 FROM Table2
WHERE Table1.Column3 = Table2.Column3

 #2: 综合选择分组语句：

SELECT empid, YEAR(orderdate) AS orderyear, SUM(freight), COUNT(*) AS numorders 
FROM Sales.Orders
WHERE custid = 71
GROUP BY empid, YEAR(orderdate)
HAVING COUNT(*) > 1
ORDER BY empid, orderyear

#3： 选择分页数据

USE [TableName] 
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[GetDatas]
  @Name varchar(max) = NULL,
  @OrderExp varchar(20) = 'AssetNumber',
  @OrderDirection varchar(10) = 'ASC',
  @PageNum int = 1,
  @PageSize int = 10,
  @ResultCount int output
AS
BEGIN
    SET NOCOUNT ON
  DECLARE @tempDatas TABLE 
  ( 
      Id int,
      Name varchar(50) NULL,
      RowNumber int
  )
  DECLARE @NameLike nvarchar(50)
  DECLARE @Order nvarchar(50)
  
  SET @AssetName = NULLIF(@AssetName, '')
  SET @NameLike = '%' + @AssetName + '%'
  SET @Order = @OrderExp + @OrderDirection

  INSERT INTO @tempDatas (
    Id, 
    Name,
    RowNumber)
    SELECT Id, 
        Name, 
        ROW_NUMBER()OVER(ORDER BY 
          CASE WHEN @Order = 'NameDESC' THEN Name END DESC,
          CASE WHEN @Order = 'NameASC' THEN Name END ASC
          )AS RowNumber
    FROM dbo.MyObjects
    WHERE ((@Name IS NULL) OR (@Name IS NOT NULL AND dbo.MyObjects.Name like @Name))
 
  SELECT @ResultCount = COUNT(1) 
  FROM @tempDatas;
  
  SELECT * FROM @tempDatas 
  WHERE RowNumber BETWEEN ((@PageNum - 1)*@PageSize + 1) AND @PageNum*@PageSize;

END

GO