难，SQL语句更新数据！

wori 2013-02-26 04:57:38 
 if exists (select * from sysobjects where id = OBJECT_ID('[kqRecord]') and OBJECTPROPERTY(id, 'IsUserTable') = 1) DROP TABLE [kqRecord] 

 CREATE TABLE [kqRecord] ( [Guid] [varchar] (32) NOT NULL , [RegisterNumber] [varchar] (20) NOT NULL , [Name] [varchar] (20) NOT NULL , [Ndate] [datetime]  NOT NULL , [Ntime] [datetime]  NOT NULL , [Nlocate] [varchar] (3) NOT NULL , [Mode] [varchar] (8) NOT NULL )

 ALTER TABLE [kqRecord] WITH NOCHECK ADD CONSTRAINT [PK_kqRecord] PRIMARY KEY  NONCLUSTERED ( [Guid] )



 INSERT [kqRecord] ( [Guid] , [RegisterNumber] , [Name] , [Ndate] , [Ntime] , [Nlocate] , [Mode] ) VALUES ( '0386620692784FD9A01E4CF43E9F8745' , '100110' , 'micropu' , '2013-02-26 00:00:00.000' , '1899-12-30 14:23:20.000' , '100' , '人脸识别' )

 INSERT [kqRecord] ( [Guid] , [RegisterNumber] , [Name] , [Ndate] , [Ntime] , [Nlocate] , [Mode] ) VALUES ( '29B9D364884D495E9CEE961928CD40AD' , '100110' , 'micropu' , '2013-02-26 00:00:00.000' , '1899-12-30 14:21:31.000' , '100' , '人脸识别' )

 INSERT [kqRecord] ( [Guid] , [RegisterNumber] , [Name] , [Ndate] , [Ntime] , [Nlocate] , [Mode] ) VALUES ( 'AA0A5CA236D543AB888A352F58075D46' , '100110' , 'micropu' , '2013-02-25 00:00:00.000' , '1899-12-30 14:25:28.000' , '100' , '人脸识别' )






 if exists (select * from sysobjects where id = OBJECT_ID('[RecordImage]') and OBJECTPROPERTY(id, 'IsUserTable') = 1) DROP TABLE [RecordImage] 

 CREATE TABLE [RecordImage] ( [RecordGUID] [varchar] (32) NOT NULL , [RecordImage] [image] NULL )

 ALTER TABLE [RecordImage] WITH NOCHECK ADD CONSTRAINT [PK_RecordImage] PRIMARY KEY  NONCLUSTERED ( [RecordGUID] )





 INSERT [RecordImage] ( [RecordGUID] , [RecordImage] ) VALUES ( '0386620692784FD9A01E4CF43E9F8745' ,图片数据3) 

 INSERT [RecordImage] ( [RecordGUID] , [RecordImage] ) VALUES ( '29B9D364884D495E9CEE961928CD40AD' ,图片数据2) 

 INSERT [RecordImage] ( [RecordGUID] , [RecordImage] ) VALUES ( 'AA0A5CA236D543AB888A352F58075D46' , 图片数据1 )









--系统临时表

drop table ##temp 



declare @s varchar(8000)

set @s='select convert(varchar,Ndate,23) 日期,registerNumber 注册号,name 姓名 '

select @s=@s+',max(case when rn='+ltrim(rn)+' then convert(varchar,Ntime,24) end) as 打卡'+ltrim(rn)

+',max(case when rn='+ltrim(rn)+' then Guid end) as 图像'+ltrim(rn)

from (select distinct rn from (select rn=row_number()over(partition by registerNumber,ndate order by ndate,ntime),Ntime,Ndate from kqRecord where ndate>='2013-2-1')a)t

set @s=@s+' into ##temp from (select rn=row_number()over(partition by registerNumber,ndate order by ndate,ntime),* from kqrecord where ndate>=''2013-2-1''

) t group by ndate,registerNumber,name'

 

exec(@s)



--update ##temp 



select * from ##temp


日期	注册号	姓名	打卡1	图像1	打卡2	图像2

2013-02-25	100110	micropu	14:25:28	AA0A5CA236D543AB888A352F58075D46	NULL	NULL

