手上有一个供求信息网站，客户要要求对其中的企业会员每月的供求信息发布量进行统计 要求以如下形式打印： 2006-10-1 到 2006-10-31 用户 供应数 求购数 合计 详细 张山 10 20 30 >> 李四 50 3 53 >>
。。。。。
现有的供求信息表（Info）表结构如下 User Info_ID dateAndTime type WDFrog 1 ... 买 WDFrog 2 卖 WDFrog 3 卖 lxj 4 卖 lxj 5 买
则将type列拆分成 sell , buy 2列的代码如下： Select [Id], [user], case [type] When '买' Then 1 Else 0 End As buy, Case [type] When '卖' Then 1 Else 0 End as sell From info
统计求和的代码如下：
Select [Id], [user], SUM(case [type] When '买' Then 1 Else 0 End ) As buy, SUM(Case [type] When '卖' Then 1 Else 0 End ) as sell, count(info_id) as total From vi_AgentInfo
Group by [Id],[user] Order by total DESC
最后还可以这样写，打印效果怎样？大家不妨自己去试试
Select [Id],[user], case [type] When '买' Then '买' Else '卖' End As BAndS, count([Id]) as total
From vi_AgentInfo Group by case [type] When '买' Then '买' Else '卖' End ,[Id],[user]
