现有两个表,分别为表1,表2:
表1
name date time
张三 03/11/01 08:00
张三 03/11/01 18:00
张三 03/11/01 21:00
张三 03/11/02 08:00
张三 03/11/02 18:00
我想把表1转换成表2格式如下:
表2:
name date time1 time2 time3
张三 03/11/01 08:00 18:00 21:00
张三 03/11/02 08:00 18:00
请问用SQL语名怎么写?????详细点
表1
name date time
张三 03/11/01 08:00
张三 03/11/01 18:00
张三 03/11/01 21:00
张三 03/11/02 08:00
张三 03/11/02 18:00
我想把表1转换成表2格式如下:
表2:
name date time1 time2 time3
张三 03/11/01 08:00 18:00 21:00
张三 03/11/02 08:00 18:00
请问用SQL语名怎么写?????详细点
解决方案 »
- 用OLEContainer在系统中嵌入word编辑环境时,当双击word时,word的菜单和工具栏充满了整个Form,怎么能够控制它们只显示在一个Panel里面?
- 求高人翻译一段代码(C->D)
- 求救,多个客户端程序可以同时发送文件到服务器端,服务端程序自动接收文件并保存。
- 内存释放问题。
- 在ActiveX中使用URLDownLoadToFile函数中的参数问题
- 请问哪有delphi开发组件的电子书?或是书店有这样的书?
- 请问如何设置 BDE 连接 Microsoft Visual FoxPro 6.0 的.dbf 文件
- 求救
- 接收邮件的问题,谢谢帮忙!!!
- 大家快去看看啊。这里面有好多书下载。
- 请教,关于“name not Unique in this context”的错误。50分,急!!!!
- 这样的招聘条件是否太高?
insert into table2 (name,date,time1)
select name, date ,min(time)
from table1 as a
group by name,dateupdate table2 set time2 = min(time)
from table1
where table1.name=table2.name and table1.date=table2.date and table1.time<> table2.time1update table2 set time3 = min(time)
from table1
where table1.name=table2.name and table1.date=table2.date and table1.time<> table2.time1 and table1.time<> table2.time2
--未测试。
7:00 到 15:00
16:00 到 19:00
20:00 到24:00select name , date,case where time between '7:00' and '15:00' then time1,
case where time between '16:00' and '19:00' then time2,
case where time between '20:00' and '24:00' then time3
into table2
from table1这样应该差不多你试试
(
select [name],data,
sum(cast(substring((case when [time] between '07:00' and '15:00' then [time] end),1,2)as numeric(10))) as time1,
sum(cast(substring((case when [time] between '16:00' and '19:00' then [time] end),1,2)as numeric(10))) as time2,
sum(cast(substring((case when [time] between '20:00' and '24:00' then [time] end),1,2)as numeric(10))) as time3
from table1
group by [name],data
) table2运行通过
有什么问题可以给我发短消息,OK
insert @a values('张三' , '03/11/01' , '08:00')
insert @a values ('张三' , '03/11/01' , '18:00')
insert @a values ('张三' , '03/11/01' , '21:00')
insert @a values ('张三' , '03/11/02' , '08:00')
insert @a values ('张三' , '03/11/02' , '18:00')
select * from @a
if object_id('tempdb..#bb')>0 drop table #bb
select * ,(select count(*) from @a b where a.date=b.date and b.time<=a.time) ii into #bb from @a a
declare @sql varchar(800)
set @sql='select name,date'
select @sql=@sql+',max(case when ii='+cast(ii as varchar(10))+' then time else '''' end) as time'+cast(ii as varchar(10))
from (select distinct ii from #bb) as aaprint @sql
exec(@sql+' from #bb group by name,date')
SELECT [表1].[name], [表1].[data]
FROM 表1
GROUP BY [表1].[name], [表1].[data]
PIVOT [表1].[time];
select t1.name,t1.date,t1.time,t2.time,t3.time from 表1 t1,表1 t2,表1 t3 where t1.name=t2.name and t.name=t3.name and t1.date=t2.date and t.date =t3.date
delete from 表2 where time1=time2 and time2=time3
or
insert into 表2(name, date,time1,time2,time3) select t1.name,t1.date,t2.time,
t3.time from 表1 as t1 inner join 表1 as t2 inner join 表1 as t3 on t1.name=t2.name and t2.name=t3.name and t1.date=t2.date and t2.date=t3.date and
(t1.time<>t2.time or t1.time<>t3.time or t2.time<>t3.time)