高难度SQL统计问题

[chshb38]

表中有字段id companyid adminid logintime
是这个计算企业登陆记录
现在想查询很个adminid管理员下的登陆超过三次（每次登陆都有添加一条数据，数据超过三条的）的企业客户

这里面会有两次统计，一次是SELECT count(company_id) as c_login,company_id,admin_id FROM company_login GROUP BY company_id,admin_id

这已经生成一个表，然后在这个表中再次统计，记录过三条的记录
请高手帮忙，最好能写出个存储过程，

也可加QQ40321289

[idayu]

SELECT count(company_id) as c_login,company_id,admin_id
FROM company_login
GROUP BY company_id,admin_id
having count(*)>=3

[zhchg6666]

up

[kjq_vb]

是不是这样?
select company_id from(SELECT count(company_id) as c_login,company_id,admin_id FROM company_login GROUP BY company_id,admin_id) a where c_login>3

[绝代坏坏]

先创建一个试图view_1
SELECT count(*) ,admin_id FROM company_login GROUP BY admin_id having count>=3
然后从试图里做select

SELECT count(company_id) as c_login,company_id,admin_id FROM company_login where(admin_id in (select admin_id from view_1) )

如果用存储过程实现可以把建立视图的过程写到临时表里。后面的就一样了。

[liubin911]

方法是在分组里价格条件，1楼的方法 conunt(*) 我感觉不大对 个人认为应该是对查询结果的company_id进行判断

SELECT count(company_id) as c_login,company_id,admin_id FROM company_login GROUP BY company_id,admin_id having count(company_id)>2

[ezhuyin]

3楼 kjq_vb(跟着MS走) 的方法应该可行。

[chshb38]

juedaihuaihuai(绝代坏坏（beyond myself）)
谢谢你的提议，但我建立存储过程后又碰到新的问题，麻烦你了，解决完马上给分
CREATE proc p_tongji
@cishu int, --登陆次数
@tiaojian varchar(4000) = '' -- 查询条件 (注意: 不要加 where)
as
declare @strsql varchar(5000) -- 主语句
declare @sql varchar(5000)
begin
create table #table1
(
c_login int,
companyid int,
adminid int
)
if @tiaojian =''

set @tiaojian='1=1'
set @sql= 'insert into #table1 SELECT count(company_id) as c_login,company_id,admin_id FROM company_login where ' + @tiaojian + ' GROUP BY company_id,admin_id'
exec (@sql)
set @sql='select count(*) as num ,adminid from #table1 group by adminid'
exec (@sql)
end
GO
以上是存储过程
代码：Set rs=conn.execute("execute p_tongji 1,'"&tiaojian&"'")'execute p_tongji 登陆次数，条件
'set rs=conn.execute("select * from company_login")
do until rs.eof
If rs("adminid")=0 Then
admin_xingming="系统管理员"
else
m_SQL = "SELECT admin_xingming FROM admin where admin_id="&rs("adminid")
response.write m_sql
Set rst = conn.Execute(m_SQL)
admin_xingming=rst1(0)
rst.Close
End if
%>
<tr><td><%=rs("num")%></td><td><%=admin_xingming%></td></tr>
<%
rs.movenext
Loop
rs.close
set rs=nothing
%>
现在这样出现在另一种错误提示：[Microsoft][ODBC SQL Server Driver]连接占线导致另一个 hstmt