我有那些桌子

     table1
|  id  |  name  |
|  1   |  axe   |
|  2   |  bow   |
|  3   |  car   |
|  4   |  dart  |


        table2                                        table3
|  t1_id  |  number  |                        |  t1_id  |  letter  |
|  1      |  5       |                        |  1      |  a       |
|  1      |  6       |                        |  1      |  b       |
|  1      |  2       |                        |  1      |  c       |
|  2      |  2       |                        |  2      |  a       |
|  2      |  2       |                        |  2      |  c       |
|  2      |  3       |                        |  2      |  r       |
|  3      |  8       |                        |  3      |  y       |
|  3      |  3       |                        |  3      |  i       |
|  3      |  1       |                        |  3      |  a       |
|  4      |  8       |                        |  4      |  a       |
|  4      |  9       |                        |  4      |  b       |
|  4      |  10      |                        |  4      |  c       |

表1(id)与表2(t1_id)、表3(t1_id)链接
我运行它,让他们按最高字母数匹配,然后按最高平均数匹配,以获得正确的结果http://www.sqlfiddle.com/#!9/69086b/8/0
SELECT
  t1.id,
  t1.name

FROM
  table1 t1

INNER JOIN
  table2 t2
    ON t2.t1_id = t1.id

LEFT JOIN
  table3 t3
    ON t3.t1_id = t1.id
      AND t3.letter IN ('a', 'b', 'c')

GROUP BY
  t1.id

ORDER BY
  COUNT(t3.letter) DESC,
  AVG(t2.number) DESC

|  id  |  name  |
|  4   |  dart  |
|  1   |  axe   |
|  2   |  bow   |
|  3   |  car   |

一切正常
但是当我想检查查询是否有任何问题时,我决定检查字母计数和平均数,所以我使用了这个查询
SELECT
  t1.id,
  t1.name,
  COUNT(t3.letter) AS letter_count,
  AVG(t2.number) AS avg_number

FROM
  table1 t1

INNER JOIN
  table2 t2
    ON t2.t1_id = t1.id

LEFT JOIN
  table3 t3
    ON t3.t1_id = t1.id
      AND t3.letter IN ('a', 'b', 'c')

GROUP BY
  t1.id

ORDER BY
  letter_count DESC,
  avg_number DESC

我所期望的结果是
|  id  |  name  |  letter_count  |  avg_number   |
|  4   |  dart  |  3             |  9            |
|  1   |  axe   |  3             |  4.3333333333 |
|  2   |  bow   |  2             |  2.3333333333 |
|  3   |  car   |  1             |  4            |

但我得到的结果是http://www.sqlfiddle.com/#!9/69086b/3/0
|  id  |  name  |  letter_count  |  avg_number   |
|  4   |  dart  |  9             |  9            |
|  1   |  axe   |  9             |  4.3333333333 |
|  2   |  bow   |  6             |  2.3333333333 |
|  3   |  car   |  3             |  4            |

令我惊讶的是,可以通过派生查询解决的字母计数成倍的行,但我不想选择字母计数或数字平均值,我只想按它们排序
将查询保持为ORDER BY的方式不会影响查询性能,还是应该仍然使用派生查询,即使不需要选择数据值,因为顺序仍然正确,还是派生查询在大型表中更快?

最佳答案

你真的问了两个问题:
ORDER BY子句是否影响查询性能
为什么我的信不按预期计算
不管怎样,要计算ORDER BY子句,都需要计算表达式,以便确定顺序。在第一个示例中,您需要指定表达式,因为这些列不包含在SELECT语句中。
但是,在第二个查询中,您选择了要按其排序的列,并且由于在处理完整个查询之后计算ORDER BY,所以您可以只使用ORDER BY子句中的列别名,而不必再次执行该函数。
一些RDBMS查询优化器将在ORDER BY语句中转换表达式,如果您对您的SELECT子句中存在的表达式排序,则为您使用列别名。
您这样做是对的,但是,由于响应中存在重复项,您的字母计数表达式不是当前表达式。
您只需将COUNT表达式更改为使用distinct子句仅计算唯一值。

COUNT(DICTINCT t3.letter)

这使您的原始查询现在看起来像这样:
SELECT
  t1.id,
  t1.name

FROM
  table1 t1

INNER JOIN
  table2 t2
    ON t2.t1_id = t1.id

LEFT JOIN
  table3 t3
    ON t3.t1_id = t1.id
      AND t3.letter IN ('a', 'b', 'c')

GROUP BY
  t1.id

ORDER BY
  COUNT(DICTINCT t3.letter) DESC,
  AVG(t2.number) DESC

关于mysql - 三个表的关系顺序正确,但值是错误的,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/57066277/

10-10 14:11
查看更多