我使用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"
只能生成一次。我做错什么了?
1条答案
按热度按时间jaxagkaj1#
请注意,在第一个查询中,对行的总数进行了聚合
在第二行中,您有没有任何聚合的行
您可以从两个查询中选择不同数量的行
因此,对于非聚合函数,您应该选择或分组,例如:
或者(根据应用程序的需要)对非平坦行使用聚合函数(例如:min())
或者像你上次的评论一样,如果你想对xx表中的所有行重复计数,你可以使用交叉连接