php - 为同一行插入临时表多次计数

Question

我正在尝试创建一个临时表并在该表中显示:每年的结果计数 |每周计数 |每天都在同一行计数。

所以第一行应该是这样的。

8 | 3 | 0 所以这是在年、周、日添加的所有记录

这是我的 php 代码来完成这个，但它不允许我选择计数并将它们插入到表中。

// Create temporary table
$sql = "CREATE TEMPORARY TABLE temp_overview(
company varchar(50),
countYearly int,
countWeekly int,
countDaily int    
)";

$stmt = $conn->prepare($sql);
if(!$stmt->execute()){
    echo "Failed first";    
}

// Insert into temp table
$sql = "INSERT INTO temp_overview(countYearly, countWeekly, countDaily)
SELECT
    (SELECT count(*) FROM client_companies WHERE YEAR(date_added) = YEAR(CURDATE())) AS companyCountY,
    (SELECT count(*) FROM client_companies WHERE DATE(date_added) >= DATE(DATE_SUB(NOW(), INTERVAL 7 DAY))) AS companyCountW,
    (SELECT count(*) FROM client_companies WHERE DAY(date_added) = DAY(CURDATE())) AS companyCountD
FROM
    client_companies";

$stmt = $conn->prepare($sql);
if(!$stmt->execute()){
    echo "Failed second";   
}

在第二个 sql 语句中失败，我如何才能将选定的计数添加到临时表中的单个行？

Answer 1

将你的第二个 sql 修改为:

$sql = "INSERT INTO temp_overview(countYearly, countWeekly, countDaily) VALUES (
(SELECT count(*) FROM client_companies WHERE YEAR(date_added) = YEAR(CURDATE())),
(SELECT count(*) FROM client_companies WHERE DATE(date_added) >= DATE(DATE_SUB(NOW(), INTERVAL 7 DAY))),
(SELECT count(*) FROM client_companies WHERE DAY(date_added) = DAY(CURDATE()))
)