2013-02-26	100110	micropu	14:21:31	29B9D364884D495E9CEE961928CD40AD	14:23:20	0386620692784FD9A01E4CF43E9F8745
...全文
424 18 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
18 条回复
切换为时间正序
请发表友善的回复…
发表回复
wori 2013-02-27
  • 打赏
  • 举报
 回复
继续求！继续求！继续求！
wori 2013-02-27
  • 打赏
  • 举报
 回复
谢谢了，非常完美！
昵称被占用了 2013-02-27
  • 打赏
  • 举报
 回复 
 
 --考勤记录表
 if exists (select * from sysobjects where id = OBJECT_ID('[kqRecord]') and OBJECTPROPERTY(id, 'IsUserTable') = 1) DROP TABLE [kqRecord] 
 CREATE TABLE [kqRecord] ( [Guid] [varchar] (32) NOT NULL , [RegisterNumber] [varchar] (20) NOT NULL , [Name] [varchar] (20) NOT NULL , [Ndate] [datetime]  NOT NULL , [Ntime] [datetime]  NOT NULL , [Nlocate] [varchar] (3) NOT NULL , [Mode] [varchar] (8) NOT NULL )
 ALTER TABLE [kqRecord] WITH NOCHECK ADD CONSTRAINT [PK_kqRecord] PRIMARY KEY  NONCLUSTERED ( [Guid] )

 INSERT [kqRecord] ( [Guid] , [RegisterNumber] , [Name] , [Ndate] , [Ntime] , [Nlocate] , [Mode] ) VALUES ( '0386620692784FD9A01E4CF43E9F8745' , '100110' , 'micropu' , '2013-02-26 00:00:00.000' , '1899-12-30 14:23:20.000' , '100' , '人脸识别' )
 INSERT [kqRecord] ( [Guid] , [RegisterNumber] , [Name] , [Ndate] , [Ntime] , [Nlocate] , [Mode] ) VALUES ( '29B9D364884D495E9CEE961928CD40AD' , '100110' , 'micropu' , '2013-02-26 00:00:00.000' , '1899-12-30 14:21:31.000' , '100' , '人脸识别' )
 INSERT [kqRecord] ( [Guid] , [RegisterNumber] , [Name] , [Ndate] , [Ntime] , [Nlocate] , [Mode] ) VALUES ( 'AA0A5CA236D543AB888A352F58075D46' , '100110' , 'micropu' , '2013-02-25 00:00:00.000' , '1899-12-30 14:25:28.000' , '100' , '人脸识别' )

--考勤图像表
 if exists (select * from sysobjects where id = OBJECT_ID('[RecordImage]') and OBJECTPROPERTY(id, 'IsUserTable') = 1) DROP TABLE [RecordImage] 
 CREATE TABLE [RecordImage] ( [RecordGUID] [varchar] (32) NOT NULL , [RecordImage] [image] NULL )
 ALTER TABLE [RecordImage] WITH NOCHECK ADD CONSTRAINT [PK_RecordImage] PRIMARY KEY  NONCLUSTERED ( [RecordGUID] )
 
 --考勤图像表数据，太长图像部份略
 
 
 INSERT [RecordImage] ( [RecordGUID] , [RecordImage] ) VALUES ( '0386620692784FD9A01E4CF43E9F8745' ,'图片数据3') 
 INSERT [RecordImage] ( [RecordGUID] , [RecordImage] ) VALUES ( '29B9D364884D495E9CEE961928CD40AD' ,'图片数据2') 
 INSERT [RecordImage] ( [RecordGUID] , [RecordImage] ) VALUES ( 'AA0A5CA236D543AB888A352F58075D46' , '图片数据1' )

--现时语句

--系统临时表
drop table ##temp 

declare @s varchar(8000)
declare @s1 varchar(8000)
set @s='select convert(varchar,Ndate,23) 日期,registerNumber 注册号,name 姓名 '
set @s1 = 'select a.日期,a.注册号,a.姓名'
select @s=@s+',max(case when rn='+ltrim(rn)+' then convert(varchar,Ntime,24) end) as 打卡'+ltrim(rn)
+',max(case when rn='+ltrim(rn)+' then Guid end) as 图像'+ltrim(rn)
	,@s1 = @s1 + ',a.打卡'+ltrim(rn) + ',(select RecordImage from [RecordImage] where recordGuid=a.图像'+ltrim(rn)+ ') as 图像'+ltrim(rn)
