SQL SERVER 视图

视图

SET　NOCOUNT ON;

SET Northwind;

GO

IF OBJECT_ID('dbo.ViewName') IS NOT NULL

    DROP VIEW dbo.ViewName;

GO

CREATE VIEW dbo.Viewname

AS

SELECT * FROM customer AS C

WHERE EXISTS

    (SELECT * FROM dbo.Orders AS O

     WHERE O.CustomerID = C.Customerid);

GO

**************************我是分割线**************************

IF OBJECT_ID('abo.VSign') IS NOT NULL

    DROP VIEW ado.VSign;

GO

    CREATE VIEW ado.VSign

AS

    SELECT mnth, qty, SIGN((S1.qty-(SELECT TOP 1 qty FROM ado.Sales AS S2

                                                         WHERE S2.mnth < S1.mnth ORDER BY S2.mnth DESC))) AS sgn

    FROM ado.Sales AS S1

GO

************************** 我是分割线**************************

刷新视图

EXEC sp_refreshview 'dbo.V1';

 

SELECT NEXEC sp_refreshview QUOTENAME(VIEW_NAME, '');

 

SELECT NEXEC sp_refreshview QUOTENAME(VIEW_NAME, '"') AS cmd

FROM(SELECT QUOTENAME(TABLE_SCHEMA) N, QUOTENAME(TABLE_NAME) AS VIEW_NAME

          FROM INFORMATION_SCHEMA, VIEWS) AS V

WHERE OBJECT PROPERTY(OBJECT_ID(VIEW_NAME), 'IsSchemaBound') = 0;

视图中的ORDER BY（必须与TOP和FORXML一起使用）

注：不要依赖于视图的ORDER BY（2000可用，2005不可用）

EXISTS中的*可以用，只起统计作用。

SQLSERVER2005支持CTE，使用模块化方法开发。

************************** 我是分割线**************************

ALTER VIEW dbo.Vtrends

AS

WITH CsalesRN AS

(SELECT mnth, qty, ROW_NUMBER() OVER(ORDER BY mnth) AS RN

 FROM dbo.sales),

CSign AS

(SELECT Cur.mnth, Cur.qty, SIGN(Cur.qty - Prv.qty) AS Sign

 FROM CsalesRN AS Cur

 LEFT OUTER JOIN CsalesRN AS Prv ON Cur.rn = Prv.rn + 1),

CGrp AS

(SELECT mnth, sgn, DATEADD(month, ROW_NUMBER() OVER(PARTITION BY sign ORDER BY mnth), mnth) AS grp

FROM CSgn),

SELECT CONVERT(VARCHAR(6), MIN(mnth), 112) AS start

             CONVERT(VARCHAR(6), MIN(mnth), 112) AS end
             CASE Sign

                     WHEN -1 THEN 'down'

                     WHEN 0 THEN 'same'

                     WHEN 1 THEN 'up'

                     ELSE 'unknown'

             END AS trend

FROM CGrp

GROUP BY sgn, grp;

GO