查询结果扩展字段，这个查询如何做？

[okdns]

数据表范例：

date type
---------------------
2010-08-05 a
2010-08-05 a
2010-08-05 a
2010-08-05 b
2010-08-05 c
2010-08-05 c
2010-08-06 a
2010-08-06 b
2010-08-06 c
2010-08-06 c


要求查询结果样式：

date count(type=a) count(type=b) count(type=c)
---------------------------------------------------------------
2010-08-05 3 1 1
2010-08-06 1 1 2

[ACMAIN_CHM]

select `date`,
	sum(if(`type`='a',1,0)) as `count(type=a)`,
	sum(if(`type`='b',1,0)) as `count(type=b)`,
	sum(if(`type`='c',1,0)) as `count(type=c)`
	
from 数据表范例
group by `date`

[ACMAIN_CHM]

另外注意 date, type 均为保留字，在创建表的时候尽量避免使用保留字。

[feixianxxx]

[Quote=引用 1 楼 acmain_chm 的回复:]

SQL code
select `date`,
sum(if(`type`='a',1,0)) as `count(type=a)`,
sum(if(`type`='b',1,0)) as `count(type=b)`,
sum(if(`type`='c',1,0)) as `count(type=c)`

from 数据表范例
group by `date`……
[/Quote]
..就是这样了

LZ这是典型行专列 应该掌握的..
技巧就是用分组加聚合的形式

[okdns]

谢谢两位，学到了