通过SQL实现二维表格

Reading time ~1 minute

前天下午,为了满足PM的要求给做个View,真是写SQL写到要吐了,这大概是我至今为止写过的最恶心的SQL了,没有之一,可又有什么办法了,谁叫我们只是苦力

要求本来并不复杂,简单点说就是各种要实现类似按照日期统计每种状态的订单数量,本来按照日期和订单状态做个group by就好了的,然后得到结果格式如下:

日期 状态 数量
2014-12-31 state1 a
2014-12-31 state2 b
2014-12-30 state1 c
2014-12-30 state2 d

可是这下PM不满意了,非说看着不爽,要改成下面的格式:

日期 state1 state2
2014-12-31 a b
2014-12-30 c d

听到这要求我也是跪了,这尼玛真是让我把SQL当Excel来玩啊!!!

蛋疼完还是得想想到底怎么去做,首先印象中SQL里似乎没有什么函数可以统计某一字段为特定值的记录数,然后大概想了一下,脑子里只有一个想法,首先将各个状态的订单都分别select出来,然后各自按日期group并统计数量,这样就会每个状态得到一个表,最后把这些表join起来就得到了想要的效果。显然,虽然这样很麻烦,但是至少是可行的。

既然没有什么好的办法,那就硬着头皮做呗,然而好不容易让SQL跑起来得到预想的效果后,准备存成view的时候却又出现了问题。由于这样实现的时候,在from中会有子查询,然后尝试用Navicat存这样的view的时候,会得到如下的错误:

无奈之下,只得又手动将from中的select又一个个的存成view,然后最后通过将这些view join起来得到结果,这样总算是得到了一个可以使用的view。不过很显然,这样的view不仅写起来极度麻烦,而且效率很低,虽说现在数据量小,都是秒出,但是对于我这种强迫症而言,这简直就是不能接受。于是完成之后又思前想后,总算灵机一动,想到一个不错的解决方案。

首先,我们增加一个如下辅助的表:

然后,我们可以通过订单状态和这个表的id字段做连接,此时,当我们count(one)的时候,就会得到订单状态为1的记录数,因为count是不会将NULL的行算上的。类似的,我们需要状态为几的订单,就可以在这个辅助表中加上对应的记录即可,最终得到SQL形式如下:

1
2
3
4
SELECT `date`, COUNT(`one`) AS `state1`, COUNT(`two`) AS `state2`
FROM `order` JOIN `assistant` ON `order`.`state` = `assistant`.`id`
GROUP BY `date`
ORDER BY `date` DESC

通过这样一种方式,我们不仅极大的降低了SQL语句的复杂程度,更是使得运行效率得到了提升,毕竟和一个如此小的表做连接,代价是很低的。

挂载网络文件夹后网络故障时文件操作命令卡死

挂载 NFS 或者 Samba 的时候,经常会由于网络故障导致挂载好的链接断掉。此时如果尝试进行 ls、cd、df 等各种命令,只要与此目录沾上边,就会卡住。如果使用了类似 oh-my-zsh 这种配置的,只要在网络目录中,弹出命令提示符前就会直接卡住。这个时候第一反应就是...… Continue reading

路由折腾记 第四弹

Published on September 02, 2017