求一条sql语句.(找出每个主人有多少仆人,仆人多的排在前面)

[bbsad]

在同一表
a_tbl中字段为

id,idid,name,flag
其中id为序号
name为主人
flag=1为主人
flag=0为仆人
idid为主仆关系
sql关系说明
select * from a_tbl where flag=0主人
select * from a_tbl where flag=1仆人
select * from a_tbl where flag=0 and id=1 列出主人id为1的仆人
现在要求一sql语句.(找出每个主人有多少仆人,仆人多的排在前面)

[clark328]

SELECT A.id as id,A.name as name,count(B.id) as count1 from company as A left join person as B on B.idid=A.id group by A.id, A.name order by count(B.id) desc
试一试吧。。。

[clark328]

等一等，，上面那句少了两个条件，，看这一句
SELECT A.id as id,A.name as name,count(B.id) as count1 from a_tbl as A left join a_tbl as B on B.idid=A.id and A.flag=0 and B.flag=1 group by A.id, A.name order by count(B.id) desc

[bbsad]

哦..太感谢了..我试试看.

[dh20156]

TRY:
Create Table a_tbl([id] int,idid int,pname varchar(20),flag int)
Insert into a_tbl Select 1,0,'ma',0
union Select 2,0,'mb',0
union Select 3,1,'mc',1
union Select 4,1,'md',1
union Select 5,3,'me',1
union Select 6,1,'mf',1
union Select 7,2,'mg',1

Select [id],pname,(Select Count(*) From [a_tbl] Where idid=a.[id]) As ct From [a_tbl] a Where flag=0 Group by [id],pname

Drop Table a_tbl

--结果：
（所影响的行数为 7 行）

id pname ct
----------- -------------------- -----------
1 ma 3
2 mb 1

（所影响的行数为 2 行）

[dh20156]

或者：
SELECT A.[id],A.pname,count(B.[id]) as count1 from a_tbl as A left join a_tbl as B on B.idid=A.[id] and A.flag=0 and B.flag=1 Where a.flag=0 group by A.[id], A.pname order by count(B.[id]) desc

[bbsad]

好像不行呀..得不出我的结果呀..

[bbsad]

真是高手..感觉GOod