SELECT
A.*
FROM nuser AS A
JOIN(
SELECT
username
FROM news
GROUP BY username
HAVING COUNT(*)>=5
) AS B
ON A.name=B.username
A.*
FROM nuser AS A
JOIN(
SELECT
username
FROM news
GROUP BY username
HAVING COUNT(*)>=5
) AS B
ON A.name=B.username
调试欢乐多
where (select count(1) from news where username=nuser.name)>=5id name
----------- --------------------------------------------------
1 a(所影响的行数为 1 行)
select a.name from nuser a left join news b
on a.name=b.name group by a.name having Count(b.name)>5
(
[id] int primary key identity(1,1),
[name] varchar(50)
)
insert nuser
select 'a' union all
select 'b' union all
select 'c'
go
create table news
(
[id] int primary key identity(1,1),
[username] varchar(50),
)
insert news
select 'a' union all
select 'a' union all
select 'a' union all
select 'a' union all
select 'a' union all
select 'b' union all
select 'b' union all
select 'c'
go
SELECT * FROM nuser WHERE [name] IN(SELECT [username] FROM NEWS GROUP BY [username] HAVING COUNT([username] )>=5)
drop table nuser
drop table news
/*id name
----------- --------------------------------------------------
1 a*/
from nuser a,(
select [username]
from news
group by [username]
having count(1)>5
) as t
where a.[name]=t.[username]
INNER JOIN (select COUNT(1) AS cnt,[username] from news
group by [username]) A On A.[username]=B.name
where A.cnt>=5
SELECT B.*
FROM nuser AS B
INNER JOIN
(SELECT [username] FROM news GROUP BY [username] HAVING COUNT([username])>5) AS A
ON A.[name]=B.[username]