mysql - 使用 ABS() 并应用带有 "AS"的标签

Question

我当前的sql语句是

"SELECT ID,ABS(tvshows-$tvshows) + ABS(actionandadventure-$actionandadventure) + ABS(anime-$anime) + ABS(childrenandfamily-$childrenandfamily) + ABS(classics-$classics) + ABS(comidies-$comidies) + ABS(cultmovies-$cultmovies) + ABS(documentaries-$documentaries) + ABS(dramas-$dramas) + ABS(faith-$faith) + ABS(foreign1-$foreign1) + ABS(gay-$gay) + ABS(horror-$horror) + ABS(independent-$independent) + ABS(music-$music) + ABS(musicals-$musicals) + ABS(independent-$independent) + ABS(music-$music) + ABS(musicals-$musicals) + ABS(romance-$romance) + ABS(scifi-$scifi) + ABS(sports-$sports) + ABS(thrillers-$thrillers) as total FROM accounts WHERE ID != '$ID' ORDER BY total ASC LIMIT 20

我想做的是为每个 ABS 都有一个单独的标签，例如

"SELECT ID,ABS(tvshows-$tvshows) AS totaltvshows + ABS(actionandadventure-$actionandadventure) AS totalactionandadventure + etc...

这可能吗？

Answer 1

不可能。您不能为表达式的子组件添加别名。例如你正在尝试做相当于:

选择 1 作为一 + 2 作为二

并以某种方式期望 mysql 将其转换为两个单独的字段/列。不，它不会那样做。你正在做加法。您将返回ONE列，其中包含添加结果，并且您可以为该结果指定别名。

SELECT (1 + 2) AS result

可以工作

如果您想要各个组件，请将它们写出来:

SELECT ABS(a+b) AS part1, ABS(c+d) AS part2, ABS(a+b) + ABS(c+d) AS result