CREATE VIEW dbo.news_view
AS
SELECT dbo.iwms_news.articleid, dbo.iwms_news.content, dbo.iwms_news.title, dbo.iwms_news.classid, dbo.iwms_news.Nkey, dbo.iwms_news.hits,
dbo.iwms_news.dateandtime, dbo.iwms_news.dayHits, dbo.iwms_news.weekHits, dbo.iwms_news.reNum, dbo.iwms_news.imgNews,
dbo.iwms_news.titleImg, dbo.iwms_news.topicid, dbo.iwms_news.summary, dbo.iwms_news.highlight, dbo.iwms_news.permitGroups,
dbo.iwms_news.memberid, dbo.iwms_news.checkUserid, dbo.iwms_news.aUrl, dbo.iwms_news.author, dbo.iwms_news.source,
dbo.iwms_news.authorEmail, dbo.iwms_news.sourceUrl, dbo.iwms_news.reLink, dbo.iwms_news.allowRe, dbo.iwms_news.outerImg,
dbo.iwms_news.price, dbo.iwms_news.Limit, dbo.iwms_news.pageTitle, dbo.iwms_member.name AS membername, selfixnews.ftype
FROM dbo.iwms_news LEFT OUTER JOIN
(SELECT fNewsId, MAX(fType) AS ftype
FROM dbo.iwms_fixNews
GROUP BY fNewsId) AS selfixnews ON dbo.iwms_news.articleid = selfixnews.fNewsId LEFT OUTER JOIN
dbo.iwms_member ON dbo.iwms_news.memberid = dbo.iwms_member.ID
这是原本的视图……
AS
SELECT dbo.iwms_news.articleid, dbo.iwms_news.content, dbo.iwms_news.title, dbo.iwms_news.classid, dbo.iwms_news.Nkey, dbo.iwms_news.hits,
dbo.iwms_news.dateandtime, dbo.iwms_news.dayHits, dbo.iwms_news.weekHits, dbo.iwms_news.reNum, dbo.iwms_news.imgNews,
dbo.iwms_news.titleImg, dbo.iwms_news.topicid, dbo.iwms_news.summary, dbo.iwms_news.highlight, dbo.iwms_news.permitGroups,
dbo.iwms_news.memberid, dbo.iwms_news.checkUserid, dbo.iwms_news.aUrl, dbo.iwms_news.author, dbo.iwms_news.source,
dbo.iwms_news.authorEmail, dbo.iwms_news.sourceUrl, dbo.iwms_news.reLink, dbo.iwms_news.allowRe, dbo.iwms_news.outerImg,
dbo.iwms_news.price, dbo.iwms_news.Limit, dbo.iwms_news.pageTitle, dbo.iwms_member.name AS membername, selfixnews.ftype
FROM dbo.iwms_news LEFT OUTER JOIN
(SELECT fNewsId, MAX(fType) AS ftype
FROM dbo.iwms_fixNews
GROUP BY fNewsId) AS selfixnews ON dbo.iwms_news.articleid = selfixnews.fNewsId LEFT OUTER JOIN
dbo.iwms_member ON dbo.iwms_news.memberid = dbo.iwms_member.ID
这是原本的视图……
[articleid] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[content] [ntext] COLLATE Chinese_PRC_CI_AS NULL ,
[title] [nvarchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
[classid] [int] NULL ,
[Nkey] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[hits] [int] NOT NULL ,
[dateandtime] [datetime] NULL ,
[dayHits] [int] NULL CONSTRAINT [DF_iwms_news_dayHits] DEFAULT (0),
[weekHits] [int] NULL CONSTRAINT [DF_iwms_news_weekHits] DEFAULT (0),
[reNum] [int] NULL CONSTRAINT [DF_iwms_news_reNum] DEFAULT (0),
[imgNews] [bit] NOT NULL ,
[titleImg] [nvarchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
[topicid] [int] NULL ,
[summary] [nvarchar] (2000) COLLATE Chinese_PRC_CI_AS NULL ,
[highlight] [bit] NOT NULL ,
[permitGroups] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[memberid] [int] NULL ,
[checkUserid] [int] NULL ,
[aUrl] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[author] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[source] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[authorEmail] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[sourceUrl] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[reLink] [bit] NULL ,
[allowRe] [bit] NULL ,
[outerImg] [bit] NULL ,
[price] [int] NULL ,
[Limit] [int] NULL ,
[pageTitle] [ntext] COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
这是原本的表》》》
[articleid] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[content] [ntext] COLLATE Chinese_PRC_CI_AS NULL ,
[title] [nvarchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
[classid] [int] NULL ,
[Nkey] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[hits] [int] NOT NULL ,
[dateandtime] [datetime] NULL ,
[dayHits] [int] NULL CONSTRAINT [DF_iwms_news_dayHits] DEFAULT (0),
[weekHits] [int] NULL CONSTRAINT [DF_iwms_news_weekHits] DEFAULT (0),
[reNum] [int] NULL CONSTRAINT [DF_iwms_news_reNum] DEFAULT (0),
[imgNews] [bit] NOT NULL ,
[titleImg] [nvarchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
[topicid] [int] NULL ,
[summary] [nvarchar] (2000) COLLATE Chinese_PRC_CI_AS NULL ,
[highlight] [bit] NOT NULL ,
[permitGroups] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[memberid] [int] NULL ,
[checkUserid] [int] NULL ,
[aUrl] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[author] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[source] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[authorEmail] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[sourceUrl] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[reLink] [bit] NULL ,
[allowRe] [bit] NULL ,
[outerImg] [bit] NULL ,
[price] [int] NULL ,
[Limit] [int] NULL ,
[pageTitle] [ntext] COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
这是现在表结构……