修改一下: ---------------------------------------------------------------------- create table #t(类别 varchar(10),日期 datetime,公司 varchar(10)) insert into #t select '大类','2004-01-02','A' insert into #t select '中类','2004-02-03','B' insert into #t select '小类','2004-02-01','C' insert into #t select '大类','2004-02-02','A' insert into #t select '大类','2004-01-17','B' insert into #t select '小类','2004-01-13','C' insert into #t select '小类','2004-01-15','C' select charindex(left(类别,1),'大中小') from #tdeclare @s varchar(8000) set @s = ''select @s = @s + ',['+right(a.日期,2)+'月'+b.类别+']=sum(case when convert(char(7),日期,120)='''+a.日期+''' and 类别='''+b.类别+''' then 1 else 0 end)' from (select distinct 日期=convert(char(7),日期,120) from #t) a, (select distinct 类别,id=(charindex(left(类别,1),'大中小')) from #t) b order by a.日期,b.IDprint @sdrop table #t
select @s = @s + ',['+right(a.日期,2)+'月'+b.类别+']=sum(case when convert(char(7),日期,120)='''+a.日期+''' and 类别='''+b.类别+''' then 1 else 0 end)' from (select distinct 日期=convert(char(7),日期,120) from #t) a, (select distinct 类别 from #t) b order by 日期,left(类别,1)
把“ from (select distinct 日期=convert(char(7),日期,120) from #t) a, (select distinct 类别 from #t) b order by 日期,charindex(left(类别,1),'大中小') ” 改 “ from (select distinct 日期=convert(char(7),日期,120) from #t) a, (select distinct 类别,charindex(left(类别,1),'大中小') AS ID from #t) b order by 日期,ID ”
同意 libin_ftsafe(子陌红尘) 也可以把下面的先插入临时表 select * from (select distinct 日期=convert(char(7),日期,120) from #t) a, (select distinct 类别 from #t) b
楼主,你试下面语句可以了。 declare @s varchar(8000) set @s = ''select @s = @s + ',['+right(a.日期,2)+'月'+a.类别+']=sum(case when convert(char(7),日期,120)='''+a.日期+''' and 类别='''+a.类别+''' then 1 else 0 end)' from (select top 100 percent *from (select distinct 日期=convert(char(7),日期,120) from #t) a, (select distinct 类别 from #t) b order by 日期 ,charindex(left(类别,1),'大中小') ) a print @s
to:libin_ftsafe 这里出的问题,注释掉后面的一段就正常了:order by 日期--,charindex(left(类别,1),'大中小') 为什么这个排序会有问题???
----------------------------------------------------------------------
create table #t(类别 varchar(10),日期 datetime,公司 varchar(10))
insert into #t select '大类','2004-01-02','A'
insert into #t select '中类','2004-02-03','B'
insert into #t select '小类','2004-02-01','C'
insert into #t select '大类','2004-02-02','A'
insert into #t select '大类','2004-01-17','B'
insert into #t select '小类','2004-01-13','C'
insert into #t select '小类','2004-01-15','C'
select charindex(left(类别,1),'大中小') from #tdeclare @s varchar(8000)
set @s = ''select @s = @s + ',['+right(a.日期,2)+'月'+b.类别+']=sum(case when convert(char(7),日期,120)='''+a.日期+''' and 类别='''+b.类别+''' then 1 else 0 end)'
from
(select distinct 日期=convert(char(7),日期,120) from #t) a,
(select distinct 类别,id=(charindex(left(类别,1),'大中小')) from #t) b
order by a.日期,b.IDprint @sdrop table #t
from
(select distinct 日期=convert(char(7),日期,120) from #t) a,
(select distinct 类别 from #t) b
order by 日期,left(类别,1)
from
(select distinct 日期=convert(char(7),日期,120) from #t) a,
(select distinct 类别 from #t) b
order by 日期,charindex(left(类别,1),'大中小')
”
改
“
from
(select distinct 日期=convert(char(7),日期,120) from #t) a,
(select distinct 类别,charindex(left(类别,1),'大中小') AS ID from #t) b
order by 日期,ID
”
select *
from
(select distinct 日期=convert(char(7),日期,120) from #t) a,
(select distinct 类别 from #t) b
declare @s varchar(8000)
set @s = ''select @s = @s + ',['+right(a.日期,2)+'月'+a.类别+']=sum(case when convert(char(7),日期,120)='''+a.日期+''' and 类别='''+a.类别+''' then 1 else 0 end)'
from (select top 100 percent *from
(select distinct 日期=convert(char(7),日期,120) from #t) a,
(select distinct 类别 from #t) b
order by 日期 ,charindex(left(类别,1),'大中小')
) a
print @s
这里出的问题,注释掉后面的一段就正常了:order by 日期--,charindex(left(类别,1),'大中小')
为什么这个排序会有问题???
我想要的是这个排序为什么会出问题?即问题究竟在哪儿-----------------------你的排序单独查询是没有错的,select @s = @s + ',['+right(a.日期,2)+'月'+a.类别+']=....结合起来给变量@S赋值就不对了,这是后面的排序不能是表达式。