from (select distinct rn from (select rn=row_number()over(partition by registerNumber,ndate order by ndate,ntime),Ntime,Ndate from kqRecord where ndate>='2013-2-1')a)t


set @s=@s+' into ##temp from (select rn=row_number()over(partition by registerNumber,ndate order by ndate,ntime),* from kqrecord where ndate>=''2013-2-1''
) t group by ndate,registerNumber,name'

set @s1=@s1+' from ##temp a'
exec(@s)
exec(@s1)
-Tracy-McGrady- 2013-02-27
  • 打赏
  • 举报
 回复
少弄点无关的东西，看了就心烦。说明白点
wori 2013-02-27
  • 打赏
  • 举报
 回复 
[code=sql]select temp.姓名,temp.打卡1,(select RecordImage from RecordImage where recordGuid=temp.图像1) ig1 ,temp.打卡2,(select RecordImage from RecordImage where recordGuid=temp.图像2) ig2 from temp
现时用这句名以解决，但不知怎样改成动态的，求大家[/code]
micropu 2013-02-27
  • 打赏
  • 举报
 回复
不懂，帮你顶呀！
wori 2013-02-26
  • 打赏
  • 举报
 回复
max(case when rn='+ltrim(rn)+' then [RecordImage] end) as 图像'+ltrim(rn) 这一句是无法通过的，因为RecordImage 是image类型
wori 2013-02-26
  • 打赏
  • 举报
 回复 
消息 8117，级别 16，状态 1，第 1 行
操作数数据类型 image 对于 max 运算符无效。
消息 208，级别 16，状态 0，第 23 行
对象名 '##temp' 无效。
昵称被占用了 2013-02-26
  • 打赏
  • 举报
 回复
TRY: 
 --考勤记录表
 if exists (select * from sysobjects where id = OBJECT_ID('[kqRecord]') and OBJECTPROPERTY(id, 'IsUserTable') = 1) DROP TABLE [kqRecord] 
 CREATE TABLE [kqRecord] ( [Guid] [varchar] (32) NOT NULL , [RegisterNumber] [varchar] (20) NOT NULL , [Name] [varchar] (20) NOT NULL , [Ndate] [datetime]  NOT NULL , [Ntime] [datetime]  NOT NULL , [Nlocate] [varchar] (3) NOT NULL , [Mode] [varchar] (8) NOT NULL )
 ALTER TABLE [kqRecord] WITH NOCHECK ADD CONSTRAINT [PK_kqRecord] PRIMARY KEY  NONCLUSTERED ( [Guid] )

 INSERT [kqRecord] ( [Guid] , [RegisterNumber] , [Name] , [Ndate] , [Ntime] , [Nlocate] , [Mode] ) VALUES ( '0386620692784FD9A01E4CF43E9F8745' , '100110' , 'micropu' , '2013-02-26 00:00:00.000' , '1899-12-30 14:23:20.000' , '100' , '人脸识别' )
 INSERT [kqRecord] ( [Guid] , [RegisterNumber] , [Name] , [Ndate] , [Ntime] , [Nlocate] , [Mode] ) VALUES ( '29B9D364884D495E9CEE961928CD40AD' , '100110' , 'micropu' , '2013-02-26 00:00:00.000' , '1899-12-30 14:21:31.000' , '100' , '人脸识别' )
 INSERT [kqRecord] ( [Guid] , [RegisterNumber] , [Name] , [Ndate] , [Ntime] , [Nlocate] , [Mode] ) VALUES ( 'AA0A5CA236D543AB888A352F58075D46' , '100110' , 'micropu' , '2013-02-25 00:00:00.000' , '1899-12-30 14:25:28.000' , '100' , '人脸识别' )

