select * from 用户表 where user_name in (select con_usernameid from 作品表 where con_name='作品')
select * from 用户表 where user_ID in (select con_usernameid from 作品表 where con_name='作品') 通过对应连接的字段连接 select 用户表.*,作品 from 用户表,作品表 where 用户表.user_ID=作品表.con_usernameid
方法很多: select * from 用户表 where exests (select * from 作品表 where con_usernameid=用户表.user_ID and con_name='作品')
SELECT A.user_name, B.CT AS 作品数 FROM 用户表 A LEFT JOIN (SELECT Con_usernameID, COUNT(1) AS CT FROM 作品表 GROUP BY Con_usernameID) B ON A.user_ID = B.Con_usernameID ORDER BY B.CT
select a.user_name,sum(b.con_id) as 作品数 from 用户表 a,作品表 b where a.user_ID = b.Con_usernameID group by a.user_name
通过对应连接的字段连接
select 用户表.*,作品 from 用户表,作品表 where 用户表.user_ID=作品表.con_usernameid
select * from 用户表 where exests (select * from 作品表 where con_usernameid=用户表.user_ID
and con_name='作品')
FROM 用户表 A
LEFT JOIN (SELECT Con_usernameID, COUNT(1) AS CT FROM 作品表 GROUP BY Con_usernameID) B
ON A.user_ID = B.Con_usernameID
ORDER BY B.CT
from 用户表 a,作品表 b
where a.user_ID = b.Con_usernameID
group by a.user_name