一个 SQL 语句写了一天

原创作品,转载请注明出处。

今天写程序遇到一个非常棘手的问题,花了一天的时间终于搞定了。最后用了一个很长的 SQL 语句解决了这个问题。这个问题比较有代表性,后面的程序里还有好多相似的问题,在这里把它记下来,后面再遇到这种问题就容易解决了。

问题乍听起来不是很变态,就是合并一个 CMS 系统中的两个分类,假设要合并的两个分类的编号分别是 from_cat_id 和 to_cat_id,from_cat_id 将并入 to_cat_id,合并以后,from_cat_id 将不再存在。但是这个系统每个分类对每个用户有按天进行的日志统计。这些统计的项目有:用户编号(user_id),分类编号(cat_id),访问日期 (visit_date),当天最后访问时间(last_time),当天最后访问的 IP(last_ip),当天最初访问时间(first_time),当天最初访问的 IP(first_ip),当天点击数(hits),当天发布主题数(posts),当天评论回复数(comments),当天最后发布的主题编号 (last_post),当天最后回复的评论编号(last_comment)。因为要合并分类,因此这些统计也要合并。

但是问题来了,我们不能保证某个用户在某天访问过 to_cat_id 分类,该用户就一定要在当天访问 from_cat_id 分类。反之亦然。也就是说现在有四种情况:

  1. 某个用户在某天既访问过 to_cat_id 分类,又访问过 from_cat_id 分类;
  2. 某个用户在某天仅访问过 to_cat_id 分类;
  3. 某个用户在某天仅访问过 from_cat_id 分类;
  4. 某个用户在某天既没有访问过 to_cat_id 分类,也没有访问过 from_cat_id 分类;

对于第一种情况,我们需要将相对应的每两条记录找出来,然后合并为对 to_cat_id 分类的统计记录。

对于第二种情况,只存在 to_cat_id 分类的记录,我们不需要进行合并操作。

对于第三种情况,我们需要把 from_cat_id 改为 to_cat_id。

对于第四种情况,不存在记录,也不用管它。

好了,现在四种情况中,第二种和第四种情况是不需要处理的,因此我们只要处理第一种和第三种情况即可。其中最复杂的是第一种情况。但是,如果不先处 理第一种情况,而先处理第三种情况,则很难找出第三种情况的记录来,反而使问题更复杂。我一开始就是因为对这两种情况的处理顺序弄反了,因此浪费了一个早 上加半个下午。

下面来看如何处理第一种情况。

首先如何在同一个表里同时找出对应的成对记录并进行修改呢?对,就用昨天才发现的好东西——表的别名,给同一个表起两个不同的别名,就可以当作两个表来进行连接查询了。假设这两个表的别名分别是 c1 和 c2,那么查询条件就是:

where `c1`.`cat_id` = '$to_cat_id'
 
and `c1`.`user_id` = `c2`.`user_id`
 
and `c1`.`visit_date` = `c2`.`visit_date`
 
and `c2`.`cat_id` = '$from_cat_id'

解决了这个问题,下面的问题就稍微简单一点了,首先来看需要修改那些字段,用户编号(user_id),分类编号(cat_id),访问日期 (visit_date)这三个字段修改时的参考字段,这三个字段不需要修改,其他的字段都是需要修改的。那我们来看看其他字段如何修改。

先看当天最后访问时间(last_time)和当天最后访问的 IP(last_ip)这两个字段,这两个字段在修改时,要参考 last_time 较大的值来修改。因此修改语句为:

`c1`.`last_time` = if(unix_timestamp(`c1`.`last_time`) > unix_timestamp(`c2`.`last_time`), `c1`.`last_time`, `c2`.`last_time`),
`c1`.`last_ip` = if(unix_timestamp(`c1`.`last_time`) > unix_timestamp(`c2`.`last_time`), `c1`.`last_ip`, `c2`.`last_ip`),

而当天最初访问时间(first_time),当天最初访问的 IP(first_ip)这两个字段正好相反,要参考 first_time 较小的值来修改:

`c1`.`first_time` = if(unix_timestamp(`c1`.`first_time`) < unix_timestamp(`c2`.`first_time`), `c1`.`first_time`, `c2`.`first_time`),
`c1`.`first_ip` = if(unix_timestamp(`c1`.`first_time`) < unix_timestamp(`c2`.`first_time`), `c1`.`first_ip`, `c2`.`first_ip`),

当天点击数(hits),当天发布主题数(posts)和当天评论回复数(comments)这三个字段比较容易,只需要简单的相加即可:

`c1`.`hits` = `c1`.`hits` + `c2`.`hits`,
`c1`.`posts` = `c1`.`posts` + `c2`.`posts`,
`c1`.`comments` = `c1`.`comments` + `c2`.`comments`,

