mysql行转列查询

CREATE TABLE sales (
    id INT PRIMARY KEY,
    product VARCHAR(50),
    sales_date DATE,
    amount DECIMAL(10, 2)
);

SELECT 
    product,
    SUM(CASE WHEN sales_date = '2023-01-01' THEN amount ELSE 0 END) AS '2023-01-01',
    SUM(CASE WHEN sales_date = '2023-01-02' THEN amount ELSE 0 END) AS '2023-01-02',
    -- ... 其他日期
FROM 
    sales
GROUP BY 
    product;

$dates = ['2023-01-01', '2023-01-02', /* ... 其他日期 */];
$caseStatements = [];

foreach ($dates as $date) {
    $caseStatements[] = "SUM(CASE WHEN sales_date = '{$date}' THEN amount ELSE 0 END) AS '{$date}'";
}

$caseStatementString = implode(', ', $caseStatements);

$sql = "SELECT product, {$caseStatementString} FROM sales GROUP BY product";

// 执行查询...