--考勤图像表
 if exists (select * from sysobjects where id = OBJECT_ID('[RecordImage]') and OBJECTPROPERTY(id, 'IsUserTable') = 1) DROP TABLE [RecordImage] 
 CREATE TABLE [RecordImage] ( [RecordGUID] [varchar] (32) NOT NULL , [RecordImage] [image] NULL )
 ALTER TABLE [RecordImage] WITH NOCHECK ADD CONSTRAINT [PK_RecordImage] PRIMARY KEY  NONCLUSTERED ( [RecordGUID] )
 
 --考勤图像表数据，太长图像部份略
 
 
 INSERT [RecordImage] ( [RecordGUID] , [RecordImage] ) VALUES ( '0386620692784FD9A01E4CF43E9F8745' ,图片数据3) 
 INSERT [RecordImage] ( [RecordGUID] , [RecordImage] ) VALUES ( '29B9D364884D495E9CEE961928CD40AD' ,图片数据2) 
 INSERT [RecordImage] ( [RecordGUID] , [RecordImage] ) VALUES ( 'AA0A5CA236D543AB888A352F58075D46' , 图片数据1 )

--现时语句

--系统临时表
drop table ##temp 

declare @s varchar(8000)
set @s='select convert(varchar,Ndate,23) 日期,registerNumber 注册号,name 姓名 '
select @s=@s+',max(case when rn='+ltrim(rn)+' then convert(varchar,Ntime,24) end) as 打卡'+ltrim(rn)
+',max(case when rn='+ltrim(rn)+' then [RecordImage] end) as 图像'+ltrim(rn)
from (select distinct rn from (select rn=row_number()over(partition by registerNumber,ndate order by ndate,ntime),Ntime,Ndate from kqRecord where ndate>='2013-2-1')a)t
set @s=@s+' into ##temp from (select rn=row_number()over(partition by A.registerNumber,A.ndate order by A.ndate,A.ntime)
,A.*, B.[RecordImage]
from kqrecord A LEFT JOIN [RecordImage] B
ON A.[Guid] = B.[RecordGUID]
where A.ndate>=''2013-2-1''
) t group by ndate,registerNumber,name'
 
exec(@s)

--update ##temp 

select * from ##temp
wori 2013-02-26
  • 打赏
  • 举报
 回复
打错了，是RecordImage
wori 2013-02-26
  • 打赏
  • 举报
 回复
列数是不定确的
昵称被占用了 2013-02-26
  • 打赏
  • 举报
 回复
没发现kqimage
wori 2013-02-26
  • 打赏
  • 举报
 回复
怎样关联呢？
Vidor 2013-02-26
  • 打赏
  • 举报
 回复
屁大点问题，搞一堆无关的数据，row_number里面关联就是了，懒得写。
wori 2013-02-26
  • 打赏
  • 举报
 回复 
 
 
 --考勤记录表
 if exists (select * from sysobjects where id = OBJECT_ID('[kqRecord]') and OBJECTPROPERTY(id, 'IsUserTable') = 1) DROP TABLE [kqRecord] 
 CREATE TABLE [kqRecord] ( [Guid] [varchar] (32) NOT NULL , [RegisterNumber] [varchar] (20) NOT NULL , [Name] [varchar] (20) NOT NULL , [Ndate] [datetime]  NOT NULL , [Ntime] [datetime]  NOT NULL , [Nlocate] [varchar] (3) NOT NULL , [Mode] [varchar] (8) NOT NULL )
 ALTER TABLE [kqRecord] WITH NOCHECK ADD CONSTRAINT [PK_kqRecord] PRIMARY KEY  NONCLUSTERED ( [Guid] )

 INSERT [kqRecord] ( [Guid] , [RegisterNumber] , [Name] , [Ndate] , [Ntime] , [Nlocate] , [Mode] ) VALUES ( '0386620692784FD9A01E4CF43E9F8745' , '100110' , 'micropu' , '2013-02-26 00:00:00.000' , '1899-12-30 14:23:20.000' , '100' , '人脸识别' )
 INSERT [kqRecord] ( [Guid] , [RegisterNumber] , [Name] , [Ndate] , [Ntime] , [Nlocate] , [Mode] ) VALUES ( '29B9D364884D495E9CEE961928CD40AD' , '100110' , 'micropu' , '2013-02-26 00:00:00.000' , '1899-12-30 14:21:31.000' , '100' , '人脸识别' )
 INSERT [kqRecord] ( [Guid] , [RegisterNumber] , [Name] , [Ndate] , [Ntime] , [Nlocate] , [Mode] ) VALUES ( 'AA0A5CA236D543AB888A352F58075D46' , '100110' , 'micropu' , '2013-02-25 00:00:00.000' , '1899-12-30 14:25:28.000' , '100' , '人脸识别' )

