克服“至少一次” LEFT JOIN 中的条件

发布于 2024-10-02 11:17:07 字数 2620 浏览 6 评论 0原文

我有以下两个表 quote(date,market,ticker,close)scenario(date1,date2,period,market,ticker,ratio)。我编写了以下 LEFT JOIN 查询,它可以执行我想要的操作,除了一个问题:

SELECT  S.date1 AS date1, 
        S.date2 AS date2,
        S.period AS period,
        Q.market AS market,
        Q.ticker AS ticker,
        Q.close * EXP(S.ratio) AS scenario

FROM portfolio.quote Q  LEFT JOIN  portfolio.scenario S 

      ON Q.market = S.market AND
         Q.ticker = S.ticker AND
         S.date1 >= '2010-06-01' AND
         S.date2 <= '2010-07-01' AND
         S.period = 'WEEK'

WHERE Q.date = '2010-07-01' 
ORDER BY S.date1, S.date2, S.period, Q.market, Q.ticker

上一个查询返回结果,并且每当报价表中的 (market,ticker) 与场景表中的等效 (market,ticker) 不匹配时为属于场景表的列添加了一行 NULL 值,如下所示:

date1, date2, period, market , ticker, scenario
NULL , NULL , NULL  , 'sp500', 'QEP' , NULL

对于“2010-06-01”和“2010-07-01”之间的期间(期间为“WEEK”),可能有许多不匹配的行('sp500','QEP'),但只有一行用属于场景表的列中的 NULL 值表示这一事实。更不用说,在这些结果中,(market,ticker) 有许多不匹配的行,但至少存在一个匹配项,因此查询不会为这些行返回 NULL 值。

我想要做的是,例如,如果 Q.date = '2010-07-01' 返回报价表的 500 行,那么将有 500 * (DISTINCT date1,date,period)。仅当以下条件不匹配时,场景列中才包含 NULL 值:

Q.market = S.market AND
Q.ticker = S.ticker

因此,例如 NULL 结果看起来像这样:

date1        , date2        , period  , market , ticker, scenario
'2010-06-01' , '2010-06-08' , 'WEEK'  , 'sp500', 'QEP' , NULL
'2010-06-08' , '2010-06-15' , 'WEEK'  , 'sp500', 'QEP' , NULL
'2010-06-15' , '2010-06-23' , 'WEEK'  , 'sp500', 'A'   , NULL

另一个例子,如果 (market,ticker,close) 行匹配 <报价表中的 code>Q.date = '2010-07-01' 为:

('sp','A',1)
('sp','AA',2)

与条件匹配的 (date1,date2,period,market,ticker,ratio)

     S.date1 >= '2010-06-01' AND
     S.date2 <= '2010-07-01' AND
     S.period = 'WEEK'

行为:

('2010-06-01','2010-06-08','WEEK','sp','A',0.43)
('2010-06-01','2010-06-08','WEEK','sp','AA',0.21)

('2010-06-08','2010-06-20','WEEK','sp','A',0.49)

('2010-06-20','2010-06-27','WEEK','sp','A',0.46)
('2010-06-20','2010-06-27','WEEK','sp','B',0.23)

那么我的查询结果应该返回

('2010-06-01','2010-06-08','WEEK','sp','A',1*EXP(0.43))
('2010-06-01','2010-06-08','WEEK','sp','AA',2*EXP(0.21))

('2010-06-08','2010-06-20','WEEK','sp','A',1*EXP(0.49))
('2010-06-08','2010-06-20','WEEK','sp','AA',NULL)

('2010-06-20','2010-06-27','WEEK','sp','A',1*EXP(0.46))
('2010-06-20','2010-06-27','WEEK','sp','AA',NULL)

MySQL 中可能出现这种情况吗?,非常感谢任何帮助:)

I have the following two tables quote(date,market,ticker,close) and scenario(date1,date2,period,market,ticker,ratio). I have written the following LEFT JOIN query which does what i want except for one problem :

SELECT  S.date1 AS date1, 
        S.date2 AS date2,
        S.period AS period,
        Q.market AS market,
        Q.ticker AS ticker,
        Q.close * EXP(S.ratio) AS scenario

