create table ta(name varchar(2), date datetime)
insert ta select 'a', '2007-1-1'
insert ta select 'b', '2007-1-3'
insert ta select 'a', '2007-2-3'
insert ta select 'a', '2007-4-5'
insert ta select 'a', '2007-6-8'
insert ta select 'b', '2007-3-3'
insert ta select 'c', '2007-3-3'
insert ta select 'd', '2007-3-9'select *,id=1 into #
from ta order by name asc,date ascdeclare @a varchar(2),@i int
update #
set @i=case when name=@a then @i+1 else 1 end,
@a=name,id=@ideclare @sql varchar(4000)
set @sql='select name'
select @sql=@sql+',[记录'+rtrim(id)+']=max(case when id='+rtrim(id)+' then date end)'
from # group by id
exec(@sql+' from # group by name')name 记录1 记录2 记录3 记录4
---- ----------------------- ----------------------- ----------------------- -----------------------
a 2007-01-01 00:00:00.000 2007-02-03 00:00:00.000 2007-04-05 00:00:00.000 2007-06-08 00:00:00.000
b 2007-01-03 00:00:00.000 2007-03-03 00:00:00.000 NULL NULL
c 2007-03-03 00:00:00.000 NULL NULL NULL
d 2007-03-09 00:00:00.000 NULL NULL NULL
警告: 聚合或其他 SET 操作消除了空值。(4 行受影响)
insert ta select 'a', '2007-1-1'
insert ta select 'b', '2007-1-3'
insert ta select 'a', '2007-2-3'
insert ta select 'a', '2007-4-5'
insert ta select 'a', '2007-6-8'
insert ta select 'b', '2007-3-3'
insert ta select 'c', '2007-3-3'
insert ta select 'd', '2007-3-9'select *,id=1 into #
from ta order by name asc,date ascdeclare @a varchar(2),@i int
update #
set @i=case when name=@a then @i+1 else 1 end,
@a=name,id=@ideclare @sql varchar(4000)
set @sql='select name'
select @sql=@sql+',[记录'+rtrim(id)+']=max(case when id='+rtrim(id)+' then date end)'
from # group by id
exec(@sql+' from # group by name')name 记录1 记录2 记录3 记录4
---- ----------------------- ----------------------- ----------------------- -----------------------
a 2007-01-01 00:00:00.000 2007-02-03 00:00:00.000 2007-04-05 00:00:00.000 2007-06-08 00:00:00.000
b 2007-01-03 00:00:00.000 2007-03-03 00:00:00.000 NULL NULL
c 2007-03-03 00:00:00.000 NULL NULL NULL
d 2007-03-09 00:00:00.000 NULL NULL NULL
警告: 聚合或其他 SET 操作消除了空值。(4 行受影响)
解决方案 »
- 当用用户回头率分析,一天算一次,如果一天来访了N次也算一次。第二天再来才能加一次
- 执行语句出错:‘SQL Server 内部错误。文本管理器无法继续执行当前语句。’如何解决?
- 这个触发器怎么写
- 如何将指定表中的更新的数据发送到syslog服务器?
- 如何将上月数据转到本月来?
- 求教怎么提高夸server的数据插入性能
- 此问题谁可解决。以后有分我全给他。
- 要在每一条记录指定的值的后面追加个字符串,改怎么做?
- 关于表字段问题
- windows 2003 MSSQL 2008R2 执行操作时突然断电
- 请问sqlservere处理海量数据问题(3亿多条数据)!!!
- 不知道为什么在ASP.NET中查询数据库,每次都会从新建立连接.
insert ta select 'a', '2007-1-1'
insert ta select 'b', '2007-1-3'
insert ta select 'a', '2007-2-3'
insert ta select 'a', '2007-4-5'
insert ta select 'a', '2007-6-8'
insert ta select 'b', '2007-3-3'
insert ta select 'c', '2007-3-3'
insert ta select 'd', '2007-3-9'
--通过临时表
select *,id=1 into #
from ta order by name asc,date asc
--更新临时表id为记录数
declare @a varchar(2),@i int
update #
set @i=case when name=@a then @i+1 else 1 end,
@a=name,id=@i
--查询
declare @sql varchar(4000)
set @sql='select name'
select @sql=@sql+',[记录'+rtrim(id)+']=max(case when id='+rtrim(id)+
' then convert(varchar(10),date,120) else '''' end)'--不显示null时
from # group by id
exec(@sql+' from # group by name')name 记录1 记录2 记录3 记录4
---- ---------- ---------- ---------- ----------
a 2007-01-01 2007-02-03 2007-04-05 2007-06-08
b 2007-01-03 2007-03-03
c 2007-03-03
d 2007-03-09 (4 行受影响)
INSERT @t SELECT 'a', '2007-1-1'
UNION ALL SELECT 'b', '2007-1-3'
UNION ALL SELECT 'a', '2007-2-3'
UNION ALL SELECT 'a', '2007-4-5'
UNION ALL SELECT 'a', '2007-6-8'
UNION ALL SELECT 'b', '2007-3-3'
UNION ALL SELECT 'c', '2007-3-3'
UNION ALL SELECT 'd', '2007-3-9'
select 字段1,
字段2=max(CASE convert(char(7),Quarter,21) WHEN convert(char(4),Quarter,21)+'-01' THEN Quarter END),
字段3=max(CASE convert(char(7),Quarter,21) WHEN convert(char(4),Quarter,21)+'-02' THEN Quarter END),
字段4=max(CASE convert(char(7),Quarter,21) WHEN convert(char(4),Quarter,21)+'-03' THEN Quarter END),
字段5=max(CASE convert(char(7),Quarter,21) WHEN convert(char(4),Quarter,21)+'-04' THEN Quarter END),
字段6=max(CASE convert(char(7),Quarter,21) WHEN convert(char(4),Quarter,21)+'-05' THEN Quarter END),
字段7=max(CASE convert(char(7),Quarter,21) WHEN convert(char(4),Quarter,21)+'-06' THEN Quarter END),
字段8=max(CASE convert(char(7),Quarter,21) WHEN convert(char(4),Quarter,21)+'-07' THEN Quarter END),
字段9=max(CASE convert(char(7),Quarter,21) WHEN convert(char(4),Quarter,21)+'-08' THEN Quarter END),
字段10=max(CASE convert(char(7),Quarter,21) WHEN convert(char(4),Quarter,21)+'-09' THEN Quarter END),
字段11=max(CASE convert(char(7),Quarter,21) WHEN convert(char(4),Quarter,21)+'-10' THEN Quarter END),
字段12=max(CASE convert(char(7),Quarter,21) WHEN convert(char(4),Quarter,21)+'-11' THEN Quarter END),
字段13=max(CASE convert(char(7),Quarter,21) WHEN convert(char(4),Quarter,21)+'-12' THEN Quarter END)
FROM @t
GROUP BY 字段1