在MySQL或MSSQL中,我需要生成一个结果集,该结果集将返回过去三个月按月分组的qheader
。所以我有questionsec
表架构
+-----------+
|questionsec|
+-----------+
|qid (PK) |
|qheader |
+-----------+
后
(select * from questionsec)
我得到:
+-------+------+
|qid| qheader |
+---|----------+
| 1 | QH1 |
| 2 | QH2 |
+---+----------+
我需要编写什么查询才能生成以下结果集:
+-------+---------+
|month | qheader |
+-------|---------+
|2014-09| QH1 |
|2014-09| QH2 |
|2014-10| QH1 |
|2014-10| QH2 |
|2014-11| QH1 |
|2014-11| QH2 |
+-------+---------+
我知道以下内容将生成(在 MySQL 中)过去三个月,但不确定如何在questionsec
中找到的每个qheader
加入每个月行
SELECT DATE_FORMAT(now()- INTERVAL 1 MONTH,'%Y-%m') as month
UNION ALL SELECT DATE_FORMAT(now()- INTERVAL 2 MONTH,'%Y-%m')
UNION ALL SELECT DATE_FORMAT(now()- INTERVAL 3 MONTH,'%Y-%m')
CREATE TABLE questionsec(qid INT NOT NULL,qheader CHAR(3));
INSERT INTO questionsec VALUES (1,'QH1'),(2,'QH2');
SELECT qheader,month FROM questionsec x JOIN (SELECT '2014-09' month UNION SELECT '2014-10' UNION SELECT '2014-11')y;
+---------+---------+
| qheader | month |
+---------+---------+
| QH1 | 2014-09 |
| QH2 | 2014-09 |
| QH1 | 2014-10 |
| QH2 | 2014-10 |
| QH1 | 2014-11 |
| QH2 | 2014-11 |
+---------+---------+