PHP MySQL 分组问题

Question

我的表中有一个列：tbl_customers，它将客户记录区分为 LEAD 或 CUS。

该列很简单：recordtype，其中是一个 char(1)。我用 C 或 L 填充它。 显然C=客户，而L=领导者。

我想运行一个按记录创建日期分组的查询，因此我有一个名为：datecreated 的列。

这是我对分组感到困惑的地方。

我想显示结果（在一个查询中）特定日期或日期范围的客户计数和潜在客户计数。我成功地仅提取了 recordtype:C 或 recordtype:L 的数字，但这需要 2 个查询。

这是我到目前为止所得到的：

  SELECT COUNT(customerid) AS `count`, datecreated 
    FROM `tbl_customers` 
   WHERE `datecreated` BETWEEN '$startdate."' AND '".$enddate."' 
     AND `recordtype` = 'C' 
GROUP BY `datecreated` ASC

正如预期的那样，这显示 2 列（客户记录的计数和创建日期）。

有没有一种方法可以在一个查询中显示两者，同时仍然按 datecreated 列进行分组？

Answer 1

您可以对多个列进行分组。

SELECT COUNT(customerid) AS `count`, datecreated, `recordtype`
FROM `tbl_customers` 
WHERE `datecreated` BETWEEN '$startdate."' AND '".$enddate."' 
GROUP BY `datecreated` ASC, `recordtype`

Answer 2

SELECT COUNT(customerid) AS `count`,
    datecreated,
    SUM(`recordtype` = 'C') AS CountOfC,
    SUM(`recordtype` = 'L') AS CountOfL
FROM `tbl_customers` 
WHERE `datecreated` BETWEEN '$startdate."' AND '".$enddate."' 
GROUP BY `datecreated` ASC

请参阅 是否可以计算其中的两列相同的查询

Answer 3

有两种解决方案，具体取决于您想要将两个计数放在单独的行中还是单独的列中。

在单独的行中：

SELECT datecreated, recordtype, COUNT(*)
FROM tbl_customers
WHERE datecreated BETWEEN '...' AND '...'
GROUP BY datecreated, recordtype

在单独的列中（这称为旋转表格）

SELECT datecreated,
       SUM(recordtype = 'C') AS count_customers,
       SUM(recordtype = 'L') AS count_leads
FROM tbl_customers
WHERE datecreated BETWEEN '...' AND '...'
GROUP BY datecreated

Answer 4

使用：

$query = sprintf("SELECT COUNT(c.customerid) AS count,
                         c.datecreated,
                         SUM(CASE WHEN c.recordtype = 'C' THEN 1 ELSE 0 END) AS CountOfC,
                         SUM(CASE WHEN c.recordtype = 'L' THEN 1 ELSE 0 END) AS CountOfL
                    FROM tbl_customers c 
                   WHERE c.datecreated BETWEEN STR_TO_DATE('%s', '%Y-%m-%d %H:%i') 
                                           AND STR_TO_DATE('%s', '%Y-%m-%d %H:%i')
                GROUP BY c.datecreated",
                  $startdate, $enddate);

需要填写日期格式 - 参见STR_TO_DATE 了解详细信息。