create table ta(tid int, tvid int ,num int, dt datetime)
insert ta select 12, 1, 10, '2007-02-10'
union all select 13, 1, 10, '2007-02-10'
union all select 12, 1, 20, '2007-02-11'
union all select 13, 1, 20, '2007-02-11'借用表变量或临时表:
declare @start datetime,@end datetime
select @start=min(dt) from ta
select @end=max([dt]) from ta
declare @tmp table(date varchar(10))
while convert(varchar(10),@start,120)!>convert(varchar(10),@end,120)
begin
insert @tmp select convert(varchar(10),@start,120)
set @start=@start+1
end
--select * from @tmp
declare @sql varchar(8000)
select @sql=isnull(@sql,'')+','+quotename(rtrim(date)+'(tvid)')+'=max (case convert(varchar(10),dt,120) when '+quotename(date,'''')
+' then tvid else 0 end),'+quotename(rtrim(date)+'(num)')+'=max (case convert(varchar(10),dt,120) when '+quotename(date,'''')
+' then num else 0 end),'+quotename(rtrim(date)+'(dt)')+'=max (case convert(varchar(10),dt,120) when '+quotename(date,'''')
+' then dt else 0 end)'
from @tmp
set @sql='select tid'+@sql+' from ta group by tid'
--print @sql
exec(@sql)(1 行受影响)(1 行受影响)
tid 2007-02-10(tvid) 2007-02-10(num) 2007-02-10(dt) 2007-02-11(tvid) 2007-02-11(num) 2007-02-11(dt)
----------- ---------------- --------------- ----------------------- ---------------- --------------- -----------------------
12 1 10 2007-02-10 00:00:00.000 1 20 2007-02-11 00:00:00.000
13 1 10 2007-02-10 00:00:00.000 1 20 2007-02-11 00:00:00.000(2 行受影响)
insert ta select 12, 1, 10, '2007-02-10'
union all select 13, 1, 10, '2007-02-10'
union all select 12, 1, 20, '2007-02-11'
union all select 13, 1, 20, '2007-02-11'借用表变量或临时表:
declare @start datetime,@end datetime
select @start=min(dt) from ta
select @end=max([dt]) from ta
declare @tmp table(date varchar(10))
while convert(varchar(10),@start,120)!>convert(varchar(10),@end,120)
begin
insert @tmp select convert(varchar(10),@start,120)
set @start=@start+1
end
--select * from @tmp
declare @sql varchar(8000)
select @sql=isnull(@sql,'')+','+quotename(rtrim(date)+'(tvid)')+'=max (case convert(varchar(10),dt,120) when '+quotename(date,'''')
+' then tvid else 0 end),'+quotename(rtrim(date)+'(num)')+'=max (case convert(varchar(10),dt,120) when '+quotename(date,'''')
+' then num else 0 end),'+quotename(rtrim(date)+'(dt)')+'=max (case convert(varchar(10),dt,120) when '+quotename(date,'''')
+' then dt else 0 end)'
from @tmp
set @sql='select tid'+@sql+' from ta group by tid'
--print @sql
exec(@sql)(1 行受影响)(1 行受影响)
tid 2007-02-10(tvid) 2007-02-10(num) 2007-02-10(dt) 2007-02-11(tvid) 2007-02-11(num) 2007-02-11(dt)
----------- ---------------- --------------- ----------------------- ---------------- --------------- -----------------------
12 1 10 2007-02-10 00:00:00.000 1 20 2007-02-11 00:00:00.000
13 1 10 2007-02-10 00:00:00.000 1 20 2007-02-11 00:00:00.000(2 行受影响)
解决方案 »
- 7张表怎么同时比对其中的相同内容
- 获取上一记录
- 一对多的SQL视图咋搞呢?
- 在线等,斑竹救!
- 写了一段程序,安装后数据库备份在客户端,如何恢复到服务器中的数据库中(MS Sql)?
- 如何通过Sql语句将一个表中的日期型字段转化为字符串类型??在线等待
- 谁能告诉哪里有关SQL SERVER的数据类型的介绍?
- 请问在哪里可以下载到关于SQL Server2000的书籍
- 各位大虾:请问能不能在sql语句中的 from 后用一个变量来表示表名。高分相送,不够再加!
- 哪位仁兄有c++联接、处理sql server的例子,不胜感激,可以另开贴给分!!!!
- 3表以上的站内搜索
- 用存储过程加密数据,高手救急啊救急!!!!
insert ta select 12, 1, 10, '2007-02-10'
union all select 13, 1, 10, '2007-02-10'
union all select 12, 1, 20, '2007-02-11'
union all select 13, 1, 20, '2007-02-11'借用表变量或临时表:
declare @start datetime,@end datetime
select @start=min(dt) from ta
select @end=max([dt]) from ta
create table #(date varchar(10))--生成临时表
while convert(varchar(10),@start,120)!>convert(varchar(10),@end,120)
begin
insert # select convert(varchar(10),@start,120)
set @start=@start+1
end执行:
declare @sql varchar(8000)
select @sql=isnull(@sql,'')+','+quotename(rtrim(date)+'(tvid)')+'=max (case convert(varchar(10),dt,120) when '+quotename(date,'''')
+' then tvid else 0 end),'+quotename(rtrim(date)+'(num)')+'=sum (case convert(varchar(10),dt,120) when '+quotename(date,'''')
+' then num else 0 end),'+quotename(rtrim(date)+'(dt)')+'=max (case convert(varchar(10),dt,120) when '+quotename(date,'''')
+' then dt else 0 end)'
from #
set @sql='select tid'+@sql+' from ta group by tid'
--print @sql
exec(@sql)
tid 2007-02-10(tvid) 2007-02-10(num) 2007-02-10(dt) 2007-02-11(tvid) 2007-02-11(num) 2007-02-11(dt)
----------- ---------------- --------------- ----------------------- ---------------- --------------- -----------------------
12 1 10 2007-02-10 00:00:00.000 1 20 2007-02-11 00:00:00.000
13 1 10 2007-02-10 00:00:00.000 1 20 2007-02-11 00:00:00.000(2 行受影响)
drop table tb
gocreate table tb(tid int,tvid int,num int,dt varchar(10))
insert into tb(tid,tvid,num,dt) values(12, 1, 10, '2007-02-10')
insert into tb(tid,tvid,num,dt) values(13, 1, 10, '2007-02-10')
insert into tb(tid,tvid,num,dt) values(12, 1, 20, '2007-02-11')
insert into tb(tid,tvid,num,dt) values(13, 1, 20, '2007-02-11')
godeclare @sql varchar(8000)
set @sql = 'select tid , tvid'
select @sql = @sql + ' , max(case cast(px as varchar) when ''' + cast(px as varchar) + ''' then num else null end) num' + cast(px as varchar)
+ ' , max(case cast(px as varchar) when ''' + cast(px as varchar) + ''' then dt else null end) dt' + cast(px as varchar)
from (select distinct px from (select px=(select count(1) from tb where tid=a.tid and num<a.num)+1 , * from tb a) t ) as a
set @sql = @sql + ' from (select px=(select count(1) from tb where tid=a.tid and num<a.num)+1 , * from tb a) t group by tid , tvid'
print @sql
exec(@sql) drop table tb/*
tid tvid num1 dt1 num2 dt2
----------- ----------- ----------- ---------- ----------- ----------
12 1 10 2007-02-10 20 2007-02-11
13 1 10 2007-02-10 20 2007-02-11
*/
insert into @t select 12,1,10,'2007-02-10'
insert into @t select 13,1,10,'2007-02-10'
insert into @t select 12,1,20,'2007-02-11'
insert into @t select 13,1,20,'2007-02-11'
select
a.*,b.num,b.dt
from
(select * from @t m where not exists(select 1 from @t where tid=m.tid and dt<m.dt)) a,
(select * from @t n where not exists(select 1 from @t where tid=n.tid and dt>n.dt)) b
where
a.tid=b.tid/*
tid tvid num dt num dt
----------- ----------- ----------- ---------------- ----------- ----------------
12 1 10 2007-02-10 20 2007-02-11
13 1 10 2007-02-10 20 2007-02-11
*/
declare @t table(tid int,tvid int,num int,dt varchar(16))
insert into @t select 12,1,10,'2007-02-10'
insert into @t select 13,1,10,'2007-02-10'
insert into @t select 12,1,20,'2007-02-11'
insert into @t select 13,1,20,'2007-02-11'
insert into @t select 12,1,20,'2007-02-12'
insert into @t select 13,1,20,'2007-02-12'
的方法。分都给他吧。各位不会见怪吧