Node.js：無法將 SQL 查詢結果儲存在陣列中

Question

我正在嘗試將 SQL 查詢的結果推送到陣列。但是，它似乎不起作用。我在網上找不到解決方案。如果有人能幫我解決這個問題，我將不勝感激。

let data = [];
        connection.query("SELECT DISTINCT referer FROM publisher", (error, results) => {
        if (error) {
            console.log(error);
        } else {
            results.forEach(async (result) => {
                let referer = result.referer;
                let today = new Date();
                for (let i = 0; i < 24; i++) {
                    let hour = i;
                    let start = new Date(today);
                    start.setHours(hour, 0, 0, 0);
                    let end = new Date(today);
                    end.setHours(hour, 59, 59, 999);
                    connection.query("SELECT COUNT(*) AS count FROM publisher WHERE referer = ? AND first_seen >= ? AND first_seen <= ?", [referer, start, end], (error, results) => {
                        if (error) {
                            console.log(error);
                        } else {
                            let count = results[0].count;
                            data.push({
                                referer: referer,
                                hour: hour,
                                count: count
                            });
                        }
                    });
                }
            });
        }
    });

Answer 1

您可以嘗試使用單一查詢從MySql DB取得資料

select referer, hour(first_seen) h, count(*) n
from publisher
where date(first_seen) = curdate()
group by referer, hour(first_seen)
order by referer, hour(first_seen)

這樣就消除了 js 程式碼中的內部非同步方法。