我在哪里放置WHERE语句? [英] Where do I put a WHERE statement?

问题描述

我有以下两个数据表:

+----------+------+--------+--+---------------+-------+------------+--+--------+--------+-----------------------------+
| stat_atp |      |        |  | tblTourns_atp |       |            |  | Output |        |                             |
+----------+------+--------+--+---------------+-------+------------+--+--------+--------+-----------------------------+
| ID_T     | FS_1 | FSOF_1 |  | ID_T          | ID_Ti | DATE       |  | ID_T   | OUTPUT | Rationale                   |
| 1        | 20   | 40     |  | 1             | 1     | 01/01/2019 |  | 1      | 50%    | ID_T "1"                    |
| 2        | 30   | 100    |  | 2             | 1     | 05/01/2019 |  | 2      | 31%    | ID_T "1" & "2" & "3"        |
| 3        | 40   | 150    |  | 3             | 1     | 03/01/2019 |  | 3      | 32%    | ID_T "1" & "3"              |
| 4        | 30   | 100    |  | 4             | 2     | 04/01/2019 |  | 4      | 30%    | ID_T "4"                    |
| 5        | 30   | 100    |  | 5             | 2     | 05/01/2019 |  | 5      | 30%    | ID_T "4" & "5"              |
| 6        | 40   | 150    |  | 6             | 2     | 06/01/2019 |  | 6      | 29%    | ID_T "4" & "5" & "6"        |
| 7        | 20   | 40     |  | 7             | 3     | 01/01/2019 |  | 7      | 50%    | ID_T "7"                    |
| 8        | 30   | 100    |  | 8             | 3     | 08/01/2019 |  | 8      | 35%    | ID_T "7" & "8" & "9" & "10" |
| 9        | 40   | 150    |  | 9             | 3     | 02/01/2019 |  | 9      | 32%    | ID_T "7" & "9"              |
| 10       | 20   | 40     |  | 10            | 3     | 06/01/2019 |  | 10     | 39%    | ID_T "7" & "9" & "10"       |
+----------+------+--------+--+---------------+-------+------------+--+--------+--------+-----------------------------+

(Rationale是一栏，说明OUTPUT栏的原理，而不是字段本身)

(Rationale is a column to explain the rationale for the OUTPUT column, not a field per se)

我目前有以下SQL，通过ID_Ti求和得出FS_1为FSOF_1的百分比:

I currently have the following SQL that sums gives FS_1 as a % of FSOF_1 by ID_Ti:

from tbltourns_atp t inner join stat_atp s on t.id_t = s.id_t
group by t.id_ti

我现在想根据该行日期或该日期之前的ID_Ti记录的总和将该百分比添加到tblTourns_atp中的每一行.我很确定我需要一个带有WHERE语句的子查询，但是我不知道在哪里以及语法.预先感谢.

I'd now like to add that % to every line in the tblTourns_atp based upon the sum of ID_Ti records that are on or before the date for that line. I'm pretty sure I'll need a sub query with a WHERE statement but I can't figure out where and the syntax. Thanks in advance.

推荐答案

加入表并将相关的子查询用于输出:

Join the tables and use a correlated subquery for the output:

SELECT t.id_t, t.id_ti, t.Date, 
    (
      select round(100 * sum(ss.fs_1) / sum(ss.fsof_1), 0) 
      from tbltourns_atp as tt inner join stat_atp as ss on tt.id_t = ss.id_t
      where tt.id_ti = t.id_ti and tt.date <= t.date
    ) as output
FROM  tbltourns_atp as t;

结果:

id_t    id_ti   Date        output
1       1       1/1/2019    50
2       1       5/1/2019    31
3       1       3/1/2019    32
4       2       4/1/2019    30
5       2       5/1/2019    30
6       2       6/1/2019    29
7       3       1/1/2019    50
8       3       8/1/2019    33
9       3       2/1/2019    32
10      3       6/1/2019    35

请注意，您按id_t 8 和 10 的预期发布的Output是错误的.

Note that the Output you posted as expected fro id_t 8 and 10 are wrong.

这篇关于我在哪里放置WHERE语句?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！