我刚开始搜索两个日期之间的记录，并显示按月份排序的记录（从1月到12月）。
我有这样的桌子。
Employee | Salary | Date from | Date To
John A. | 15000 | 2013-05-26 | 2013-06-10
Mark | 15000 | 2013-05-26 | 2013-06-10
John A. | 15000 | 2013-06-11 | 2013-06-25
Mark | 20000 | 2013-06-11 | 2013-06-25
我想把报告展示成这样。
Employee | 26 May - June 10 | 11 June - 25 June | So on..
John A. | 15000 | 15000
Mark | 15000 | 20000
请查看我的代码。这将只搜索两个日期之间的记录
SELECT *
FROM payroll
WHERE datefrom >= '2013-01-01'
AND dateto < '2013-12-31'
请告诉我如何解决这个问题。
您需要对数据进行透视，不幸的是，在mysql中，透视是静态的，因此您需要为每种情况编写它（但您可以使用脚本来完成），下面您有一个仅用于示例数据的示例，但它可以继续。
试试这个：
SELECT
employee,
SUM(IF(datefrom='2013-05-26' AND dateto='2013-06-10',Salary,0)) as `26 May - June 10`,
SUM(IF(datefrom='2013-06-11' AND dateto='2013-06-25',Salary,0)) as `11 June - 25 June`
FROM
payroll
WHERE
datefrom >= '2013-01-01'
AND dateto < '2013-12-31'
GROUP BY
employee
使用php 进行尝试
<?php
// connection
$dns = "mysql:host=localhost;dbname=***";
$dbh = new PDO($dns, 'user', '***');
// sql
$query = "SELECT DISTINCT (CONCAT(`datefrom` ,' ', `dateto` )) as `formated date`
FROM empdetail";
$stmt = $dbh->prepare($query);
$stmt->execute();
$case_string = '';
while($r = $stmt->fetch(PDO::FETCH_ASSOC))
{
list($fromdate,$todate) = explode(' ',$r['formated date']);
$from = date('d-M',strtotime($fromdate));
$to = date('d-M',strtotime($todate));
$case_string.= "SUM(IF(datefrom='{$fromdate}' AND dateto='{$todate}',Salary,0)) as `{$from} to {$to}`,";
}
$case_string = rtrim($case_string,',');
$sql = "SELECT employee, {$case_string}
FROM empdetail
GROUP BY employee";
$stmt1 = $dbh->prepare($sql);
$stmt1->execute();
while($r = $stmt1->fetch(PDO::FETCH_ASSOC))
{
// do whatever you want
}
?>
在PHYMYADMIN中运行最终SQL时的输出
╔════════════╦═══════════════════╦══════════════════╗
║ employee ║ 26-May to 10-Jun ║ 11-Jun to 25-Jun ║
╠════════════╬═══════════════════╬══════════════════╣
║ john ║ 15000 ║ 15000 ║
║ Mark ║ 15000 ║ 20000 ║
╚════════════╩═══════════════════╩══════════════════╝