MySql 表自连接

Question

我有下表

sku|date      |price
---|----------|-----
A1 |2015-01-10|3
A2 |2015-01-15|2
A1 |2015-01-20|5
A1 |2015-02-10|2
A2 |2015-02-10|1

我正在尝试获取每月的计数。我想得到下面的输出；

sku|JAN_ORDER|FEB_ORDER|JAN_TOTAL
---|---------|---------|---------
A1 |2        |1        |10
A2 |1        |1        |2

我尝试过自连接和左连接，但没有成功，我有点困惑。

我使用下面的代码得到了错误的结果

select s.sku, count(f.sku)
from database f, database s
where f.sku between '2015-01-01' and '2015-01-31'
and
s.sku=f.sku
group by s.sku

请指教。

Answer 1

就像 Marc B 所说，不需要联接:

SQL Fiddle

MySQL 5.6 架构设置:

CREATE TABLE `database`
    (`sku` varchar(2), `date` datetime)
;

INSERT INTO `database`
    (`sku`, `date`)
VALUES
    ('A1', '2015-01-10 00:00:00'),
    ('A2', '2015-01-15 00:00:00'),
    ('A1', '2015-01-20 00:00:00'),
    ('A1', '2015-02-10 00:00:00'),
    ('A2', '2015-02-10 00:00:00')
;

查询 1:

SELECT sku,
   count(IF(month(date) = 1, sku, null)) AS JAN,
   count(IF(month(date) = 2, sku, null)) AS Feb
FROM `database`
GROUP BY sku

<强> Results :

| sku | JAN | Feb |
|-----|-----|-----|
|  A1 |   2 |   1 |
|  A2 |   1 |   1 |