|
usertype表
统计后的收费表
SELECT Operator, ChargeType, SUM(Price) AS price
FROM InOutList
GROUP BY Operator, ChargeType
ORDER BY Operator, ChargeType
| Operator |
ChargeType |
price |
| System |
大车 |
160 |
| System |
免费 |
0 |
| System |
小车 |
40 |
| System |
中巴 |
45 |
| 冯丽 |
大车 |
0 |
| 冯丽 |
免费 |
0 |
| 冯丽 |
小车 |
510 |
| 冯丽 |
中巴 |
15 |
| 焦艳玲 |
大车 |
60 |
| 卢建玲 |
大车 |
100 |
| 马春艳 |
大车 |
220 |
| 徐金涛 |
免费 |
0 |
| 徐金涛 |
小车 |
240 |
| 徐金涛 |
中巴 |
30 |
| 杨天明 |
大车 |
0 |
| 岳红胜 |
免费 |
0 |
| 岳红胜 |
小车 |
80 |
| 张新乐 |
免费 |
0 |
| 张新乐 |
小车 |
190 |
要求的效果如下：
| operator |
小车 |
大车 |
中巴 |
免费 |
| System |
40 |
160 |
45 |
0 |
| 冯丽 |
510 |
0 |
15 |
0 |
| 焦艳玲 |
0 |
60 |
0 |
0 |
| 卢建玲 |
0 |
100 |
0 |
0 |
| 马春艳 |
0 |
220 |
0 |
0 |
| 徐金涛 |
240 |
0 |
30 |
0 |
| 杨天明 |
0 |
0 |
0 |
0 |
| 岳红胜 |
80 |
0 |
0 |
0 |
| 张新乐 |
190 |
0 |
0 |
0 |
实现：
declare @sql varchar(8000)
set @sql='select bb.operator'
select @sql=@sql+','+usertype.ChargeName+'=isnull(sum(case chargetype when '''+usertype.ChargeName+''' then bb.price end),0) 'from usertype
set @sql=@sql+' from (SELECT Operator, ChargeType, SUM(Price) AS price
FROM InOutList
GROUP BY Operator, ChargeType) bb group by operator order by operator'
--print @sql
exec(@sql)
|