存储过程代码如下:
ALTER proc salesAccount
@syear int,@eyear int,
@smonth int,@emonth int,
@sday int,@eday int
as
select count(a.xsid),b.ygxm as c,b.zb from cjdb as a inner join employment_info as b on a.xsid=b.ygid where year([cjrq]) between @syear and @eyear and month([cjrq]) between @smonth and @emonth and
day([cjrq]) between @sday and @eday and b.bmid= '销售部门 '
group by b.ygxm,b.zb
GO
结果如下:
2 小盛 A
2 小喽喽 C
3 小喽 G
但我现在要这个加了一个a.other
select count(a.xsid),b.ygxm as c,b.zb,a.other from cjdb as a inner join employment_info as b on a.xsid=b.ygid where year([cjrq]) between @syear and @eyear and month([cjrq]) between @smonth and @emonth and
day([cjrq]) between @sday and @eday and b.bmid= '销售部门 '
group by b.ygxm,b.zb,a.other
GO
现在有一个other字段里有值的
运行后的结果如下:
2 小喽 G
1 小喽 G 111
2 小喽喽 C
2 小盛 A
但我要的结果如下:
就是
2 小盛 A
2 小喽喽 C
3 小喽 G 111
能不能把a.other里面的值加起来
------解决方案--------------------
id name value
2 小喽 G
1 小喽 G 111
2 小喽喽 C
2 小盛 A
select sum(id) id ,name,value from (你的查询) t group by name,value
------解决方案--------------------
select count(a.xsid),b.ygxm as c,b.zb,
other=case when sum(case when a.other= ' ' then 0 else cast(a.other as int) end)=0 then ' ' else ltrim(sum(case when a.other= ' ' then 0 else cast(a.other as int) end)) end
from cjdb as a inner join employment_info as b on a.xsid=b.ygid where year([cjrq]) between @syear and @eyear and month([cjrq]) between @smonth and @emonth and
day([cjrq]) between @sday and @eday and b.bmid= '销售部门 '
group by b.ygxm,b.zb
------解决方案--------------------
select sum(xsid+a.other),c,zb from
(
select count(a.xsid) as a.xsid ,b.ygxm as c,b.zb,a.other from cjdb as a inner join employment_info as b on a.xsid=b.ygid where year([cjrq]) between @syear and @eyear and month([cjrq]) between @smonth and @emonth and
day([cjrq]) between @sday and @eday and b.bmid= '销售部门 '
group by b.ygxm,b.zb,a.other
) a
group by c,zb
------解决方案--------------------
--带符号合并行列转换
--有表t，其数据如下：
a b
1 1
1 2
1 3
2 1
2 2
3 1
--如何转换成如下结果:
a b
1 1,2,3
2 1,2
3 1
create table tb
(
a int,
b int
)
insert into tb(a,b) values(1,1)
insert into tb(a,b) values(1,2)
insert into tb(a,b) values(1,3)