• 全部
  • 基础类
  • 应用实例
  • 新技术前沿

一个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 标题 作者 发布时间 文章内容 【小图】 【颜色】 【品牌】

请各位大大帮忙啊,在线等啊!!!
...全文
242 点赞 收藏 12
写回复
12 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
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
要行列转呀,【小图】 【颜色】 【品牌】 这些是动态的?

回复
相关推荐
发帖
MS-SQL Server
创建于2007-09-28

3.3w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
帖子事件
创建了帖子
2009-05-13 10:53
社区公告
暂无公告