插入:$sql2=" replace into collectSELECT log.* FROM log, ( SELECT
swname, username FROM log WHERE date(dtime) = CURDATE() and swname like 'error%' GROUP BY
swname, username HAVING COUNT(*) =1 ) AS logtmp WHERE log.username = logtmp.username AND log.swname = logtmp.swname and date(dtime) = CURDATE() ORDER BY dtime DESc";
mysql_query($sql2,$con); $sql1 = "REPLACE into collect SELECT log.* FROM log, ( SELECT
swname, username FROM log WHERE date(dtime) = CURDATE() GROUP BY
swname, username HAVING COUNT(*) >= 3 ) AS logtmp WHERE log.username = logtmp.username AND log.swname = logtmp.swname and date(dtime) = CURDATE() ORDER BY dtime DESc";
SELECT *, count(*) FROM collect WHERE EXISTS ( SELECT 1 FROM follow WHERE collect.username = follow.username AND collect.swname = follow.swname and date(dtime) = CURDATE() ) GROUP BY username, swname ORDER BY dtime DESC
查询未处理的: SELECT *, count(*) FROM collect WHERE not EXISTS ( SELECT 1 FROM follow WHERE collect.username = follow.username AND collect.swname = follow.swname
) GROUP BY username, swname order by dtime desc;
给上个帖子差不多,自行修改select * from a a2 inner join ( select a1.* from a a1 inner join b on a1.dtime=b.dtime where not exists(select 1 from a where a1.username=username and a1.dtime<dtime)) b on a1.username=a2.username and a1.swname=a2.swname and date(a1.dtime)=date(a2.time)
select * from collect c where not exists ( select 1 from follow where username = c.username and swname=c.swname and dtime>=c.dtime )
李的结果是什么?
*, count(*)
FROM
collect
WHERE
NOT EXISTS (
SELECT
1
FROM
follow
WHERE
collect.username = follow.username
AND collect.swname = follow.swname
)
GROUP BY
username,
swname
ORDER BY
dtime DESC;
我的查询是如上,但是只比较了username和swname,这样的话小王就会变成已处理,其实小王有三相记录都应该是未处理的
pcname varchar(20),
swname varchar(20),
dtime datetime
以供测试
replace into collectSELECT
log.*
FROM
log,
(
SELECT
swname,
username
FROM
log
WHERE
date(dtime) = CURDATE() and swname like 'error%'
GROUP BY
swname,
username
HAVING
COUNT(*) =1
) AS logtmp
WHERE
log.username = logtmp.username
AND log.swname = logtmp.swname
and date(dtime) = CURDATE()
ORDER BY
dtime DESc";
mysql_query($sql2,$con); $sql1 = "REPLACE into collect
SELECT
log.*
FROM
log,
(
SELECT
swname,
username
FROM
log
WHERE
date(dtime) = CURDATE()
GROUP BY
swname,
username
HAVING
COUNT(*) >= 3
) AS logtmp
WHERE
log.username = logtmp.username
AND log.swname = logtmp.swname
and date(dtime) = CURDATE()
ORDER BY
dtime DESc";
*, count(*)
FROM
collect
WHERE
EXISTS (
SELECT
1
FROM
follow
WHERE
collect.username = follow.username
AND collect.swname = follow.swname
and date(dtime) = CURDATE()
)
GROUP BY
username,
swname
ORDER BY
dtime DESC
SELECT
*, count(*)
FROM
collect
WHERE
not EXISTS (
SELECT
1
FROM
follow
WHERE
collect.username = follow.username
AND collect.swname = follow.swname
)
GROUP BY
username,
swname
order by dtime desc;
(
select a1.* from a a1
inner join b on a1.dtime=b.dtime
where not exists(select 1 from a where a1.username=username and a1.dtime<dtime)) b
on a1.username=a2.username and a1.swname=a2.swname and date(a1.dtime)=date(a2.time)
from collect c
where not exists (
select 1
from follow
where username = c.username
and swname=c.swname
and dtime>=c.dtime
)