一个SQL视图的问题，解决马上给分！！！！

AVAmyZ 2009-05-13 10:53:04
现在有四个表，想做一个视图，但是没有合适思路，望各位大大帮忙！！
=================================
表content（放文章属性）
contentid 频道id 作者 标题 发布时间
21 1 admin 测试文章标题 2009-10-10
=================================
表pages（放大文本字段，也就是文章内容，一般一篇文章就一个page）
pageid contentid 文章内容
1 21 测试内容。。。
=================================
表schema（放自定义字段）
propertyid 频道id propertyName
33 1 小图
34 1 颜色
35 1 品牌
。。。。。
=================================
表property（放自定义字段的值）
contentid propertyid stringValue
21 33 /smallpic.jpg
21 34 红色
21 35 李宁
。。。。。。

================================================
现在想建一个视图，把频道id为1的所有文章全都列出来，包括所有的自定义字段和对应的值，如下：
频道id 标题 作者 发布时间 文章内容 【小图】 【颜色】 【品牌】

请各位大大帮忙啊，在线等啊！！！
...全文
286 12 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
12 条回复
切换为时间正序
请发表友善的回复…
发表回复
AVAmyZ 2009-05-14
  • 打赏
  • 举报
 回复
感谢josy 的帮助，正确的代码是
select 

a.columnid, a.title, a.author, a.publishdate, cast(b.content as varchar(8000)) as content, 

PRICE=max(case c.propertyName when 'PRICE' then d.string_Value else '' end), 

COLOR=max(case c.propertyName when 'COLOR' then d.string_Value else '' end), 

BRAND=max(case c.propertyName when 'BRAND' then d.string_Value else '' end) 

from 

cms_content a 

left join 

cms_pages b 

on 

a.id=b.contentid 

left join 

ChannelSchema c 

on 

a.columnid=c.channelid 

left join 

ContentProperty d 

on 

d.propertyid=c.propertyid and d.contentid=a.id 

where 

a.columnid=38 

group by a.columnid, a.title, a.author, a.publishdate, cast(b.content as varchar(8000))


因为content字段是Ntext类型，所以要转一下。
-狙击手- 2009-05-13
  • 打赏
  • 举报
 回复 
------------------------------------------------------------------------

-- Author:  happyflystone  

-- Date  :  2009-05-13 23:05:56

-- Ver:     Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86) 

--      	Apr 14 2006 01:12:25 

--      	Copyright (c) 1988-2005 Microsoft Corporation

--      	Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

--      

------------------------------------------------------------------------



-- Test Data: schema

IF OBJECT_ID('schema') IS NOT NULL 

    DROP TABLE [schema]

Go

CREATE TABLE [schema](propertyid INT,频道id INT,propertyName NVARCHAR(2))

Go

INSERT INTO [schema]

SELECT 33,1,'小图' UNION ALL

SELECT 34,1,'颜色' UNION ALL

SELECT 35,1,'品牌' 

GO

-- Test Data: property

IF OBJECT_ID('property') IS NOT NULL 

    DROP TABLE [property]

Go

CREATE TABLE [property](contentid INT,propertyid INT,stringValue NVARCHAR(13))

Go

INSERT INTO [property]

SELECT 21,33,'/smallpic.jpg' UNION ALL

SELECT 21,34,'红色' UNION ALL

SELECT 21,35,'李宁' UNION ALL

SELECT 21,36,'李宁1' UNION ALL

SELECT 21,37,'李宁2' 

GO

-- Test Data: content

IF OBJECT_ID('content') IS NOT NULL 

    DROP TABLE content

Go

CREATE TABLE content(contentid INT,频道id INT,作者 NVARCHAR(8),标题 NVARCHAR(8),发布时间 SMALLDATETIME)

Go

INSERT INTO content

SELECT 21,1,'admin','测试文章标题','2009-10-10' 

INSERT INTO content

