有两个表
I_PhotoType(Pay_Type表示父结点,TypePath表示路径)
TypeID TypeName TypeRe Pay_Type TypePath
81 相册类型 0 0
82 婚礼 婚礼相册 81 0,81
83 婚纱 婚纱相册 82 0,81,82
84 庆祝 81 0,81
85 婚宴 婚宴相册 82 0,81,82
另一张表是T_PhotoGallery
PhotoID TypeID PhotoName SmallPhoto
70 83 婚纱1 uploadfiles/20104114551134_detail.gif
71 83 123 uploadfiles/20104115172248_detail.gif
72 82 345 uploadfiles/20104118543852_detail.jpg
通过一条查询语句,查出婚礼下两个子类婚纱,婚宴,另调出一张图片(子类下有很多图片取第一张),如果在T_PhotoGallery表中没有I_PhotoType的图片,像婚宴就没有一张图片,就是一张无图的图片路径'images/nopic.jpg'
我写了一个,但有问题就是婚纱会多两张,一个子类下只有一张图片,子类的子类不去管它
select a.TypeID,a.TypeName,isnull(SmallPhoto,'images/nopic.jpg') as SmallPhoto from I_photoType a
left join T_PhotoGallery b on a.typeID=b.typeID
where ','+TypePath+'/' like '%,82/%'
请高手帮忙,急,正解立结贴
I_PhotoType(Pay_Type表示父结点,TypePath表示路径)
TypeID TypeName TypeRe Pay_Type TypePath
81 相册类型 0 0
82 婚礼 婚礼相册 81 0,81
83 婚纱 婚纱相册 82 0,81,82
84 庆祝 81 0,81
85 婚宴 婚宴相册 82 0,81,82
另一张表是T_PhotoGallery
PhotoID TypeID PhotoName SmallPhoto
70 83 婚纱1 uploadfiles/20104114551134_detail.gif
71 83 123 uploadfiles/20104115172248_detail.gif
72 82 345 uploadfiles/20104118543852_detail.jpg
通过一条查询语句,查出婚礼下两个子类婚纱,婚宴,另调出一张图片(子类下有很多图片取第一张),如果在T_PhotoGallery表中没有I_PhotoType的图片,像婚宴就没有一张图片,就是一张无图的图片路径'images/nopic.jpg'
我写了一个,但有问题就是婚纱会多两张,一个子类下只有一张图片,子类的子类不去管它
select a.TypeID,a.TypeName,isnull(SmallPhoto,'images/nopic.jpg') as SmallPhoto from I_photoType a
left join T_PhotoGallery b on a.typeID=b.typeID
where ','+TypePath+'/' like '%,82/%'
请高手帮忙,急,正解立结贴
解决方案 »
- 求,哪里有初级sqlserver的教程啊?完整版的
- 请问如何对比版本号才能知道我下载的SQL Server 2005要不要安装SP2呢?
- 打sp4时程序一直卡在"安装程序正在准备复制文件.."
- 为何备份的数据库变的越来越大?
- 求一个sql方法或者语句
- 小弟求救。无法解决 equal to 操作的排序规则冲突的错误。详情请见正文。
- 请问这个SQL哪里有问题?
- 得到相同的结果,以下哪种方式查询的速度是最快的!!
- sql 事件探查器能否对某个表,或某个updata监控记录。
- sql server 2005+delphi7 用 联合查询 query1.edit修改查询值 报cannot be modified 错误
- 查询数据库中表和表对应的主键名称
- 请教SQL语句~~~在线等高手~~~
-- Author :fredrickhu(小F,向高手学习)
-- Date :2010-04-02 09:44:21
-- Verstion:
-- Microsoft SQL Server 2005 - 9.00.4053.00 (Intel X86)
-- May 26 2009 14:24:20
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[I_PhotoType]
if object_id('[I_PhotoType]') is not null drop table [I_PhotoType]
go
create table [I_PhotoType]([TypeID] int,[TypeName] varchar(8),[TypeRe] varchar(8),[Pay_Type] varchar(4),[TypePath] varchar(7))
insert [I_PhotoType]
select 81,'相册类型','0','0',null union all
select 82,'婚礼','婚礼相册','81','0,81' union all
select 83,'婚纱','婚纱相册','82','0,81,82' union all
select 84,'庆祝','81','0,81',null union all
select 85,'婚宴','婚宴相册','82','0,81,82'
--> 测试数据:[T_PhotoGallery]
if object_id('[T_PhotoGallery]') is not null drop table [T_PhotoGallery]
go
create table [T_PhotoGallery]([PhotoID] int,[TypeID] int,[PhotoName] varchar(5),[SmallPhoto] varchar(37))
insert [T_PhotoGallery]
select 70,83,'婚纱1','uploadfiles/20104114551134_detail.gif' union all
select 71,83,'123','uploadfiles/20104115172248_detail.gif' union all
select 72,82,'345','uploadfiles/20104118543852_detail.jpg'
--------------开始查询--------------------------
;with f as
(
select
a.TypeID,a.TypeName,isnull(SmallPhoto,'images/nopic.jpg') as SmallPhoto
from
I_photoType a
left join
T_PhotoGallery b
on
a.typeID=b.typeID
where
','+TypePath+'/' like '%,82/%'
)
select
*
from
f t
where
SmallPhoto=(select min(SmallPhoto) from f where typeid=t.typeid)
----------------结果----------------------------
/* TypeID TypeName SmallPhoto
----------- -------- -------------------------------------
83 婚纱 uploadfiles/20104114551134_detail.gif
85 婚宴 images/nopic.jpg(2 行受影响)
*/
if object_id('[I_PhotoType]') is not null drop table [I_PhotoType]
go
create table [I_PhotoType]([TypeID] int,[TypeName] varchar(8),[TypeRe] varchar(8),[Pay_Type] varchar(4),[TypePath] varchar(7))
insert [I_PhotoType]
select 81,'相册类型','0','0',null union all
select 82,'婚礼','婚礼相册','81','0,81' union all
select 83,'婚纱','婚纱相册','82','0,81,82' union all
select 84,'庆祝','81','0,81',null union all
select 85,'婚宴','婚宴相册','82','0,81,82'
--> 测试数据:[T_PhotoGallery]
if object_id('[T_PhotoGallery]') is not null drop table [T_PhotoGallery]
go
create table [T_PhotoGallery]([PhotoID] int,[TypeID] int,[PhotoName] varchar(5),[SmallPhoto] varchar(37))
insert [T_PhotoGallery]
select 70,83,'婚纱1','uploadfiles/20104114551134_detail.gif' union all
select 71,83,'123','uploadfiles/20104115172248_detail.gif' union all
select 72,82,'345','uploadfiles/20104118543852_detail.jpg'select a.TypeID,a.TypeName,isnull(SmallPhoto,'images/nopic.jpg') as SmallPhoto from I_photoType a
left join T_PhotoGallery b on a.typeID=b.typeID
where ','+TypePath+'/' like '%,82/%'
and not exists(select 1 from T_PhotoGallery where TypeID=b.TypeID and SmallPhoto<b.SmallPhoto)
/*
TypeID TypeName SmallPhoto
----------- -------- -------------------------------------
83 婚纱 uploadfiles/20104114551134_detail.gif
85 婚宴 images/nopic.jpg(2 行受影响)
*/借用小F的表数据