select id, b.tag, totalhot from
(
select tag, count(hot) as totalhot
from flowerinfo join flower_reflect
on flowerinfo.id = flower_reflect.flowerid
) a join flower_tag b
on a.tag = b.id
(
select tag, count(hot) as totalhot
from flowerinfo join flower_reflect
on flowerinfo.id = flower_reflect.flowerid
) a join flower_tag b
on a.tag = b.id
列出标签编号,标签名字,和总共人气指数
>>>
--SQL 2005
;
WITH CTE
AS
(
SELECT id,tag,fid
FROM Flower_tag
WHERE ID=@要查询的标签编号
UNION ALL
SELECT a.id,a.tag,a.fid
FROM Flower_tag a,CTE b
WHERE a.fID=b.ID)
SELECT ID,Tag,(
SELECT SUM(hot) as totalHot
FROM CTE a INNER JOIN flower_reflect b
ON a.id=b.tag
INNER JOIN flowerinfo c
ON b.flowerid=c.ID
) as 总人气指数
FROM flower_tag
WHERE ID=@要查询的标签编号
--往标签表加入标签信息记录
insert into flower_tag(id,tag,fid) values('1','节日专场','0')
insert into flower_tag(id,tag,fid) values('2','鲜花用途分类','0')
insert into flower_tag(id,tag,fid) values('3','花材分类','0')--根据节日分类的标签
insert into flower_tag(id,tag,fid) values('1-29','中国鬼节','1')
insert into flower_tag(id,tag,fid) values('1-30','姊妹节','1')
insert into flower_tag(id,tag,fid) values('1-31','教师节','1')
insert into flower_tag(id,tag,fid) values('1-32','祖父祖母日','1')
insert into flower_tag(id,tag,fid) values('1-33','中秋节','1')
insert into flower_tag(id,tag,fid) values('1-34','花周','1')
insert into flower_tag(id,tag,fid) values('1-35','秋季第一天','1')
--根据用途分类
insert into flower_tag(id,tag,fid) values('2-1','生日鲜花','2')
insert into flower_tag(id,tag,fid) values('2-2','慰问鲜花','2')
insert into flower_tag(id,tag,fid) values('2-3','爱情鲜花','2')
insert into flower_tag(id,tag,fid) values('2-4','友情鲜花','2')
insert into flower_tag(id,tag,fid) values('2-5','婚礼鲜花','2')
insert into flower_tag(id,tag,fid) values('2-6','哀思鲜花','2')--根据材料分类
insert into flower_tag(id,tag,fid) values('3-5','马蹄莲花','3')
insert into flower_tag(id,tag,fid) values('3-6','康乃馨花','3')
insert into flower_tag(id,tag,fid) values('3-7','太阳菊花','3')
insert into flower_tag(id,tag,fid) values('3-8','巧克力花束','3')
insert into flower_tag(id,tag,fid) values('3-9','礼盒鲜花','3')下面是鲜花信息
insert into flowerinfo(name,price,detail,flowersay,quantity,path)
values
('我的心里只有你 ',
609,
'99枝粉玫瑰,叶上花外围(如当地没有叶上花,就用黄莺代替),内衬白色纱网,粉色绉纹纸外包装,配同色蝴蝶结。',
'神给了我一双眼,我用来整天看你;神给了我一颗心,我用来整夜想你。我的眼里全是你,我的心里只有你 ',
100,
'flower_image/c45bfd9f015d4d389e46d1fcb922d676.jpg')insert into flowerinfo(name,price,detail,flowersay,quantity,path)
values
('爱上微笑的你 ',
399,
'36枝极品昆明红玫瑰(心型),配绿叶,满天星,紫红色进口羽纱外围,
2只超可爱兜兜狗(目前兜兜熊已缺货,将用兜兜狗代替,敬请谅解!)放入花丛中,
每只各怀抱一颗金莎巧克力,进口的虎皮纸单面包装。(限送深圳) ',
'爱上你的微笑 /一夜睡不著 /怎么办才好/ 因为你的微笑 /让我神魂颠倒 ',
100,
'flower_image/e6994d323343b6b6384f8efb13d97fe6.jpg')
insert into flowerinfo(name,price,detail,flowersay,quantity,path)
values
('爱情的瞬间 ',
299,
'19朵极品昆明红玫瑰,中间2枝多头白香水百合,黄莺丰满间插,浅紫色绉纹纸圆形包装,橙色蝴蝶结束扎。',
'我为你收集了大自然所有的美,放在你生日的烛台上。将能说的话都藏在花蕾里,让它成为待放的秘密。生日快乐! ',
100,
'flower_image/e45aee66f769b03f6c624f61cd60a963.jpg')
添加了标签和鲜花信息,现在把鲜花和标签联系起来,往第三张表添加数据(鲜花id是自动增长)insert into flower_reflect(flowerid,tag)values(1,'1-29')--第一朵鲜花根据节日 用于中国鬼节
insert into flower_reflect(flowerid,tag)values(1,'1-30')--第一朵鲜花根据节日 用于姊妹节
insert into flower_reflect(flowerid,tag)values(1,'1-31')--第一朵鲜花根据节日 用于教师节insert into flower_reflect(flowerid,tag)values(2,'2-1')--第二朵鲜花根据用途用于生日鲜花
insert into flower_reflect(flowerid,tag)values(2,'2-2')--第二朵鲜花根据用途用于慰问鲜花
insert into flower_reflect(flowerid,tag)values(2,'2-3')--第二朵鲜花根据用途用于爱情鲜花
insert into flower_reflect(flowerid,tag)values(3,'3-5')--第三朵鲜花根据花材是马蹄鲜花
insert into flower_reflect(flowerid,tag)values(3,'3-6')--第三朵鲜花根据花材是康乃馨花
insert into flower_reflect(flowerid,tag)values(3,'3-7')--第三朵鲜花根据花材是太阳菊花
假设现在有人卖了不同的鲜花,那么鲜花的人气将增加,(卖一朵加1)
假设1,2,3号鲜花分别卖了1朵,2,朵,3朵,现在要查处所有
标签编号,标签名称,标签人气那么结果应该是
'1','节日专场',1
'1-29','中国鬼节,1
'1-30','姊妹节,1
'1-31','教师节',1
'2','鲜花用途分类',2
'2-1,'生日鲜花',2
'2-2,'慰问鲜花',2
'2-3,'爱情鲜花',2如下类推
'3','花材分类',3)其他就是0
;
WITH CTE
AS
(
SELECT id,tag,fid ,id as grp
FROM Flower_tag
WHERE fid=0
UNION ALL
SELECT a.id,a.tag,a.fid,a.fid
FROM Flower_tag a,CTE b
WHERE a.fID=b.ID) SELECT a.ID,a.tag,a.grp,c.hot
INTO #t
FROM CTE a LEFT JOIN flower_reflect b
ON a.ID=b.tag
LEFT JOIN flowerinfo c
ON b.flowerid=c.id
WHERE a.fid =0 OR c.id is not null
ORDER BY grp,IDSELECT ID,tag,(SELECT MAX(hot) FROM #t WHERE grp=a.grp) as totalhot
FROM #t aDROP TABLE #t
AS
(
select a.id,c.id tagid,a.hot,c.tag,c.fid from flowerInfo a
join flower_reflect b
on a.id=b.flowerid
right join flower_tag c
on b.tag=c.id
)select
tagid 标签编号,tag 标签名称,hot 标签人气
from
(
--有父编号的标签
select tagid,tag ,hot
from cte a
where fid<>0
group by tagid,tag,fid,hotunion all
--没有父标签编号的标签
select tagid,tag ,
hot=(select max(hot) from cte where fid=a.tagid)
from cte a
where tagid in (select tagid from cte where fid=0)
group by tagid,tag,fid,hot
) T
where hot is not null
order by tagid
/*
标签编号 标签名称 标签人气
----- -------------------- -----------
1 节日专场 1
1-29 中国鬼节 1
1-30 姊妹节 1
1-31 教师节 1
2 鲜花用途分类 2
2-1 生日鲜花 2
2-2 慰问鲜花 2
2-3 爱情鲜花 2
3 花材分类 3
3-5 马蹄莲花 3
3-6 康乃馨花 3
3-7 太阳菊花 3
*/