COUNT问题,急急~!!!!
存储过程代码如下:
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)
insert into tb(a,b) values(2,1)
insert into tb(a,b) values(2,2)
insert into tb(a,b) values(3,1)
go
if object_id( 'pubs..f_hb ') is not null
drop function f_hb
go
--创建一个合并的函数
create function f_hb(@a int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str = ' '
select @str = @str + ', ' + cast(b as varchar) from tb where a = @a
set @str = right(@str , len(@str) - 1)
return(@str)
End
go
--调用自定义函数得到结果：
select distinct a ,dbo.f_hb(a) as b from tb
drop table tb
--结果
a b
----------- ------
1 1,2,3
2 1,2
3 1
（所影响的行数为 3 行）
多个前列的合并
数据的原始状态如下：
ID PR CON OP SC
001 p c 差 6
001 p c 好 2
001 p c 一般 4
002 w e 差 8
002 w e 好 7
002 w e 一般 1
===========================
用SQL语句实现，变成如下的数据
ID PR CON OPS
001 p c 差(6),好(2),一般(4)
002 w e 差(8),好(7),一般(1)
if object_id( 'pubs..tb ') is not null
drop table tb
go
create table tb
(
id varchar(10),
pr varchar(10),
con varchar(10),
op varchar(10),
sc int
)
insert into tb(ID,PR,CON,OP,SC) values( '001 ', 'p ', 'c ', '差 ', 6)
insert into tb(ID,PR,CON,OP,SC) values( '001 ', 'p ', 'c ', '好 ', 2)
insert into tb(ID,PR,CON,OP,SC) values( '001 ', 'p ', 'c ', '一般 ', 4)
insert into tb(ID,PR,CON,OP,SC) values( '002 ', 'w ', 'e ', '差 ', 8)
insert into tb(ID,PR,CON,OP,SC) values( '002 ', 'w ', 'e ', '好 ', 7)
insert into tb(ID,PR,CON,OP,SC) values( '002 ', 'w ', 'e ', '一般 ', 1)
go
if object_id( 'pubs..test ') is not null
drop table test
go
select ID,PR,CON , OPS = op + '( ' + cast(sc as varchar(10)) + ') ' into test from tb
--创建一个合并的函数
if object_id( 'pubs..f_hb ') is not null
drop function f_hb
go
create function f_hb(@id varchar(10),@pr varchar(10),@con varchar(10))
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str = ' '
select @str = @str + ', ' + cast(OPS as varchar) from test where id = @id and @pr = pr and @con = con
set @str = right(@str , len(@str) - 1)
return(@str)
End
go
--调用自定义函数得到结果：
select distinct id ,pr , con , dbo.f_hb(id,pr,con) as OPS from test
drop table tb
drop table test
--结果
id pr con OPS
---------- ---------- ---------- -------------------
001 p c 差(6),好(2),一般(4)
002 w e 差(8),好(7),一般(1)
（所影响的行数为 2 行）
create table b
(col varchar(20))
insert b values ( 'a ')
insert b values ( 'b ')
insert b values ( 'c ')
insert b values ( 'd ')
insert b values ( 'e ')
declare @sql varchar(1024)
set @sql= ' '
select @sql=@sql+b.col+ ', ' from (select col from b) as b
set @sql= 'select ' ' '+@sql+ ' ' ' '
exec(@sql)
