Question

我在构造我的奇迹时有问题:

我的本表“订阅者”是我储存通讯邮寄名单的用户。

表格照此(简化):

--
-- Table structure for tSubscriber
--
CREATE TABLE tSubscriber (
    fId INT UNSIGNED NOT NULL AUTO_INCREMENT,
    fSubscriberGroupId INT UNSIGNED NOT NULL,
    fEmail VARCHAR(255) NOT NULL DEFAULT   ,
    fDateConfirmed DATETIME NOT NULL DEFAULT  0000-00-00 00:00:00 ,
    fDateUnsubscribed TIMESTAMP NOT NULL DEFAULT  0000-00-00 00:00:00 ,
    PRIMARY KEY (fId),
    INDEX (fSubscriberGroupId),
) ENGINE=MyISAM;

现在,我想完成的是一张图表,显示每个用户集团每月的订阅和订阅情况。

So I need to extract the year and months from the fDateConfirmed, fDateUnsubscribed dates, count them and show the count sorted by month and year for a subscriber group.

我认为,这一棘手问题非常复杂,我只能接手。即便是这样,也有可能。

Answer 1

你们需要两个单独的询问,一个是订阅,另一个是未订阅。

SELECT COUNT(*), YEAR(fDateConfirmed), MONTH(fDateConfirmed) FROM tSubscriber GROUP BY YEAR(fDateConfirmed), MONTH(fDateConfirmed)
SELECT COUNT(*), YEAR(fDateUnsubscribed), MONTH(fDateUnsubscribe ) FROM tSubscriber GROUP BY YEAR(fDateUnsubscribed), MONTH(fDateUnsubscribed)

友情链接