将count与其他查询一起使用时出现mysql错误

1aaf6o9v  于 2021-06-24  发布在  Mysql
关注(0)|答案(1)|浏览(368)

我使用jdbc运行此查询并在mysql中成功获得结果:

SELECT COUNT(DISTINCT xx.x, xx.z) as `n_x|z`, 
       COUNT(DISTINCT xx.w, xx.y, xx.z) as `n_w|y|z` /*,...same COUNTS*/
       FROM (SELECT x0.field0 as `x`, x0.field1 as `y`, x0.field2 as `z`, 
       x0.field3 as `w` FROM `Kind` x0 ) as xx

以下查询还成功生成结果:

SELECT (1  * -1.5) as weight,
       xx.x, xx.y, xx.z, xx.w
/*The same table:*/
       FROM (SELECT x0.field0 as `x`, x0.field1 as `y`, x0.field2 as `z`, 
       x0.field3 as `w` FROM `Kind` x0 ) as xx

但是组合:

SELECT COUNT(DISTINCT xx.x, xx.z) as `n_x|z`, 
       COUNT(DISTINCT xx.w, xx.y, xx.z) as `n_w|y|z` /*,... same COUNTs*/
       ,(1  * -1.5) as weight,
       xx.x, xx.y, xx.z, xx.w
/*The same table:*/
       FROM (SELECT x0.field0 as `x`, x0.field1 as `y`, x0.field2 as `z`, 
       x0.field3 as `w` FROM `Kind` x0 ) as xx

出错:
sql语法有错误;检查与您的mysql服务器版本对应的手册,以获得在“xx.x,xx.y,xx.z,xx.w from”附近使用的正确语法(选择x0.field0作为 x ,x0.field1为 y ,x0.fie'在第1行
甚至移除 ,(1 * -1.5) as weight 没用的!我可以分别运行两个查询,但我更喜欢将它们组合起来,以便从复杂耗时的查询中获得表:

FROM (SELECT x0.field0 as `x`, x0.field1 as `y`, x0.field2 as `z`, x0.field3 as `w` FROM `Kind` x0 ) as xx"

只能生成一次。我做错什么了?

jaxagkaj

jaxagkaj1#

请注意,在第一个查询中,对行的总数进行了聚合
在第二行中,您有没有任何聚合的行
您可以从两个查询中选择不同数量的行
因此,对于非聚合函数,您应该选择或分组,例如:

SELECT COUNT(DISTINCT xx.x, xx.z) as `n_x|z`, 
       COUNT(DISTINCT xx.w, xx.y, xx.z) as `n_w|y|z`
       ,(1  * -1.5) as weight,
       xx.x, xx.y, xx.z, xx.w

       FROM (SELECT x0.field0 as `x`, x0.field1 as `y`, x0.field2 as `z`, 
       x0.field3 as `w` FROM `Kind` x0 ) as xx 
       group by  weight, xx.x, xx.y, xx.z, xx.w

或者(根据应用程序的需要)对非平坦行使用聚合函数(例如:min())

SELECT COUNT(DISTINCT xx.x, xx.z) as `n_x|z`, 
       COUNT(DISTINCT xx.w, xx.y, xx.z) as `n_w|y|z`,
       min((1  * -1.5)) as weight,
       min(xx.x), 
       min(xx.y), 
       min(xx.z), 
       min(xx.w)

       FROM (SELECT x0.field0 as `x`, x0.field1 as `y`, x0.field2 as `z`, 
       x0.field3 as `w` FROM `Kind` x0 ) as xx 
       group by  weight, xx.x, xx.y, xx.z, xx.w

或者像你上次的评论一样,如果你想对xx表中的所有行重复计数,你可以使用交叉连接

SELECT c_xx.`n_x|z`, c_xx.`n_w|y|z`, 
         (1  * -1.5) as weight,
         xx1.x, xx1.y, xx1.z, xx1.w
  FROM (SELECT x0.field0 as `x`, x0.field1 as `y`, x0.field2 as `z`, 
         x0.field3 as `w` FROM `Kind` x0 ) xx1 

  CROSS JOIN (

      SELECT COUNT(DISTINCT xx.x, xx.z) as `n_x|z`, 
             COUNT(DISTINCT xx.w, xx.y, xx.z) as `n_w|y|z` //,... same COUNTS
             FROM (SELECT x0.field0 as `x`, x0.field1 as `y`, x0.field2 as `z`, 
             x0.field3 as `w` FROM `Kind` x0 ) xx
  ) c_xx

相关问题