求教一个sql查询语句，进来看看谢谢

[hexiaomin1010]

一个数据库的表结构如下
id pid date
1 100 2011-06-30
2 100 2011-06-30
3 101 2011-06-30
4 100 2011-07-01
5 100 2011-07-02
...

我需要的结果是，一周（自然周）或者一个月内登陆日大于等于3天以上的pid的数量
一日内多次登陆算一日，需要在一个自然周不同日登陆的次数大于等于3。月同理
请问这样该如何查询，坐等高手指点
在线等谢谢- -

[PIGer920]

[Quote=引用 4 楼 hexiaomin1010 的回复:]

而且日期是外部可供选择的，就是客户选择了日期后，我需要转化成自然周- -！
[/Quote]
早说
你测试下

select count(pid) as count,date 
from 
(select DISTINCT pid ,date from table where date<='2011-06-20' and date>='2011-06-26'  ) tmp  
group by date 
having count >= 3

[PIGer920]

错了
应该是这个

select count(pid) as counter ,pid
from 
(select DISTINCT pid ,date from table where date<='2011-06-26' and date>='2011-06-20'  ) tmp  
group by pid 
having counter >= 3

[xuzuning]

select pid, count(*) as cnt, DATE_FORMAT(dates, '%X%V') as dt from (SELECT * FROM `tbl` group by pid, dates) t group by dt, pid

[hexiaomin1010]

刚去吃饭了，不好意思- -！6F的，外部条件可能选的不是刚好一个周啊，应该先DATE_FORMAT？不过这样查应该性能不会很差吧。那个表的数据估计上千万条- -、！！
7F的我没有看出来怎么大于3了啊。。。

[xuzuning]

我没有看出来怎么大于3了啊
是的，为清楚起见，没有加 HAVING 子句

子查询 SELECT * FROM `tbl` group by pid, dates 是为了满足“一日内多次登陆算一日”
也可以用 select DISTINCT pid, dates form tbl

如果有初始的过滤条件，则过滤应在子查询中进行

[hexiaomin1010]

如果我写个接口，用计划每天来执行他，就是把这个表去重插入到另外个表中，是否更好点？
因为表中数据太大了- -，还有些其他的数据处理，要限制循环在10万次以内（基本预计在5个小时内要把数据算完。还有很多操作数据库的动作，查询插入什么的）
如果查询去重的表，是否用

select count(pid) as ct,DATE_FORMAT(date, '%x%v') as dt from table where date>="starttime" and date<="endtime" group by dt,pid having ct>=3

这样就可以达到我想要的结果了？

[hexiaomin1010]

请问，是用子循环查询效率高些，还是先查询一个结果，然后用mysql_fetch_array循环控制拼成新的sql查询想要的结果，哪种效率高？？？
我初步估计了下，我的循环次数估计超了一倍了。。。。。。。。。。

[xuzuning]

那肯定是子查询了，因为子查询是在 sql 中完成的，只涉及索引
你还可以建一个带唯一索引的视图来替代子查询

[hexiaomin1010]

            $count_month=0;
            $sql_login_month_last='select distinct pid from login_log where date<=last_day(date_add("'.$thisdate.'",interval -2 month)) and date>=DATE_FORMAT(concat(extract(year_month from date_add("'.$thisdate.'",interval -2 MONTH)),"'."01".'"),"'."%Y-%m-%d".'") and serverid='.$server;
            $res_login_month_last=$dbhelper->Excute($sql_login_month_last);
            while($row_login_month_last=mysql_fetch_array($res_login_month_last))
            {
                $sql_login_month='select count(distinct pid) as count from login_log where date<=last_day(date_add("'.$thisdate.'",interval -1 month)) and date>=DATE_FORMAT(concat(extract(year_month from date_add("'.$thisdate.'",interval -1 MONTH)),"'."01".'"),"'."%Y-%m-%d".'") and pid="'.$row_login_month_last['pid'].'"';
                $res_login_month=$dbhelper->Excute($sql_login_month);
                $rowcount_login_month=mysql_fetch_array($res_login_month);
                if($rowcount_login_month['count']>0)
                {
                    $count_month+=1;
                }
            }

这是我查询一个数据的代码，需求是，查询上上个月登陆过的人，并在上个月也登陆过的人数
这样写虽然达到了目的，但是那个表太大了，一个月累积起来，估计这个循环就要跑10万次。
请问这种问题改怎么修改最优？谢谢了。因为我这样的代码写的实在是太多了。。汗！

[hexiaomin1010]

我觉得这代码写的也很丑，水平有限，求指导。。

[hexiaomin1010]

up一下~

[xuzuning]

得到每一天访问的人
select distinct pid, date from login_log

得到每一月访问的人
select distinct pid, date_form(date, "%Y-%m-1") as date from login_log

得到本月与上月都访问的人
select a.* from
(select distinct pid, date_form(date, "%Y-%m-1") as date from login_log) a,
(select distinct pid, date_form(date, "%Y-%m-1") as date from login_log) b
where a.pid=b.pid and a.date=date_add(date,interval +1 month)

[hexiaomin1010]

额。。请解释一下，为什么a表和b表数据相同，最后a.date=date_add(date,interval +1 month)
后面个date应该是b表的吧？？？

[hexiaomin1010]

最后个date应该是变量- -！但是我要上上个月登陆过同时在上个月也登陆过的怎么写？不是很懂。。

[hexiaomin1010]

把每个月的pid都找出来，再与上个月登陆过的pid对等？

[hexiaomin1010]

up.没人理- -！

[PIGer920]

一条sql就把这些分类统计给算出来估计很难 恐怕也很低效

[hexiaomin1010]

那应该怎样查询呢？

[hexiaomin1010]

而且日期是外部可供选择的，就是客户选择了日期后，我需要转化成自然周- -！