SELECT 22,2,'admin1','测试文章标题1','2019-10-10' 

GO

-- Test Data: pages

IF OBJECT_ID('pages') IS NOT NULL 

    DROP TABLE pages

Go

CREATE TABLE pages(pageid INT,contentid INT,文章内容 NVARCHAR(7))

Go

INSERT INTO pages

SELECT 1,21,'测试内容。。。' 

GO

--Start

create proc pr_test

@id int

as

begin

declare @s varchar(1000),@s1 varchar(1000),@sql varchar(2000)

select @s1 = isnull(@s1+',','')+propertyName, @s = isnull(@s+',','')+'['+propertyName+']= max(case when propertyid = '

  +ltrim(propertyid)+' then stringvalue else '''' end)'

from [schema] where 频道id = @id

set @sql= ('select a.频道id,a.标题,a.作者,a.发布时间,p.文章内容,'+@s1+'

from [content] a 

left join pages p on a.contentid = p.contentid

left join (select contentid, '+@s+ ' from [property] group by contentid) c

on a.contentid = c.contentid

where a.频道id ='+ltrim(@id)+'')

exec(@sql)

end

go

exec pr_test 1

drop proc pr_test

--Result:

/*

频道id        标题     作者    发布时间                    文章内容    小图            颜色            品牌

----------- ------ ----- ----------------------- ------- ------------- ------------- -------------

1           测试文章标题 admin 2009-10-10 00:00:00     测试内容。。。 /smallpic.jpg 红色            李宁



*/

--End
-狙击手- 2009-05-13
  • 打赏
  • 举报
 回复
搞半天是固定的？
-狙击手- 2009-05-13
  • 打赏
  • 举报
 回复
不固定就用过程吧
-狙击手- 2009-05-13
  • 打赏
  • 举报
 回复 
------------------------------------------------------------------------

-- Author:  happyflystone  

-- Date  :  2009-05-13 23:05:56

-- Ver:     Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86) 

--      	Apr 14 2006 01:12:25 

--      	Copyright (c) 1988-2005 Microsoft Corporation

--      	Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

--      

------------------------------------------------------------------------



-- Test Data: schema

IF OBJECT_ID('schema') IS NOT NULL 

    DROP TABLE [schema]

Go

CREATE TABLE [schema](propertyid INT,频道id INT,propertyName NVARCHAR(2))

Go

INSERT INTO [schema]

SELECT 33,1,'小图' UNION ALL

SELECT 34,1,'颜色' UNION ALL

SELECT 35,1,'品牌' 

GO

-- Test Data: property

IF OBJECT_ID('property') IS NOT NULL 

    DROP TABLE [property]

Go

CREATE TABLE [property](contentid INT,propertyid INT,stringValue NVARCHAR(13))

Go

INSERT INTO [property]

SELECT 21,33,'/smallpic.jpg' UNION ALL

SELECT 21,34,'红色' UNION ALL

SELECT 21,35,'李宁' UNION ALL

SELECT 21,36,'李宁1' UNION ALL

SELECT 21,37,'李宁2' 

GO

-- Test Data: content

IF OBJECT_ID('content') IS NOT NULL 

    DROP TABLE content

Go

CREATE TABLE content(contentid INT,频道id INT,作者 NVARCHAR(5),标题 NVARCHAR(6),发布时间 SMALLDATETIME)

Go

INSERT INTO content

SELECT 21,1,'admin','测试文章标题','2009-10-10' 

GO

-- Test Data: pages

IF OBJECT_ID('pages') IS NOT NULL 

    DROP TABLE pages

Go

CREATE TABLE pages(pageid INT,contentid INT,文章内容 NVARCHAR(7))

Go

INSERT INTO pages

SELECT 1,21,'测试内容。。。' 

GO

--Start

create proc pr_test

@id int

as

begin

declare @s varchar(1000),@s1 varchar(1000)

select @s1 = isnull(@s1+',','')+propertyName, @s = isnull(@s+',','')+'['+propertyName+']= max(case when propertyid = '

  +ltrim(propertyid)+' then stringvalue else '''' end)'

