php - 加快用 PHP 进行的 MySQL 查询

Question

比如说，我有一张给定的 table here .该表具有以下结构(测试日期、学生姓名和获得的分数)

D       NAME    MARKS
2001-01-01  a   1
2001-01-04  a   4
2001-01-06  a   3
2001-01-08  a   3
2001-01-01  b   1
2001-01-10  b   15
2001-01-01  c   1
2001-01-06  c   2
2001-01-08  c   5
2001-01-10  c   7

我想通过给那些没有参加每项考试的学生打 0 分来更新表格。更新表应类似于 this

D   NAME    MARKS
2001-01-01  a   1
2001-01-02  a   0
2001-01-04  a   4
2001-01-06  a   3
2001-01-08  a   3
2001-01-02  a   0
2001-01-01  b   1
2001-01-02  b   0
2001-01-04  b   0
2001-01-06  b   0
2001-01-08  b   0
2001-01-10  b   15
2001-01-01  c   1
2001-01-02  c   2
2001-01-04  c   0
2001-01-06  c   0
2001-01-08  c   5
2001-01-10  c   7

到目前为止，我能想到的唯一解决方案(非常非常慢的查询)是:

SELECT DISTINCT(D) FROM tableA;
SELECT DISTINCT(NAME) FROM tableA;

使用 PHP，在嵌套循环内进行 sql 查询

INSERT IGNORE (D,NAME,MARKS)($D,$NAME,0);

但是，由于有超过 50k 行，整个代码花费了太多时间(以分钟计)。

有更好的建议吗？

Answer 1

可能对可能的日期和可能的名称进行交叉连接，并将其与当前结果进行左连接:-

INSERT INTO A (D, name, marks)
SELECT Dates.D, Names.name, 0
FROM (SELECT DISTINCT D FROM A) Dates
CROSS JOIN  (SELECT DISTINCT name FROM A) Names
LEFT OUTER JOIN A
ON Dates.D = A.D AND Names.name = A.name
WHERE A.name IS NULL

请注意，这确实假设至少有一个人每天都有一个标记。

如果你有一个姓名表和一个日期表，并且只使用表 A 中这些行的 id，那就更好了

如果您想在某个日期范围内执行此操作，即使那天没有人得到分数:-

INSERT INTO A (D, name, marks)
SELECT Dates.aDate, Names.name, 0
FROM 
(
    SELECT DATE_ADD('2001-01-01', INTERVAL units.i + tens.i * 10 + hundreds.i * 100 DAY) AS aDate
    FROM (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) units
    CROSS JOIN (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) tens
    CROSS JOIN (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) hundreds
    HAVING aDate BETWEEN '2001-01-01' AND '2001-12-30'
) Dates
CROSS JOIN  
(
    SELECT DISTINCT name 
    FROM A
) Names
LEFT OUTER JOIN A
ON Dates.aDate = A.D AND Names.name = A.name
WHERE A.name IS NULL