update table3
set values=(
select value
from table2,t1,t2
where table2.guid=t1.guid
and table2.guid = t2.guid
table3.id = table2.id
)table1没用?
set values=(
select value
from table2,t1,t2
where table2.guid=t1.guid
and table2.guid = t2.guid
table3.id = table2.id
)table1没用?
解决方案 »
- 日志文件里大量 用户 'sa' 登录失败。 [客户端: IP]
- select @e=@@error怎么没有了?求救
- sql最快能在一个小时写入多少条记录呢?
- ·········又是交叉表的问题 ·······
- 求两张表交集(补集)的问题
- 请教一个关于数据库性能的问题,谢谢了
- 突然傻了,怎么会这样?
- Reporting service中怎样实现在一张图表上表示两种类型的图表.
- 请问,触发器定义为INSERT, UPDATE, DELETE后,如何用程序判别本次触发的是类型是insert还是update还是delete???
- 怎样取得ADO中的错误代码???急急急!!!
- 数据库(或库中的表)可以加密吗?
- 高分求助,关于视图的奇怪问题
declare @table1 table (tId1 varchar(10),tId2 varchar(10),tName varchar(10))
insert @table1
select '1','1','@t1' union
select '2','x','@t1' union
select '2','x','@t2' declare @table2 table (tId1 varchar(10),tId2 varchar(10),[id] varchar(10),GUID varchar(20))
insert @table2
select '1','1','0001','{a guid...}' union
select '2','x','0002','{b guid...}' declare @table3 table ([id] varchar(10) ,value varchar(10))
insert @table3
select '0001','' union
select '0002','' declare @t1 table (GUID varchar(20),value varchar(10))
insert @t1
select '{a guid...}','12345' declare @t2 table (GUID varchar(20),value varchar(10))
insert @t2
select '{b guid...}','67890'
--更新
update a set value=isnull(b.value,a.value) from @table3 a
inner join (
select a.[id],
value=case when d.tName='@t1' then b.value
when d.tName='@t2' then c.value
end
from @table2 a
left join @t1 b on a.GUID=b.GUID
left join @t2 c on a.GUID=c.GUID
left join @table1 d on a.tId1=d.tId1 and a.tId2=d.tId2
) b on a.[id]=b.[id]select * from @table3/*结果
id value
---------- ----------
0001 12345
0002 67890
*/
--注:如果t1与t2没有相同的GUID,则table1就没有意义了,直接用下面的语句就可以了
update a set value=isnull(b.value,a.value) from @table3 a
inner join (
select a.[id],value=isnull(b.value,c.value) from @table2 a
left join @t1 b on a.GUID=b.GUID
left join @t2 c on a.GUID=c.GUID
) b on a.[id]=b.[id]
标题:两表通过表字段内容确定列名后连接数据
作者:爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)
时间:2008-11-06
地点:广东深圳
描述:table1
tid tname
----------- -----
1 zhao
2 qian
3 sun
...table2
tid zhao qian sun
----------- ---- ---- ----
1 a b c
2 d e f
3 g h i
... 2个表通过tId连接,需要查询的结果是∶(就是说table1的值指名了table2的列名,而且table2有很多列(不能规定长度))
tid tname tvalue
----------- ----- ------
1 zhao a
2 qian e
3 sun i
...
*/--一、sql server 2000
create table [table1]([tid] int,[tname] varchar(4))
insert [table1]
select 1,'zhao' union all
select 2,'qian' union all
select 3,'sun'
create table [table2]([tid] int,[zhao] varchar(1),[qian] varchar(1),[sun] varchar(1))
insert [table2]
select 1,'a','b','c' union all
select 2,'d','e','f' union all
select 3,'g','h','i'--1、静态写法(指tname的值固定或确定,只有zhao,qian,sun三个)
select a.tid , tname , tvalue = case a.tname when 'qian' then b.qian when 'sun' then b.sun when 'zhao' then b.zhao end
from table1 a join table2 b
on a.tid = b.tid--2、动态写法(指tname的值不固定或不确定,不止zhao,qian,sun三个)
declare @sql nvarchar(1000)
set @sql = 'select a.tid , tname , tvalue = case a.tname '
select @sql = @sql + ' when ''' + tname + ''' then b.' + tname
from(select tname from table1) t
set @sql = @sql + ' end from table1 a join table2 b on a.tid=b.tid'
exec(@sql)drop table table1 , table2
--二、sql server 2005
create table [table1]([tid] int,[tname] varchar(4))
insert [table1]
select 1,'zhao' union all
select 2,'qian' union all
select 3,'sun'
create table [table2]([tid] int,[zhao] varchar(1),[qian] varchar(1),[sun] varchar(1))
insert [table2]
select 1,'a','b','c' union all
select 2,'d','e','f' union all
select 3,'g','h','i'declare @s nvarchar(4000)
select @s = isnull(@s + ',' , '') + quotename([tname]) from [table1]
exec('with c as (Select * from [table2] unpivot (tvalue for tname in(' + @s + ')) t3) select c.* from c join [table1] on c.tid = [table1].tid and c.tname = [table1].tname ')drop table table1 , table2
2, x, t2 2,x的对应有t1,t2两个表,为什么0002, 67890 而不是 0002, 12345 ?
DROP TABLE table1
IF object_id('table2','u') IS NOT NULL
DROP TABLE table2
IF object_id('table3','u') IS NOT NULL
DROP TABLE table3
IF object_id('t1','u') IS NOT NULL
DROP TABLE t1
IF object_id('t2','u') IS NOT NULL
DROP TABLE t2
GOCREATE TABLE table1(tid1 INT,tid2 VARCHAR(10),tName VARCHAR(20))
INSERT table1 SELECT 1, '1', 't1'
UNION ALL SELECT 2, 'x', 't1'
UNION ALL SELECT 2, 'x', 't2' CREATE TABLE table2(tid1 INT,tid2 VARCHAR(10),id VARCHAR(10),guid INT) --guid我改成int,测试数据懒得用guid去匹配
INSERT table2 SELECT 1, '1', '0001', 1
UNION ALL SELECT 2, 'x', '0002', 11CREATE TABLE table3(id VARCHAR(10),value INT)
INSERT table3 SELECT '0001',NULL
UNION ALL SELECT '0002',NULLCREATE TABLE t1(guid INT,value INT)
INSERT t1 SELECT 1,12345CREATE TABLE t2(guid INT,value INT)
INSERT t2 SELECT 11,67890
GO
DECLARE @s VARCHAR(MAX),@s1 VARCHAR(1000),@s3 VARCHAR(1000)
SELECT @s=' FROM table1 a , table2 b ,table3 c ',
@s1=' WHERE b.id=c.id AND a.tid1=b.tid1 AND a.tid2=b.tid2 AND b.guid=CASE ',
@s3=' CASE '
;WITH fc AS
(
SELECT CHAR(99 + ROW_NUMBER() OVER(ORDER BY GETDATE())) a,tName FROM table1 GROUP BY tName
)SELECT @s=@s + ',' + tName + ' ' + a,
@s1=@s1 + ' WHEN a.tName =''' + tName + ''' THEN ' + a + '.guid',
@s3= @s3 + ' WHEN a.tName =''' + tName + ''' THEN ' + a + '.value'
FROM fcEXEC(' UPDATE c SET c.value = ' + @s3 + ' END ' + @s + @s1 + ' end')
SELECT * FROM table3/*
0001 12345
0002 67890
*/GO
--测试数据
create table #table1 (tId1 varchar(10),tId2 varchar(10),tName varchar(10))
insert #table1
select '1','1','#t1' union
select '2','x','#t1' union
select '2','x','#t2' create table #table2 (tId1 varchar(10),tId2 varchar(10),[id] varchar(10),GUID varchar(20))
insert #table2
select '1','1','0001','{a guid...}' union
select '2','x','0002','{b guid...}' create table #table3 ([id] varchar(10) ,value varchar(10))
insert #table3
select '0001','' union
select '0002','' create table #t1 (GUID varchar(20),value varchar(10))
insert #t1
select '{a guid...}','12345' create table #t2 (GUID varchar(20),value varchar(10))
insert #t2
select '{b guid...}','67890'
--更新
declare @sql1 varchar(4000),@sql2 varchar(4000),@i int,@表别名 varchar(10)
select @sql1='update a set value=isnull(b.value,a.value) from #table3 a
inner join ( select a.[id],value=case ',@sql2='',@i=1select @表别名='t'+cast(@i as varchar(10)),
@sql1=@sql1+'when d.tName='''+tName+''' then '+@表别名+'.value ',
@sql2=@sql2+' left join '+tName+' '+@表别名+' on a.GUID='+@表别名+'.GUID',
@i=@i+1
from (select distinct tName from #table1) aexec( @sql1+' end from #table2 a '+@sql2+' left join #table1 d on a.tId1=d.tId1 and a.tId2=d.tId2
) b on a.[id]=b.[id]')select * from #table3
/*
id value
---------- ----------
0001 12345
0002 67890
*/
drop table #table1,#table2, #table3,#t1,#t2
直接用表名的话,把
+ ''' THEN ' + a + '.value' 这里的a 改为 tName就可以了。当然,还有select部分。end那里错误,你注意下,是否你把空格敲掉了。 把语句 print 出来看看,有没有什么问题。 如果print了看不出错误,就粘出来,运行print出的语句,这样排错比较直观.
关键在这儿,如果唯一不如把T1,t2... tn做成视图,和table2关联去更新table3 了
DROP TABLE table1
IF object_id('table2','u') IS NOT NULL
DROP TABLE table2
IF object_id('table3','u') IS NOT NULL
DROP TABLE table3
IF object_id('t1','u') IS NOT NULL
DROP TABLE t1
IF object_id('t2','u') IS NOT NULL
DROP TABLE t2
GOCREATE TABLE table1(tid1 INT,tid2 VARCHAR(10),tName VARCHAR(20))
INSERT table1 SELECT 1, '1', 't1'
UNION ALL SELECT 2, 'x', 't1'
UNION ALL SELECT 2, 'x', 't2' CREATE TABLE table2(tid1 INT,tid2 VARCHAR(10),id VARCHAR(10),guid INT) --guid我改成int,测试数据懒得用guid去匹配
INSERT table2 SELECT 1, '1', '0001', 1
UNION ALL SELECT 2, 'x', '0002', 11CREATE TABLE table3(id VARCHAR(10),value INT)
INSERT table3 SELECT '0001',NULL
UNION ALL SELECT '0002',NULLCREATE TABLE t1(guid INT,value INT)
INSERT t1 SELECT 1,12345CREATE TABLE t2(guid INT,value INT)
INSERT t2 SELECT 11,67890
GO
DECLARE @s VARCHAR(MAX),@s1 VARCHAR(1000),@s3 VARCHAR(1000)
SELECT @s=' FROM table1 a , table2 b ,table3 c ',
@s1=' WHERE b.id=c.id AND a.tid1=b.tid1 AND a.tid2=b.tid2 AND b.guid=CASE ',
@s3=' CASE 'SELECT @s=@s + ',' + tName + ' ' + tName,
@s1=@s1 + ' WHEN a.tName =''' + tName + ''' THEN ' + tName + '.guid',
@s3= @s3 + ' WHEN a.tName =''' + tName + ''' THEN ' + tName + '.value'
FROM table1 GROUP BY tName--PRINT ' UPDATE c SET c.value = ' + @s3 + ' END ' + @s + @s1 + ' end'
EXEC(' UPDATE c SET c.value = ' + @s3 + ' END ' + @s + @s1 + ' end')
SELECT * FROM table3/*
0001 12345
0002 67890
*/GO
t1, guid, value
t2, guid2, value2
也就是说下面的两个值(guid, value)从这个表里取,请问如何改动?多谢! + '.guid',
+ '.value'