from [schema] where 频道id = @id

exec ('select a.频道id,a.标题,a.作者,a.发布时间,p.文章内容,'+@s1+'

from [content] a 

left join pages p on a.contentid = p.contentid

left join (select contentid, '+@s+ ' from [property] group by contentid) c

on a.contentid = c.contentid')

end

go

exec pr_test 1

drop proc pr_test

--Result:

/*

频道id        标题     作者    发布时间                    文章内容    小图            颜色            品牌

----------- ------ ----- ----------------------- ------- ------------- ------------- -------------

1           测试文章标题 admin 2009-10-10 00:00:00     测试内容。。。 /smallpic.jpg 红色            李宁



*/

--End
百年树人 2009-05-13
  • 打赏
  • 举报
 回复
借jj的数据

create table content(contentid varchar(10),频道id varchar(10),作者 varchar(10),标题 varchar(20),发布时间 varchar(10))

insert into content

select '21','1','admin','测试文章标题','2009-10-10'

create table pages(pageid varchar(10),contentid varchar(10),文章内容 varchar(20))

insert into pages

select '1','21','测试内容。。'

create table  [schema](propertyid varchar(10),频道id varchar(10),propertyName varchar(10))

insert into [schema]

select '33','1','小图' union

select '34','1','颜色' union

select '35','1','品牌'

create table property (contentid varchar(10),propertyid varchar(10),stringValue varchar(20))

insert into property

select '21','33','/smallpic.jpg' union

select '21','34','红色' union

select '21','35','李宁'

go



create view V_Channel

as

select 

  a.频道id,

  a.标题,

  a.作者,

  a.发布时间,

  b.文章内容,

  [小图]=max(case c.propertyName when '小图' then d.stringValue else '' end),

  [颜色]=max(case c.propertyName when '颜色' then d.stringValue else '' end),

  [品牌]=max(case c.propertyName when '品牌' then d.stringValue else '' end)

from

  [content] a

left join

  [pages] b

on

  a.contentid=b.contentid

left join

  [schema] c

on

  a.频道id=c.频道id

left join

  [property] d

on

  d.propertyid=c.propertyid and d.contentid=a.contentid

where

  a.频道id=1

group by

  a.频道id,

  a.标题,

  a.作者,

  a.发布时间,

  b.文章内容



select * from V_Channel



/**



频道id       标题                   作者         发布时间       文章内容                 小图                   颜色                   品牌                   

---------- -------------------- ---------- ---------- -------------------- -------------------- -------------------- -------------------- 

1          测试文章标题               admin      2009-10-10 测试内容。。               /smallpic.jpg        红色                   李宁



（所影响的行数为 1 行）

**/
jinjazz 2009-05-13
  • 打赏
  • 举报
 回复 
create view v_test

as

select a.*,b.文章内容,c.小图,c.颜色,c.品牌 from content a 

inner join pages b on a.contentid=b.contentid

inner join

(

	select contentid,

		max(case when propertyName='小图' then stringValue else null end)小图,

		max(case when propertyName='颜色' then stringValue else null end)颜色,

		max(case when propertyName='品牌' then stringValue else null end)品牌

		 from schema t1 ,property t2 where t1.propertyid=t2.propertyid

		group by contentid

) c

on a.contentid=c.contentid
百年树人 2009-05-13
  • 打赏
  • 举报
 回复
try--
create view V_Channel

as

select 

  a.频道id,

  a.标题,

  a.作者,

  a.发布时间,

  b.文章内容,

  [小图]=case c.propertyName when '小图' then d.stringValue else '' end,

  [颜色]=case c.propertyName when '颜色' then d.stringValue else '' end,

  [品牌]=case c.propertyName when '品牌' then d.stringValue else '' end

from

  content a

left join

  pages b

on

  a.contentid=b.contentid

left join

  schema c

on

  a.频道id=c.频道id

left join

  property d

on

  d.propertyid=c.propertyid and d.contentid=a.contentid

where

  a.频道id=1
jinjazz 2009-05-13
  • 打赏
  • 举报
 回复 
--建立测试环境

declare @content table(contentid varchar(100),频道id varchar(100),作者 varchar(100),标题 varchar(100),发布时间 varchar(100))

insert into @content

select '21','1','admin','测试文章标题','2009-10-10'

declare @pages table(pageid varchar(10),contentid varchar(10),文章内容 varchar(100))

insert into @pages

select '1','21','测试内容。。'

declare @schema table(propertyid varchar(10),频道id varchar(10),propertyName varchar(10))

insert into @schema

select '33','1','小图' union

select '34','1','颜色' union

select '35','1','品牌'

declare @property table(contentid varchar(10),propertyid varchar(10),stringValue varchar(100))

insert into @property

select '21','33','/smallpic.jpg' union

select '21','34','红色' union

select '21','35','李宁'

 



select a.*,b.文章内容,c.小图,c.颜色,c.品牌 from @content a 

inner join @pages b on a.contentid=b.contentid

inner join

(

	select contentid,

		max(case when propertyName='小图' then stringValue else null end)小图,

		max(case when propertyName='颜色' then stringValue else null end)颜色,

		max(case when propertyName='品牌' then stringValue else null end)品牌

		 from @schema t1 ,@property t2 where t1.propertyid=t2.propertyid

		group by contentid

) c

on a.contentid=c.contentid



/*

21	1	admin	测试文章标题	2009-10-10	测试内容。。	/smallpic.jpg	红色	李宁

*/
AVAmyZ 2009-05-13
  • 打赏
  • 举报
 回复
，【小图】 【颜色】 【品牌】这些对于一个频道而言是固定的，在这个视图中就当是固定的吧！
AVAmyZ 2009-05-13
  • 打赏
  • 举报
 回复
顶上去啊，在线等哦，各位大大！
-狙击手- 2009-05-13
  • 打赏
  • 举报
 回复
要行列转呀，【小图】 【颜色】 【品牌】 这些是动态的？

Spring+SpringMVC+Mybatis框架整合例子(SSM) 下载
Mybatis则是一个轻量级的持久层框架，它解决了JDBC的繁琐代码问题，提供了更方便的SQL映射和对象关系映射。Mybatis允许开发者直接编写SQL，提高了查询效率和灵活性，同时通过XML或注解方式配置和映射原生信息，将...
SQL解决视图问题
好不容易在安装中解决了字符集问题，然而在练习视图这一部分时发现只要牵扯上文字，就会出错！！mmmm，希望下一代SQL能彻底解决这个问题!!!废话不多说，直接看问题： 这是怎么回事？？我的student表中有女孩纸啊，...
SqlServer视图——视图的创建与使用
这里是引用在数据查询中，可以看到数据表设计过程中，考虑到数据的冗余度低、数据一致性等问题，通常对数据表的设计要满足范式的要求，因此也会造成一个实体的所有信息保存在多个表中。 当检索数据时，往往在一个表...
mysql和sql视图连接_SQL视图和多表连接
本篇博客关注的焦点是视图的使用以及视图和多表连接的配合。以便可以了解视图，以及更好的使用视图。首先，还是要说明一下视图的...如图所示是机房收费系统中的《User_sys》和《CheckInfo_sys》建立的一个视图的关系...
SQL视图的概念和使用
SQL视图的创建和使用视图这个东西在其他的软件中，我们也经常看得到，例如word中就有视图，我们不难发现，视图似乎是一种显示方式，就像WORD一样，有几种显示版面，这就是视图，那么SQL中的视图又该怎么定义呢？...
MS-SQL Server

34,837

社区成员

254,632

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章