Category表结构(无限分类结构)
CategoryId,CategoryName,ParentIdNews表(其中Category1,Category2,Category3分别表示一级分类,二级分类,三级分类)
Category1,Category2,Category3,NewsId,Title...需求是统计出所有三级分类下的新闻数量,通过下面语句可以得到1级分类的,二、三级的不会弄
select c1.CategoryId,COUNT(n.NewsId) AS Count from News as n
LEFT JOIN Category as c1 ON n.Category1=c1.CategoryId
GROUP BY c1.CategoryId
CategoryId,CategoryName,ParentIdNews表(其中Category1,Category2,Category3分别表示一级分类,二级分类,三级分类)
Category1,Category2,Category3,NewsId,Title...需求是统计出所有三级分类下的新闻数量,通过下面语句可以得到1级分类的,二、三级的不会弄
select c1.CategoryId,COUNT(n.NewsId) AS Count from News as n
LEFT JOIN Category as c1 ON n.Category1=c1.CategoryId
GROUP BY c1.CategoryId
解决方案 »
- 请教:sql server 2005连接字符串无效
- PHP连接mssql数据库问题(非常着急)进来就有分。解决再送200分
- 求一对字段格式化的SQL语句
- mysql正则表达式,判断字符串长度的问题
- 在SQL中怎么判断一个数是另一个数的整数倍呢
- Windows2003 安装 SQL2000Server 出现问题
- 字符串赋值问题,急急!!!!!!!!
- 两个数据库的问题?等待!
- 刚刚接触数据库,应当学些什么?
- 通过sp_helpdb得到的db_size大小和数据库中所有表的reserved之和大小不一样
- sqlserver里有一千万条数据怎样优化更快的查询出来?
- 请教VisualStudio2010中附带的SQL如何使用链接语句
(
select 一级 from(
select a.CategoryId as 一级,b.CategoryId as 二级,c.CategoryId as 三级 from Category a
left join Category b on b.ParentIdID=a.CategoryId
left join Category c on c.ParentId=b.CategoryId) a
)select COUNT(*) as 二级数 from news where Category2 in
(
select 二级 from(
select a.CategoryId as 一级,b.CategoryId as 二级,c.CategoryId as 三级 from Category a
left join Category b on b.ParentIdID=a.CategoryId
left join Category c on c.ParentId=b.CategoryId) a
)select COUNT(*) as 三级数 from news where Category3 in
(
select 三级 from(
select a.CategoryId as 一级,b.CategoryId as 二级,c.CategoryId as 三级 from Category a
left join Category b on b.ParentIdID=a.CategoryId
left join Category c on c.ParentId=b.CategoryId) a
)
with cte as (
select n.Category1,n.Category2,n.Category3,c1.CategoryId ,n.NewsId from News as n
LEFT JOIN Category as c1 ON n.Category1=c1.CategoryId
)
select n.Category1 ,count(NewsId) as Count from cte
/****
既然n.Category1=c1.CategoryId 那统计一级的时候可以用Category1这个字段,
那统计二级的时候可以用Category2
三级的时候可以用Category3,依次类推就可以了
****
(
select 一级 from(
select a.CategoryId as 一级,b.CategoryId as 二级,c.CategoryId as 三级 from Category a
left join Category b on b.ParentIdID=a.CategoryId
left join Category c on c.ParentId=b.CategoryId) a
)
union
select 2,COUNT(*) from news where Category2 in
(
select 二级 from(
select a.CategoryId as 一级,b.CategoryId as 二级,c.CategoryId as 三级 from Category a
left join Category b on b.ParentIdID=a.CategoryId
left join Category c on c.ParentId=b.CategoryId) a
)
union
select 3,COUNT(*) from news where Category3 in
(
select 三级 from(
select a.CategoryId as 一级,b.CategoryId as 二级,c.CategoryId as 三级 from Category a
left join Category b on b.ParentIdID=a.CategoryId
left join Category c on c.ParentId=b.CategoryId) a
)我直接拿上面哥们的代码无脑改下,哥们别介意。