mysql选择条件值

Question

我有一个账单表，其中包含购买日期、项目类型、项目大小和其他详细信息。

计费表

+--------------------------------------------+
| PurchaseDate | ItemType | ItemSize | price |
+--------------------------------------------+
| 1-Jan-2015   | Jumper   | S        | 20    |
| 1-Jan-2015   | Jumper   | S        | 20    |
| 1-Jan-2015   | Jumper   | M        | 20    |
| 1-Jan-2015   | Jumper   | L        | 20    |
| 1-Jan-2015   | Shirt    | M        | 15    |
| 1-Jan-2015   | Shirt    | M        | 15    |
| 2-Jan-2015   | Shirt    | L        | 20    |
+--------------------------------------------+
...

ItemType 是固定的，可以是 Jumper 或 Shirt。ItemSize 是固定的，可以是 S、M 或 L。

我需要的是显示订购商品的摘要PurchaseDate，后跟存在的每个组合的计数每个日期。

示例输出

+----------------------------------------------------------------------------------+
| Date       | Jumper[S] | Jumper[M] | Jumper[L] | Shirt[S] | Shirt[M] | Shirt[L]  |
+----------------------------------------------------------------------------------+
| 1-Jan-2015 | 2         | 1         | 1         | 0        | 1         | 0        |
| 2-Jan-2015 | 1         | 5         | 0         | 1        | 3         | 3        |
| 3-Jan-2015 | 0         | 0         | 0         | 0        | 0         | 0        |
| 4-Jan-2015 | 0         | 3         | 1         | 1        | 2         | 2        |
+----------------------------------------------------------------------------------+

这可以使用 mysql 查询吗？

Answer 1

是的，这是可能的。

select PurchaseDate, 
sum(if(ItemType="Jumper" AND ItemSize="S",1,0)) as "Jumper[S]",
sum(if(ItemType="Jumper" AND ItemSize="M",1,0)) as "Jumper[M]",
sum(if(ItemType="Jumper" AND ItemSize="L",1,0)) as "Jumper[L]",
sum(if(ItemType="Shirt" AND ItemSize="S",1,0)) as "Shirt[S]",
sum(if(ItemType="Shirt" AND ItemSize="M",1,0)) as "Shirt[M]",
sum(if(ItemType="Shirt" AND ItemSize="L",1,0)) as "Shirt[L]"

from TableName
group by PurchaseDate;

案例:如果您需要一个月的所有日期，创建一个表，其中一列包含所有日期。设表名为“datetable”，列名为“datecolumn”。

select t1.datecolumn, 
sum(if(ItemType="Jumper" AND ItemSize="S",1,0)) as "Jumper[S]",
sum(if(ItemType="Jumper" AND ItemSize="M",1,0)) as "Jumper[M]",
sum(if(ItemType="Jumper" AND ItemSize="L",1,0)) as "Jumper[L]",
sum(if(ItemType="Shirt" AND ItemSize="S",1,0)) as "Shirt[S]",
sum(if(ItemType="Shirt" AND ItemSize="M",1,0)) as "Shirt[M]",
sum(if(ItemType="Shirt" AND ItemSize="L",1,0)) as "Shirt[L]"

from datetable  t1 left join TableName  t2 on ( date(t1.datecolumn)=date(t2.PurchaseDate))

Where date(t1.datecolumn) between <date_1> and <date_2> // optional if you want data between two dates

group by t1.datecolumn;

注意:代码未经测试。如果发现任何语法错误，请告诉我。