mysql - ON 语句中的内部 Join

Question

我正在做一项作业:

Compare the average daily revenue (as I define it in Teradata Week 5 Exercise Guide) of the store with the highest msa_income and the store with the lowest median msa_income (according to the msa_income field). In what city and state were these two stores, and which store had a higher average daily revenue?

...并且答案键在 ON 语句中有一个内部连接，这让我很困惑。我只学会了加入FROM。于是我在网上搜索了关于ON语句中的inner join，没有太多相关内容。

我是一个新学习者，所以这个问题可能非常基础。预先感谢您的耐心等待!

我遇到问题的行是:ON m.store=t.store JOIN strinfo s

SELECT SUM(store_rev. tot_sales)
    SUM(store_rev.numdays) AS daily_average, 
    store_rev.msa_income as med_income,     
    store_rev.city, store_rev.state
    FROM (SELECT COUNT (DISTINCT t.saledate) as numdays, 
    EXTRACT(YEAR from t.saledate) as s_year, 
    EXTRACT(MONTH from t.saledate) as s_month, t.store, 
    sum(t.amt) as tot_sales, 
    CASE 
       when extract(year from t.saledate) = 2005 AND extract(month from t.saledate) = 8 then 'exclude'
END as exclude_flag, m.msa_income, s.city, s.state
FROM trnsact t JOIN store_msa m
ON m.store=t.store JOIN strinfo s
ON t.store=s.store
WHERE t.stype = 'P' AND exclude_flag IS NULL
GROUP BY s_year, s_month, t.store, m.msa_income, s.city, s.state
HAVING numdays >= 20) as store_rev
WHERE store_rev.msa_income IN ((SELECT MAX(msa_income) 
FROM store_msa),(SELECT MIN(msa_income) FROM store_msa))
GROUP BY med_income, store_rev.city, store_rev.state;

Answer 1

如果这样写的话可能会更容易理解:

FROM trnsact t JOIN
     store_msa m
     ON m.store = t.store JOIN
     strinfo s
     ON t.store = s.store

JOIN 不在 ON 语句中。 FROM 子句由链接在一起的多个联接组成。

您可以将 JOIN 关键字视为结束 ON 子句并开始新的连接条件。