请帮忙看看这两条 MySQL 语句问题出在何处？

UPDATE user,thread SET money=0.5*money WHERE user.userid = thread.postuserid AND ((SELECT dateline FROM thread ORDER BY dateline DESC LIMIT 0,1) BETWEEN UNIX_TIMESTAMP(DATE_SUB(DATE(SYSDATE()), INTERVAL 1 MONTH)) AND UNIX_TIMESTAMP(DATE_SUB(DATE(SYSDATE()), INTERVAL 4 MONTH))) AND thread.forumid IN (56,57,60,62,67,65,70,71,73,178,75,76,77,109,189,186,81,83,88,89,74,175,181,85,74,175,79,80,165,193,172,59) AND user.membergroupids !=27 AND user.money >0;
上条语句的意义，查询在技术区发的新主题的时间，如果晚于当前时间 1 － 4 个月的，会员金币缩减 50%
这条语句能运行，但是没有效果，问题出在这里， user.userid = thread.postuserid AND ((SELECT dateline FROM thread ORDER BY dateline DESC LIMIT 0,1) 我搞不定了，水平有限，请帮忙，这个条件是取出 dataline 最大的值参与比较运算。

UPDATE user,thread SET money=0.05*money WHERE user.userid = thread.postuserid AND ((SELECT count(threadid) FROM thread ) = 0) AND thread.forumid IN (56,57,60,62,67,65,70,71,73,178,75,76,77,109,189,186,81,83,88,89,74,175,181,85,74,175,79,80,165,193,172,59) AND user.membergroupids !=27 AND user.money >0;
第二条是从未在技术区发过主题的，金币扣 95%
这条是统计在技术区的主题为 0 个的。也是没有结果。

thread

and

dateline

技术区

17 条回复 • 2016-07-28 07:20:50 +08:00

Layne

2016-07-27 00:38:48 +08:00

不知道数据量有多少，用 exists 和 not exists 会不会在效率上有巨大差异，

另外，没有环境验证，不确定能不能执行，我只是个数据渣…

update user u
set u.money = 0.5 * u.money
where u.membergroupids != 27
and u.money > 0
and exists (
select 1 from thread t where t.postuserid = u.userid
and t.dateline between date_add(now(), interval -4 month) and date_add(now(), interval -4 month)
and t.forumid in (56,57,60,62,67,65,70,71,73,178,75,76,77,109,189,186,81,83,88,89,74,175,181,85,74,175,79,80,165,193,172,59)
);

update user u
set u.money = 0.5 * u.money
where u.membergroupids != 27
and u.money > 0
and not exists (
select 1 from thread t where t.postuserid = u.userid
and t.forumid in (56,57,60,62,67,65,70,71,73,178,75,76,77,109,189,186,81,83,88,89,74,175,181,85,74,175,79,80,165,193,172,59)
);

oclock

2016-07-27 08:55:21 +08:00

查询条件这么复杂，建议把逻辑拆出来放进 with CTE 或者临时表

former

2016-07-27 09:43:26 +08:00

取出 dataline 最大的值，直接 select MAX(dataline) from table where 条件就可以

adv007

2016-07-27 09:56:48 +08:00 via iPhone

不要在 sql 中做复杂逻辑，抽到程序里面，你会发现整个世界都是清晰的

wangyu1314

2016-07-27 10:18:39 +08:00

@Layne
感谢，两条都可以执行，但是第一条没有结果。第二条有。
1 queries executed, 1 success, 0 errors, 0 warnings

查询： update user u set u.money = 0.5 * u.money where u.membergroupids != 27 and u.money > 0 and exists ( select 1 from thread t where...

共 0 行受到影响

执行耗时 : 2.945 sec
传送时间 : 0 sec
总耗时 : 2.946 sec

1 queries executed, 1 success, 0 errors, 0 warnings

update user u
set u.money = 0.5 * u.money
where u.membergroupids != 27
and u.money > 0
and not exists (
select 1 from thread t where t.postuserid = u.userid
and t.forumid in (56,57,60,62,67,65,70,71,73,178,75,76,77,109,189,186,81,83,88,89,74,175,181,85,74,175,79,80,165,193,172,59)
);

查询： update user u set u.money = 0.5 * u.money where u.membergroupids != 27 and u.money > 0 and not exists ( select 1 from thread t w...

共 23176 行受到影响

执行耗时 : 3.634 sec
传送时间 : 0 sec
总耗时 : 3.634 sec

wangyu1314

2016-07-27 10:21:28 +08:00

@oclock 谢谢你，你说的更高深，还学到那步来。
@former 这种方法我试过，但是语句没写成功。
@adv007 现在主要是苦恼不懂 PHP 呀。

Layne

2016-07-27 11:02:38 +08:00

@wangyu1314 写 update 时，可以先把查询逻辑部分写好，然后更新需要处理的字段，以我写的第一句来看，查询逻辑为：

select *
from
/*update*/ user u
/* set u.money = 0.5 * u.money */
where u.membergroupids != 27
and u.money > 0
and exists (
select 1 from thread t where t.postuserid = u.userid
and t.dateline between date_add(now(), interval -4 month) and date_add(now(), interval -4 month)
and t.forumid in (56,57,60,62,67,65,70,71,73,178,75,76,77,109,189,186,81,83,88,89,74,175,181,85,74,175,79,80,165,193,172,59)
);

调整这个查询逻辑，直到能查询出你需要更新的数据，然后再 update 对应字段即可。

写 update 语句时我一般习惯不用表关联，如果查询逻辑本身就特别复杂，就用存储过程来处理，或者用程序来处理了。

wangyu1314

2016-07-27 12:11:40 +08:00

@Layne 改出来了，但是这个思路是错的，这个语句会导致用户如果每个月都发了新主题的，会被重复扣钱。我们有 3 条规则， 1 － 3 个月扣多少， 3 － 6 扣多少， 6 个月以上扣多少。

wangyu1314

2016-07-27 12:17:58 +08:00

这个语句只能找在技术区的所发主题的最大 dateline ，只对这个用户操作一次。

Layne

2016-07-27 13:15:42 +08:00

@wangyu1314

试试这样：

select *
from
/*update*/ user u
/* set u.money = 0.5 * u.money */
where u.membergroupids != 27
and u.money > 0
and exists (
select max(t.dateline) from thread t where t.postuserid = u.userid
and t.forumid in (56,57,60,62,67,65,70,71,73,178,75,76,77,109,189,186,81,83,88,89,74,175,181,85,74,175,79,80,165,193,172,59)
) between date_add(now(), interval -4 month) and date_add(now(), interval -4 month)
;

wangyu1314

2016-07-27 14:19:45 +08:00

@Layne
SELECT *
FROM
/*update*/ USER u
/* set u.money = 0.5 * u.money */
WHERE u.membergroupids != 27
AND u.money > 0
AND EXISTS (
SELECT MAX(t.dateline) FROM thread t WHERE t.postuserid = u.userid
AND t.forumid IN (56,57,60,62,67,65,70,71,73,178,75,76,77,109,189,186,81,83,88,89,74,175,181,85,74,175,79,80,165,193,172,59)
) BETWEEN DATE_ADD(NOW(), INTERVAL 4 MONTH) AND DATE_ADD(NOW(), INTERVAL 1 MONTH) ;
运行了以后没结果。
这种最大化取值 MAX(dateline)我试过了，只能取出一个全局最大值，不能取出每个人的最大值，而是这种取出值不能参与运算。

Layne

2016-07-27 14:27:53 +08:00

把 exists 去掉呢？

自查询限制了 postuserid = userid ，取得的 max(dateline) 应该就是对应用户的最大值

wangyu1314

2016-07-27 14:48:18 +08:00

@Layne 去掉 exists 仍然没有效果。。。

Layne

2016-07-27 14:53:54 +08:00

@wangyu1314 看一下 between 语句后面的月份加减对不对，我用的是 date_add 函数，然后前溯的话应该是负数参数，或者用你习惯的日期函数来写

wangyu1314

2016-07-27 14:56:46 +08:00

我用另外一种方法取出了 thread 表每个用户的最大 dateline.

SELECT postuserid,FROM_UNIXTIME(dateline) FROM thread
WHERE
forumid IN (56,57,60,62,67,65,70,71,73,178,75,76,77,109,189,186,81,83,88,89,74,175,181,85,74,175,79,80,165,193,172,59)
GROUP BY postuserid ORDER BY dateline DESC

adv007

2016-07-27 17:37:40 +08:00 via iPhone

@wangyu1314 学学呗，你想想你这条语句遇到大并发的执行效率

msg7086

2016-07-28 07:20:50 +08:00

用关系数据库的 SQL 来跑业务逻辑只会更慢吧。
要不写存储过程？