牛の腹のニュースはシステムのノートを発表します11:記憶の過程の編纂

4424 ワード
JOIN c Date delete 保存 インセンス
・最新10件のニュースを取り出す(所属分類、ニュースタイトル、配信時期)
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author:		keithray
-- Create date: 2011-8-27
-- Description:	    10   （    、    、    ）
-- =============================================
ALTER PROCEDURE [dbo].[news_SelectNewNews]
	
AS
BEGIN
	select top 10 n.id,n.title,n.createTime,c.[name] from news n
	inner join category c on n.caId=c.id
	order by n.createTime desc
END

・ホットなニュースを10件取り出す
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author:		keithray
-- Create date: 2011-8-27
-- Description:   10     (         )
-- =============================================
ALTER PROCEDURE [dbo].[news_SelectHotNews]

AS
BEGIN
select top 10 n.id,n.title,n.createTime,c.[name], count(com.id) as comCount
    from news n
	inner join category c on n.caId=c.id
    inner join comment com on com.newsId=n.id
	group by  n.id,n.title,n.createTime,c.[name]
	order by n.createTime desc
END

・カテゴリIDから当該カテゴリの下の全てのニュースを取り出す
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author:		keithray	
-- Create date: 20118-27
-- Description:	     ID           
-- =============================================
ALTER PROCEDURE [dbo].[news_SelectByCaId] 
	@caid int
AS
BEGIN
	select n.id,n.title,n.createTime,c.[name] from news n
	inner join category c on n.caId=c.id and n.caId=@caid
	order by n.createTime desc
END

・ニュースIDから当該ニュース主体コンテンツを取り出す
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author:		keithray	
-- Create date: 2011-8-27
-- Description:    ID          
-- =============================================
ALTER PROCEDURE [dbo].[news_SelectById]
	@id int
AS
BEGIN
	select title,[content],createTime,caId from news where id=@id
END

・見出しによるニュース検索
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


-- =============================================
-- Author:		keithray
-- Create date: 2011-8-27
-- Description:	         
-- =============================================
ALTER PROCEDURE [dbo].[news_SelectByTitle] 
	@title varchar(100)
AS
BEGIN
	select top 10 n.id,n.title,n.createTime,c.[name] from news n
	inner join category c on n.caId=c.id
    where n.title like '%'+@title+'%'
	order by n.createTime desc
END

・コンテンツによるニュース検索
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author:		keithray	
-- Create date: 2011-8-27
-- Description:	         
-- =============================================
ALTER PROCEDURE [dbo].[news_SelectByContent]
	
 @content varchar(1000)
AS
BEGIN
	select top 10 n.id,n.title,n.createTime,c.[name] from news n
	inner join category c on n.caId=c.id
    where n.content like '%'+@content+'%'
	order by n.createTime desc
END

・ニュースを増やす
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author:		keithray	
-- Create date: 2011-8-27
-- Description:	    
-- =============================================
ALTER PROCEDURE [dbo].[news_Insert]
	@title varchar(100),
	@content text,
	@caId int
AS
BEGIN
		INSERT INTO news (title, [content], caId)
		VALUES(@title,@content,@caId)
END

・ニュースの修正
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author:		keithray	
-- Create date: 2011-8-27
-- Description:	    
-- =============================================
ALTER PROCEDURE [dbo].[news_Update]
	@id int,
	@title varchar(100),
	@content text,
	@caId int
AS
BEGIN
	UPDATE news SET title = @title, [content] = @content, caId = @caid where id=@id
END

・ニュースの削除(その下のニュースコメントとともに削除)
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author:		keithray	
-- Create date: 2011-8-27
-- Description:	    （            ）
-- =============================================
ALTER PROCEDURE [dbo].[news_Delete]
	@id int
AS
BEGIN
	--     
	delete comment where newsId=@id
   --       
	delete news where id=@id
END
DAOを反射するときに役立ちます
Google Test勉強録 (1) CMakeでのビルド