我有那些桌子
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/