sqlite 使用多个连接缩短查询

gywdnpxw 于 2023-01-31 发布在 SQLite

关注(0)|答案(1)|浏览(182)

有什么方法可以缩短这个查询吗？这个查询返回了我想要的结果，但是我觉得这个太长了。有什么技巧可以让我使用多重联接进行有效的查询吗？

SELECT home.team_id, home.name, ((home.hwins+away.awins)*1.0/(home.hwins+away.awins+draw.nowin)) as winratio
FROM(
    SELECT m.home_team_api_id AS team_id, t.team_long_name AS name, COUNT(m.id) as hwins
    FROM match m
    LEFT JOIN team t
        ON m.home_team_api_id=t.team_api_id
    WHERE m.home_team_goal > m.away_team_goal
    GROUP BY m.home_team_api_id) AS home
LEFT JOIN(
    SELECT m.away_team_api_id AS team_id, t.team_long_name AS name, COUNT(m.id) as awins
    FROM match m
    LEFT JOIN team t
        ON m.away_team_api_id=t.team_api_id
    WHERE m.away_team_goal > m.home_team_goal
    GROUP BY m.away_team_api_id) AS away
    ON home.team_id=away.team_id
LEFT JOIN(
    SELECT m.away_team_api_id AS team_id, t.team_long_name AS name, COUNT(m.id) as nowin
    FROM match m
    LEFT JOIN team t
        ON m.away_team_api_id=t.team_api_id
    WHERE m.away_team_goal = m.home_team_goal
    GROUP BY m.away_team_api_id) AS draw
    ON home.team_id=away.team_id
GROUP BY home.team_id
ORDER BY winratio DESC
LIMIT 10;

结果是：
| 组标识|姓名|温比|
| - ------|- ------|- ------|
| 小行星8634|巴塞罗那足球俱乐部|0.8897338403041825美元|
| 小行星8633|皇家马德里|0.8871595330739299美元|
| 小行星9925|凯尔特语|0.8825910931174089美元|
| 小行星9823|拜仁慕尼黑足球俱乐部|0.8693693693694美元|
| 小行星1026|曼联|0.8687782805429864美元|
| 小行星9885|尤文图斯|0.8669724770642202美元|
| 小行星9772|本菲卡足球俱乐部|0.8644859813084113美元|
| 小行星9773|Perl图足球会|0.8632075471698113美元|
| 小行星8593| AJAX |0.861904761904762美元|
| 小行星9931|巴塞尔足球俱乐部|0.861244019138756平均值|

sqlite

来源：https://stackoverflow.com/questions/75258799/shorten-queries-with-multiple-joins

1条答案

按热度按时间

x7yiwoj41#

你可以使用条件聚集，根据它你只在有一个满足的条件时才计数。这将避免你有三个子查询。

SELECT team_id, name, ((hwins+awins)*1.0/(hwins+awins+nowin)) as winratio
FROM(SELECT m.home_team_api_id                                     AS team_id, 
            t.team_long_name                                       AS name, 
            COUNT(CASE WHEN m.home_team_goal > m.away_team_goal
                       THEN m.id END)                              AS hwins,
            COUNT(CASE WHEN m.away_team_goal > m.home_team_goal
                       THEN m.id END)                              AS awins,
            COUNT(CASE WHEN m.away_team_goal = m.home_team_goal
                       THEN m.id END)                              AS nowin
     FROM      match m
     LEFT JOIN team t
            ON m.home_team_api_id=t.team_api_id
     GROUP BY m.home_team_api_id, t.team_long_name
     ) AS all_matches
ORDER BY winratio DESC
LIMIT 10;

或者，如果您希望在不使用子查询的情况下执行此操作：

SELECT m.home_team_api_id                                     AS team_id, 
       t.team_long_name                                       AS name, 
       (COUNT(CASE WHEN m.home_team_goal <> m.away_team_goal 
                   THEN m.id END) *0.1) / COUNT(*)            AS winratio
FROM      match m
LEFT JOIN team t
       ON m.home_team_api_id=t.team_api_id
GROUP BY m.home_team_api_id, t.team_long_name

注意：聚合中缺少一些字段，并且不需要最后的外部GROUP BY。通常，只有在使用聚合函数时才需要使用此子句。

赞(0）回复(0）举报 2023-01-31

我来回答

sqlite 使用多个连接缩短查询

1条答案

相关问题

热门标签

最新问答