--考勤图像表
 if exists (select * from sysobjects where id = OBJECT_ID('[RecordImage]') and OBJECTPROPERTY(id, 'IsUserTable') = 1) DROP TABLE [RecordImage] 
 CREATE TABLE [RecordImage] ( [RecordGUID] [varchar] (32) NOT NULL , [RecordImage] [image] NULL )
 ALTER TABLE [RecordImage] WITH NOCHECK ADD CONSTRAINT [PK_RecordImage] PRIMARY KEY  NONCLUSTERED ( [RecordGUID] )
 
 --考勤图像表数据，太长图像部份略
 
 
 INSERT [RecordImage] ( [RecordGUID] , [RecordImage] ) VALUES ( '0386620692784FD9A01E4CF43E9F8745' ,图片数据3) 
 INSERT [RecordImage] ( [RecordGUID] , [RecordImage] ) VALUES ( '29B9D364884D495E9CEE961928CD40AD' ,图片数据2) 
 INSERT [RecordImage] ( [RecordGUID] , [RecordImage] ) VALUES ( 'AA0A5CA236D543AB888A352F58075D46' , 图片数据1 )

--现时语句

--系统临时表
drop table ##temp 

declare @s varchar(8000)
set @s='select convert(varchar,Ndate,23) 日期,registerNumber 注册号,name 姓名 '
select @s=@s+',max(case when rn='+ltrim(rn)+' then convert(varchar,Ntime,24) end) as 打卡'+ltrim(rn)
+',max(case when rn='+ltrim(rn)+' then Guid end) as 图像'+ltrim(rn)
from (select distinct rn from (select rn=row_number()over(partition by registerNumber,ndate order by ndate,ntime),Ntime,Ndate from kqRecord where ndate>='2013-2-1')a)t
set @s=@s+' into ##temp from (select rn=row_number()over(partition by registerNumber,ndate order by ndate,ntime),* from kqrecord where ndate>=''2013-2-1''
) t group by ndate,registerNumber,name'
 
exec(@s)

--update ##temp 

select * from ##temp

--结果

日期	注册号	姓名	打卡1	图像1	打卡2	图像2
2013-02-25	100110	micropu	14:25:28	AA0A5CA236D543AB888A352F58075D46	NULL	NULL
2013-02-26	100110	micropu	14:21:31	29B9D364884D495E9CEE961928CD40AD	14:23:20	0386620692784FD9A01E4CF43E9F8745
 
 
 --想得到的结果就是将图像1、图像2 的值更新为 kqimage中对应的图像值
 
 
--
wori 2013-02-26
  • 打赏
  • 举报
 回复
求，怎样将 图像1，图像2 update成kqimage中的图像！
Vidor 2013-02-26
  • 打赏
  • 举报
 回复
请说难在何处
昵称被占用了 2013-02-26
  • 打赏
  • 举报
 回复
什么需求？？
SQL语句基础教程
SQL语句基础教程
经典sql语句大全（基础篇、提升篇、技巧篇）
经典sql语句大全（基础篇、提升篇、技巧篇）
数据库SQL基本语句（半天即可学会，轻松简单）
只要你花半天时间，就能轻松掌握SQL基本语句的使用方式，很好的参考资料，主要看文章中的例子，因为例子具有可读连续性，因此，文档描述非常简单，容易掌握，也较通俗易懂！与大家分享！与大家共享！
SQL语句集锦
该资料整合了常用的sql语句，可以在平时的工作学习中起到指导作用。
sql2000学生管理数据库
sql2000学生管理数据库 这是个备份的数据库，用于练习SQL语句，在SQL2000中恢复该数据库就可以使用里面的数据了，注意恢复时在选项里要更改一下恢复的路径，还要更改恢复后的数据库的名字，里面有三张表student,course,SC
疑难问题

22,302

社区成员

121,734

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章