Question

我有两个表格。第一是订户。登记员还被任命为一类。下表是用户支付的款项。我想知道,在签署和支付签字人时,平均时间是多少(可以多出)。

这里是一纸空文,但我只想做些什么——尽管我感觉到我很接近;

SELECT category,
AVG(TIMESTAMPDIFF(HOUR, subs.timestamp, MIN(payments.timestamp)))
FROM subs
JOIN payments ON (payments.user_id = subs.user_id)
GROUP BY category

现在,由于卫生部的职能,我得到“有效利用团体职能”的帮助,因此这样做是正确的。现在我必须做些什么? 提前感谢!

Answer 1

SELECT category,
AVG(TIMESTAMPDIFF(HOUR, subs.timestamp, p.timestamp))
FROM subs
JOIN ( SELECT user_id
            , min(timestamp) timestamp
         FROM payments 
       GROUP BY user_id
     ) p
ON p.user_id = subs.user_id
GROUP BY category

如果你需要用这一询问的结果更新另一个表格,那么你可以做这样的事情(没有测试,因此可能会出现同学错误,但希望你能够了解这一想法)。我假定,<代码>another_table 有类别和vg_hrs_spent栏。

UPDATE another_table
   SET avg_hrs_spent =
     (
       SELECT a.avg_hrs_spent FROM
       (
         (SELECT category,
                 AVG(TIMESTAMPDIFF(HOUR, subs.timestamp, p.timestamp)) avg_hrs_spent
            FROM subs
            JOIN ( SELECT user_id
                        , min(timestamp) timestamp
                     FROM payments 
                   GROUP BY user_id
               ) p
            ON p.user_id = subs.user_id
            GROUP BY category) a
       )
       WHERE a.category = another_table.category
     )

友情链接