在一个试图中有如下数据/*
ID Name Number AddTime UserID
7 时尚新闻 1 2010-02-01 00:00:00.000 110
8 童装新闻 2 2010-02-01 00:00:00.000 110
8 童装新闻 3 2010-02-02 00:00:00.000 110
8 童装新闻 4 2010-02-03 00:00:00.000 110
9 经销商动态 NULL NULL 110
10 面料市场 1 2010-02-01 00:00:00.000 110
11 棉花市场 1 2010-02-01 00:00:00.000 110
12 纱线行情 NULL NULL 110
13 羊毛市场 NULL NULL 110
14 市场透析 NULL NULL 110
15 缝纫设备 1 2010-02-01 00:00:00.000 110
16 原料市场 NULL NULL 110
17 针织市场 NULL NULL 110
18 护照知识 NULL NULL 110
19 爱的教育 1 2010-02-01 00:00:00.000 110
20 爱心大使 1 2010-02-01 00:00:00.000 110
21 超级导购 NULL NULL 110
22 行业信息 1 2010-02-01 00:00:00.000 110
23 外贸资讯 NULL NULL 110
...
*/想让它变成UserID AddTime 时尚新闻 童装新闻 面料市场 棉花市场 ...
110 2010-02-01 1 2 1 1
110 2010-02-02 0 3 0 0
110 2010-02-03 0 4 0 0
111 2010-02-01 .......
111 2010-02-02 .......
根据UserID以及AddTime分组该怎么写SQL?
我这样写一直有问题
declare @sql varchar(500)
set @sql = 'select AddTime'
select @sql=@sql+',max(case Name when '''+[Name]+''' then isnull(Number,0) end) ['+[Name]+']'
from (select distinct Name from NewsView) as a
set @sql = @sql +' from NewsView group by AddTime'
exec(@sql)
ID Name Number AddTime UserID
7 时尚新闻 1 2010-02-01 00:00:00.000 110
8 童装新闻 2 2010-02-01 00:00:00.000 110
8 童装新闻 3 2010-02-02 00:00:00.000 110
8 童装新闻 4 2010-02-03 00:00:00.000 110
9 经销商动态 NULL NULL 110
10 面料市场 1 2010-02-01 00:00:00.000 110
11 棉花市场 1 2010-02-01 00:00:00.000 110
12 纱线行情 NULL NULL 110
13 羊毛市场 NULL NULL 110
14 市场透析 NULL NULL 110
15 缝纫设备 1 2010-02-01 00:00:00.000 110
16 原料市场 NULL NULL 110
17 针织市场 NULL NULL 110
18 护照知识 NULL NULL 110
19 爱的教育 1 2010-02-01 00:00:00.000 110
20 爱心大使 1 2010-02-01 00:00:00.000 110
21 超级导购 NULL NULL 110
22 行业信息 1 2010-02-01 00:00:00.000 110
23 外贸资讯 NULL NULL 110
...
*/想让它变成UserID AddTime 时尚新闻 童装新闻 面料市场 棉花市场 ...
110 2010-02-01 1 2 1 1
110 2010-02-02 0 3 0 0
110 2010-02-03 0 4 0 0
111 2010-02-01 .......
111 2010-02-02 .......
根据UserID以及AddTime分组该怎么写SQL?
我这样写一直有问题
declare @sql varchar(500)
set @sql = 'select AddTime'
select @sql=@sql+',max(case Name when '''+[Name]+''' then isnull(Number,0) end) ['+[Name]+']'
from (select distinct Name from NewsView) as a
set @sql = @sql +' from NewsView group by AddTime'
exec(@sql)
set @sql = 'select UserID , convert(varchar(10),AddTime,120) AddTime'
select @sql=@sql+',sum(case Name when '''+[Name]+''' then Number else 0 end) ['+[Name]+']'
from (select distinct Name from NewsView) as a
set @sql = @sql +' from NewsView group by UserID , convert(varchar(10),AddTime,120)'
exec(@sql)
max(case Name when '童装新闻' then Number else 0 end) as '童装新闻',
max(case Name when '棉花市场' then Number else 0 end) as '棉花市场',
max(case Name when '面料市场' then Number else 0 end) as '面料市场',
max(case Name when '经销商动态' then Number else 0 end) as '经销商动态',
max(case Name when '羊毛市场' then Number else 0 end) as '羊毛市场',
max(case Name when '市场透析' then Number else 0 end) as '市场透析'
from NewsView group by AddTime,UserID
having AddTime is not null
/*
2010-02-01 110 2 1 1 0 0 0
2010-02-01 111 10 0 0 0 0 0
2010-02-02 110 3 0 0 0 0 0
2010-02-03 110 4 0 0 0 0 0*/
if object_id('class') is not null
drop table class
Go
Create table class(
student nvarchar(2),
course nvarchar(2),
score int
)
Insert class
select '张三','语文',78 union all
select '张三','数学',87 union all
select '张三','英语',82 union all
select '张三','物理',90 union all
select '李四','语文',65 union all
select '李四','数学',77 union all
select '李四','英语',65 union all
select '李四','物理',85 --静态实现方法:
select student,
MAX(case course when '语文' then score else 0 end) as 语文,
MAX(case course when '数学' then score else 0 end) as 数学,
MAX(case course when '英语' then score else 0 end) as 英语,
MAX(case course when '物理' then score else 0 end) as 物理,
SUM(score) as 总分,
cast(AVG(score) as numeric(3,1)) as 平均分
from class group by student--动态实现方法:
go
if OBJECT_ID('p_change')is not null
drop proc p_change
go
create proc p_change
as
declare @name varchar(max)
set @name=''
select @name=@name+','+course+'=max(case when course='+quotename(course,'''')
+' then score else 0 end)'
+CHAR(10)+CHAR(13)
from class group by course
print @name
select @name='select student'+@name+',sum(score) as 总分'+' from class group by student'
print @name
exec(@name)exec p_change
set @sql = 'select UserID , convert(varchar(10),AddTime,120) AddTime'
select @sql=@sql+',sum(case Name when '+quotename([name],'''')+' then Number else 0 end) ['+[Name]+']'
from (select distinct Name from NewsView) as a
set @sql = @sql +' from NewsView group by UserID , convert(varchar(10),AddTime,120)'
exec(@sql)改成红色的部分试试
--自己把表名tb改为newsview即可.
create table tb(ID int, Name varchar(20),Number int,AddTime datetime,UserID int)
insert into tb values(7 , '时尚新闻' , 1 ,'2010-02-01 00:00:00.000', 110)
insert into tb values(8 , '童装新闻' , 2 ,'2010-02-01 00:00:00.000', 110)
insert into tb values(8 , '童装新闻' , 3 ,'2010-02-02 00:00:00.000', 110)
insert into tb values(8 , '童装新闻' , 4 ,'2010-02-03 00:00:00.000', 110)
insert into tb values(9 , '经销商动态', NULL , NULL , 110)
godeclare @sql varchar(500)
set @sql = 'select UserID , isnull(convert(varchar(10),AddTime,120),'''') AddTime'
select @sql=@sql+',sum(case Name when '''+[Name]+''' then isnull(Number,0) else 0 end) ['+[Name]+']'
from (select distinct Name from tb) as a
set @sql = @sql +' from tb group by UserID , isnull(convert(varchar(10),AddTime,120),'''')'
exec(@sql)drop table tb/*
UserID AddTime 经销商动态 时尚新闻 童装新闻
----------- ---------- ----------- ----------- -----------
110 0 0 0
110 2010-02-01 0 1 2
110 2010-02-02 0 0 3
110 2010-02-03 0 0 4*/
print(@sql)
然后看语句错在什么地方?
declare @sql varchar(8000)
@sql长度不够
declare那部分是你提供给我的那个sql语句
下面那个就是打印出来的,打印到那部分就提示出错了。
是什么问题呢?
declare那部分是你提供给我的那个sql语句
下面那个就是打印出来的,打印到那部分就提示出错了。
是什么问题呢?
也谢谢(爱新觉罗.毓华),(TracyLee)
我在13楼的回复
还真的是汗 没注意,谢谢!
也谢谢(爱新觉罗.毓华),(TracyLee)