当天最后发布的主题编号(last_post)和当天最后回复的评论编号(last_comment)这两个字段的合并最复杂,首先如果当天该用户 没有在这个分类中发布主题,则 last_post 为空(null)。同样,如果当天该用户没有在这个分类中进行回复评论,则 last_comment 为空。如果有发布主题和评论,则其值为主题或评论的编号。我们如何来比较那个是较晚的主题或评论呢?当然可以根据发布或回复的时间来判断,但是这里记录的 不是时间,因此要判断时间,还需要连接主题和评论表,这样效率就会很低了,也会使语句更复杂,因此我没用这种方法。我的方法比较简单,较晚发布的主题或回 复的评论的编号应该是比早发布的主题或回复的评论的编号大的,通过这一点我们就很容易判断那个是最后的主题或评论了。不过这里要作特殊处理的是空 (null)值,因为它不能直接跟编号(id)进行比较。下面是这两个字段的修改语句实现:

`c1`.`last_post` = if(isnull(`c1`.`last_post`), `c2`.`last_post`, if(isnull(`c2`.`last_post`), `c1`.`last_post`, if(`c1`.`last_post` > `c2`.`last_post`, `c1`.`last_post`, `c2`.`last_post`))),
`c1`.`last_comment` = if(isnull(`c1`.`last_comment`), `c2`.`last_comment`, if(isnull(`c2`.`last_comment`), `c1`.`last_comment`, if(`c1`.`last_comment` > `c2`.`last_comment`, `c1`.`last_comment`, `c2`.`last_comment`)))

好了,修改完这些对应的记录之后,就是把这些记录中的 from_cat_id 所对应的那一部分删除掉了。只有删除掉以后,才能处理第三种情况的记录。

删除这些记录比较简单,条件跟修改这些记录的条件差不多,不过在 delete 语句中,from 后面的表是不能用别名的,但是 using 后面的表还是可以用别名的,因此删除这些记录的语句如下:

delete from `categorylogs` using `categorylogs`, `categorylogs` as `c1` where `categorylogs`.`cat_id` = '$from_cat_id' and `categorylogs`.`user_id` = `c1`.`user_id` and `categorylogs`.`visit_date` = `c1`.`visit_date` and `c1`.`cat_id` = '$to_cat_id'

OK,接下来就可以很简单的修改第三种情况的记录了。这三条记录合起来如下:

update `categorylogs` as `c1`, `categorylogs` as `c2` set `c1`.`last_time` = if(unix_timestamp(`c1`.`last_time`) > unix_timestamp(`c2`.`last_time`), `c1`.`last_time`, `c2`.`last_time`), `c1`.`last_ip` = if(unix_timestamp(`c1`.`last_time`) > unix_timestamp(`c2`.`last_time`), `c1`.`last_ip`, `c2`.`last_ip`), `c1`.`first_time` = if(unix_timestamp(`c1`.`first_time`) < unix_timestamp(`c2`.`first_time`), `c1`.`first_time`, `c2`.`first_time`), `c1`.`first_ip` = if(unix_timestamp(`c1`.`first_time`) < unix_timestamp(`c2`.`first_time`), `c1`.`first_ip`, `c2`.`first_ip`), `c1`.`hits` = `c1`.`hits` + `c2`.`hits`, `c1`.`posts` = `c1`.`posts` + `c2`.`posts`, `c1`.`comments` = `c1`.`comments` + `c2`.`comments`, `c1`.`last_post` = if(isnull(`c1`.`last_post`), `c2`.`last_post`, if(isnull(`c2`.`last_post`), `c1`.`last_post`, if(`c1`.`last_post` > `c2`.`last_post`, `c1`.`last_post`, `c2`.`last_post`))), `c1`.`last_comment` = if(isnull(`c1`.`last_comment`), `c2`.`last_comment`, if(isnull(`c2`.`last_comment`), `c1`.`last_comment`, if(`c1`.`last_comment` > `c2`.`last_comment`, `c1`.`last_comment`, `c2`.`last_comment`))) where `c1`.`cat_id` = '$to_cat_id' and `c1`.`user_id` = `c2`.`user_id` and `c1`.`visit_date` = `c2`.`visit_date` and `c2`.`cat_id` = '$from_cat_id';
 
delete from `categorylogs` using `categorylogs`, `categorylogs` as `c1` where `categorylogs`.`cat_id` = '$from_cat_id' and `categorylogs`.`user_id` = `c1`.`user_id` and `categorylogs`.`visit_date` = `c1`.`visit_date` and `c1`.`cat_id` = '$to_cat_id';
 
update `categorylogs` set `cat_id` = '$to_cat_id' where `cat_id` = '$from_cat_id';

怎么样,够壮观吧!如果不写这篇注解,下次再看这段程序准晕了!

原创作品,转载请注明出处。

标签: PHP, Database

« 上一篇 | 下一篇 »

只显示10条记录相关文章

发表评论

评论 (必须):