在临时表中插入同一行的多个计数

debugcn 发表于 Dev

亚历克斯·拜尔

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

因此第一行应该看起来像。

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语句中失败了，我如何才能将选定的计数添加到temp表的单行中？

瓦吉

修改您的第二个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()))
)

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。