select b.typename,b.shortname,subcount=count(1) from
tab a
inner join
(
select typename,shortname,pid from tab where pid=0
) b
on a.id=b.pid
group by b.typename,b.shortname,b.pid
解决方案 »
- SQL语句
- linux+mysql+php VS windows+sqls+asp
- 在同一个INSERT语句中将从一个表中查询出的记录(多条)插入到另外两个表中,并且在插入前先要对每条记录的数据进行处理后再插入,这样可行否?方法如何?急切盼答!!!
- 无法用排他锁锁定该数据库,以执行该操作。"是什么意思怎样解决!谢谢
- 关于sql语句的分组问题,着急啊,谢谢前辈们了
- ///怎么统计一个值在字段里出现过几次???
- replace里通配符的问题
- 根据数据库内的IP记录如SQL指定标内或者WEB 如ASP程序 来进行 防火墙或IP策略配置 从而实现WEB得到客户IP服务器授权访问指定端口的办法?
- sql2000的错误信息为什么不显示?
- 急!急!急!急!急!求教,高分相送
- 如何让数据在SQL里面按一定规则排序?
- 怎样把A库B表复制为C库B表?
tab a
inner join
(
select id,typename,shortname from tab where pid=0
) b
on a.pid=b.id
group by b.typename,b.shortname,b.id
select a.Typename,a.ShortName,(select count(id) form 表 where pid=a.id) form 表 a where PID=0
select a.Typename,a.ShortName,(select count(id) form 表 where pid=a.id)as subcount form 表 a where a.PID=0
drop table MOVIE
Go
Create table MOVIE([ID] int,[PID] int,[Typename] nvarchar(5),[ShortName] nvarchar(2))
Insert MOVIE
select 1,0,N'动漫',N'动漫' union all
select 3,0,N'综艺',N'综艺' union all
select 4,3,N'行规划',N'达到' union all
select 5,0,N'剧集',N'剧集' union all
select 6,1,N'覆盖特瑞特',N'达到' union all
select 7,0,N'电影',N'电影' union all
select 8,7,N'动作',N'动作' union all
select 9,7,N'爱情',N'爱情' union all
select 10,5,N'国产',N'国产' union all
select 11,5,N'港台',N'港台' union all
select 13,7,N'战争',N'战争'
Go
Select * from MOVIESELECT A.TYPENAME,A.SHORTNAME,SUBCOUNT=COUNT(1)
FROM MOVIE A
INNER JOIN MOVIE B
ON A.ID=B.PID
WHERE A.PID=0
GROUP BY A.TYPENAME,A.SHORTNAME
ORDER BY SUBCOUNT DESC/*
TYPENAME SHORTNAME SUBCOUNT
-------- --------- -----------
电影 电影 3
剧集 剧集 2
综艺 综艺 1
动漫 动漫 1(所影响的行数为 4 行)*/
(
ID int,
PID int,
Typename nvarchar(20),
ShortName nvarchar(20)
)
insert into #C
select 1, 0, '动漫', '动漫' union all
select 3, 0, '综艺', '综艺' union all
select 4, 3, '行规划', '达到' union all
select 5, 0, '剧集', '剧集' union all
select 6, 1, '覆盖特瑞特', '达到' union all
select 7, 0, '电影', '电影' union all
select 8, 7, '动作', '动作' union all
select 9, 7, '爱情', '爱情' union all
select 10, 5, '国产', '国产' union all
select 11, 5, '港台', '港台' union all
select 13, 7, '战争', '战争'
select Typename,ShortName,cnt from #C A,
(select PID,COUNT(1) cnt from #C group by PID) B
where A.ID=B.PID and A.PID=0
table_A a
inner join
(
select typename,shortname,pid from table_A where pid=0
) b
on a.id=b.pid
group by b.typename,b.shortname,b.pid
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] int,[PID] int,[Typename] varchar(10),[ShortName] varchar(4))
insert [tb]
select 1,0,'动漫','动漫' union all
select 3,0,'综艺','综艺' union all
select 4,3,'行规划','达到' union all
select 5,0,'剧集','剧集' union all
select 6,1,'覆盖特瑞特','达到' union all
select 7,0,'电影','电影' union all
select 8,7,'动作','动作' union all
select 9,7,'爱情','爱情' union all
select 10,5,'国产','国产' union all
select 11,5,'港台','港台' union all
select 13,7,'战争','战争'
---查询---
select
Typename,
ShortName,
subcount=(select count(1) from tb where pid=t.id)
from [tb] t
where pid=0
---结果---
Typename ShortName subcount
---------- --------- -----------
动漫 动漫 1
综艺 综艺 1
剧集 剧集 2
电影 电影 3(所影响的行数为 4 行)