FROM portfolio.quote Q  LEFT JOIN  portfolio.scenario S 

      ON Q.market = S.market AND
         Q.ticker = S.ticker AND
         S.date1 >= '2010-06-01' AND
         S.date2 <= '2010-07-01' AND
         S.period = 'WEEK'

WHERE Q.date = '2010-07-01' 
ORDER BY S.date1, S.date2, S.period, Q.market, Q.ticker

The previous query returns the results and whenever (market,ticker) in the quote table is not matched by an equivalent (market,ticker) in the scenario table a row is added with NULL values for the columns belonging to the scenario table as such :

date1, date2, period, market , ticker, scenario
NULL , NULL , NULL  , 'sp500', 'QEP' , NULL

For the period between '2010-06-01' and '2010-07-01' with period 'WEEK' there may have been many unmatched rows for ('sp500','QEP') but only one row represent this fact with NULL values in the columns belonging to scenario table. Let alone, Within these results there are many unmatched rows for (market,ticker) but there exists at least one match so the query doesn't return NULL values for these rows.

What i want to do is for example if Q.date = '2010-07-01' returns 500 rows for quote table, then there would be 500 * (DISTINCT date1,date,period). with NULL values in the scenario column only when the following condition is unmatched:

Q.market = S.market AND
Q.ticker = S.ticker

So for example the NULL results would look something like that :

date1        , date2        , period  , market , ticker, scenario
'2010-06-01' , '2010-06-08' , 'WEEK'  , 'sp500', 'QEP' , NULL
'2010-06-08' , '2010-06-15' , 'WEEK'  , 'sp500', 'QEP' , NULL
'2010-06-15' , '2010-06-23' , 'WEEK'  , 'sp500', 'A'   , NULL

Another example if for example (market,ticker,close) rows that matched Q.date = '2010-07-01' from quote table are:

('sp','A',1)
('sp','AA',2)

And the (date1,date2,period,market,ticker,ratio) rows that matched the conditions

     S.date1 >= '2010-06-01' AND
     S.date2 <= '2010-07-01' AND
     S.period = 'WEEK'

are:

('2010-06-01','2010-06-08','WEEK','sp','A',0.43)
('2010-06-01','2010-06-08','WEEK','sp','AA',0.21)

('2010-06-08','2010-06-20','WEEK','sp','A',0.49)

('2010-06-20','2010-06-27','WEEK','sp','A',0.46)
('2010-06-20','2010-06-27','WEEK','sp','B',0.23)

Then the result of my query should return

('2010-06-01','2010-06-08','WEEK','sp','A',1*EXP(0.43))
('2010-06-01','2010-06-08','WEEK','sp','AA',2*EXP(0.21))

('2010-06-08','2010-06-20','WEEK','sp','A',1*EXP(0.49))
('2010-06-08','2010-06-20','WEEK','sp','AA',NULL)

('2010-06-20','2010-06-27','WEEK','sp','A',1*EXP(0.46))
('2010-06-20','2010-06-27','WEEK','sp','AA',NULL)

Is this sort of thing possible in MySQL ?, really appreciate any help :)

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

明媚如初 2024-10-09 11:17:07

由于您使用的是 LEFT 连接,因此即使 ON 条件与其他表(场景)不完全匹配,也会显示 FROM 表的(引用)行。其他表中的列只需替换为 NULL。

但是,您一直想要做的是显示方案表而不是报价表中的所有行。因此,我建议您使用 RIGHT join 或将查询部分替换为
FROM Portfolio.scenario S LEFT JOIN Portfolio.quote Q

希望能解决您的问题。

Since you are using LEFT join, the FROM table's (quote) rows are displayed even when there is no exact match with other table (scenario) for ON conditions. The columns in other table are simply replaced with NULL.

However, what you have been wanting to do is display the all rows from scenario table rather than from quote table. So I would suggest you to either use RIGHT join or replace the portion of the query as
FROM portfolio.scenario S LEFT JOIN portfolio.quote Q

Hope that solves your problem.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文