mysql - MySQL 中的数据透视表 - 根据日期时间列显示第一个和最后一个值

Question

经过几个小时的研究和尝试，我终于在 Stackoverflow 上注册了，它通过阅读帮助了我多年。非常感谢这次学习机会!我现在希望能找到解决我无法解决的 MySQL 问题的帮助。我卡住了。

来源:我有一张客户订单表。

输出: 我想要按电子邮件地址分组的订单，对于每个电子邮件地址，应该有总营业额的总和，订单计数以及创建的第一个订单和最后一个订单的行创建。 很好，直到这里，可以通过分组和 min() 和 max() 函数来完成。 我被困在哪里:我想要第一个和最后一个订单的 IP 地址，第一个和最后一个订单等等(进一步查看所需的输出)。

我尝试和研究的内容: Groupwise max，在子选择内有一个顺序的子选择和在外部的一个组，几个连接变体。

这是一个带有随机数据的 SQLfiddle 和一个我构建的 sql 查询。它的工作原理是: http://sqlfiddle.com/#!9/0272b/6

作为 SQLfiddle 中 SQL 的替代方法，我尝试了这个，它适用于一个电子邮件地址:

SELECT
    lastentry.entity_id,
    lastentry.customer_email,
    firstentry.created_at AS FirstOrder,
    lastentry.created_at  AS LastOrder,
    COUNT(lastentry.entity_id) AS TotalOrders,
    SUM(lastentry.grand_total) AS TotalTurnover,
    firstentry.entity_id,
    firstentry.remote_ip AS FirstIP,
    lastentry.remote_ip  AS LastIP
FROM
    orders lastentry
LEFT OUTER JOIN
    (
        SELECT
            co1.entity_id,
            co1.customer_email,
            co1.remote_ip,
            co1.created_at
        FROM
            orders AS co1,
            (
                SELECT
                    customer_email,
                    remote_ip,
                    MIN(created_at) AS maxpop
                FROM
                    orders
                GROUP BY
                    customer_email) AS co2
        WHERE
            co2.customer_email = co1.customer_email
        AND co1.created_at = co2.maxpop ) AS firstentry
ON
    (
        lastentry.customer_email = firstentry.customer_email )
ORDER BY
    lastentry.created_at DESC,
    firstentry.created_at ASC
LIMIT 1

我也一直在尝试在 where 语句中使用子选择进行子选择或连接，但没有成功:

created_at = (SELECT MAX(t2.created_at)
    FROM orders t2
    WHERE customer_email= t1.customer_email
)

我实际想要的输出如下所示:

| customer_email           | FirstOrder          | LastOrder           | TotalOrders | TotalTurnover | FirstIP       | LastIP         | FirstName | LastName |
|--------------------------|---------------------|---------------------|-------------|---------------|---------------|----------------|-----------|----------|
| darmstrong3@skype.com    | 2014-11-06 16:38:31 | 2014-11-15 11:14:42 | 2           | 116,09        | 103.132.17.9  | 153.241.73.137 | David     | David    |
| fthompson0@moonfruit.com | 2014-08-19 06:26:26 | (null)              | 1           | 1,1           | 87.217.157.91 | (null)         | Frank     | (null)   |
| jrice2@icq.com           | 2014-06-01 09:59:10 | (null)              | 1           | 95,76         | 117.4.9.206   | (null)         | Joshua    | (null)   |
| kphillips8@oracle.com    | 2015-01-30 22:49:56 | (null)              | 1           | 57,12         | 220.77.70.87  | (null)         | Kevin     | (null)   |
| lcruz5@techcrunch.com    | 2014-10-27 01:02:46 | (null)              | 1           | 90,45         | 122.38.175.17 | (null)         | Larry     | (null)   |
| scarpenter1@salon.com    | 2012-11-05 07:56:38 | 2014-06-09 21:57:20 | 3           | 163,58        | 220.75.17.164 | 203.81.207.35  | Steven    | Lousie   |

感谢任何帮助!

---

我问自己的问题:

• 这在 MySQL 中可以解决吗？在 Excel 中，我可以简单地构建一个关键表。
• 如果不是，我可以通过使用存储过程来解决它吗？循环遍历所有记录，如果不超过一个订单则跳过字段？

Answer 1

您还可以使用子查询来查找第一个和最后一个订单的行值。这假设第一个和最后一个订单是根据 entity_id。

SELECT 
   customer_email, 
   COUNT(*) AS total_orders, 
   SUM(grand_total) AS total_turnover,
   (SELECT created_at FROM orders WHERE 
       entity_id = MIN(t.entity_id)) AS first_created_at,
   (SELECT created_at FROM orders WHERE 
       entity_id = MAX(t.entity_id)) AS last_created_at,
   (SELECT remote_ip FROM orders WHERE 
       entity_id = MIN(t.entity_id)) AS first_remote_ip,
   (SELECT remote_ip FROM orders WHERE 
       entity_id = MAX(t.entity_id)) AS last_remote_ip,
   (SELECT customer_firstname FROM orders WHERE 
       entity_id = MIN(t.entity_id)) AS first_customer_firstname,
   (SELECT customer_firstname FROM orders WHERE 
       entity_id = MAX(t.entity_id)) AS last_customer_firstname
FROM orders AS t
GROUP BY customer_email