select role_name, r.roleID, nCol, nInfo, role_typeID
from E_role as r,
(select roleID, count(*) as nCol from E_p_column_role group by roleID) as c,
(select roleID, count(*) as nInfo from E_role_info group by roleID) as i
where r.roleID = c.roleID and r.roleID = i.roleID;
如语句所示,意在统计r表中roleID对应c,i表的数量是多少。
语句这么写效率很差,能不能改成联合查询呢?
from E_role as r inner join E_p_column_role c on r.roleID = c.roleID
inner join E_role_info i on r.roleID = i.roleID
group by r.role_name, r.role_typeID
你现在这个不是也可以吗?有什么方面的问题?
from E_role as r inner join E_p_column_role c on r.roleID = c.roleID
inner join E_role_info i on r.roleID = i.roleID
group by r.role_name, r.role_typeID ,r.roleID
好像并不支持count(b.*)这样的语法!
from E_role as r inner join E_p_column_role c on r.roleID = c.roleID
inner join E_role_info i on r.roleID = i.roleID
group by r.role_name, r.role_typeID ,r.roleID
from E_role as r inner join E_p_column_role c on r.roleID = c.roleID
inner join E_role_info i on r.roleID = i.roleID
group by r.role_name, r.role_typeID ,r.roleID不能改成这样,会导致效率可能更差。要看你的具体情况。
最好提供你的 show index from
select r.role_name, r.role_typeID ,r.roleID, count(distinct i.主键), count(distinct c.主键)
from E_role as r inner join E_p_column_role c on r.roleID = c.roleID
inner join E_role_info i on r.roleID = i.roleID
group by r.role_name, r.role_typeID ,r.roleID
这种方法为什么会差?是如何运作的呢?
ID
1
2t2
ID col1
1 A1
1 A3
2 A4
2 A5t3
1 D1
1 D4JOIN 之后,对T2T3的统计显然不正确!什么索引都没有的情况下,还是你顶楼的原语句最合适。
如此做的话,r表id在t1的个数是为cnt1,在t2为cnt2,
如此统计的话,看似没错啊?
版主说的 对T2T3的统计显然不正确! 指的是什么地方呢?