还是刚才3表联合FULL JOIN查询的问题。。
你刚才最后写的SQL语句的是在指导有什么字段的情况下解决的。。如果我不知道有什么字段。指我这3个表的字段可以动态添加。。如果列名一样。。我怎么去掉重复的?
QQ:317926070,真的很感谢你。。这个问题令我很苦恼。。
你刚才最后写的SQL语句的是在指导有什么字段的情况下解决的。。如果我不知道有什么字段。指我这3个表的字段可以动态添加。。如果列名一样。。我怎么去掉重复的?
QQ:317926070,真的很感谢你。。这个问题令我很苦恼。。
http://topic.csdn.net/u/20091130/21/fb718680-98ff-4afb-98d8-cff2f8293ed5.html?24281
情况说明:现在做印刷厂的一个项目,分主料表,辅料表,其他材料表,初始每个表只有ID,NAME两个字段,可以添加料的属性,也就是在数据库里加列。。所以我不能确定他的列名是什么,当然就有可能重复我现在需要一个试图,把3个表的所有数据,查出来。FULL JOIN可以满足我这个要求,但是。。如果重复了就不行了。。因为后面还有别的处理。。不知道大家明白了么。。
USE [PrintingProject]
GO
/****** 对象: Table [dbo].[DH_A_Material] 脚本日期: 12/17/2009 15:22:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DH_A_Material](
[A_Material_Id] [uniqueidentifier] NOT NULL,
[A_Material_Name] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[a] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[b] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[c] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[d] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[e] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
CONSTRAINT [PK_DH_A_MATERIAL] PRIMARY KEY CLUSTERED
(
[A_Material_Id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]GO
SET ANSI_PADDING OFF
辅料表结构:USE [PrintingProject]
GO
/****** 对象: Table [dbo].[DH_M_Material] 脚本日期: 12/17/2009 15:24:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DH_M_Material](
[M_Material_Id] [uniqueidentifier] NOT NULL,
[M_Material_Name] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[a] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[f] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[g] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
CONSTRAINT [PK_DH_M_MATERIAL] PRIMARY KEY CLUSTERED
(
[M_Material_Id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]GO
SET ANSI_PADDING OFF其它材料表结构:USE [PrintingProject]
GO
/****** 对象: Table [dbo].[DH_O_Material] 脚本日期: 12/17/2009 15:25:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DH_O_Material](
[O_Material_Id] [uniqueidentifier] NOT NULL,
[O_Material_Name] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[b] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[c] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[h] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[i] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
CONSTRAINT [PK_DH_O_MATERIAL] PRIMARY KEY CLUSTERED
(
[O_Material_Id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]GO
SET ANSI_PADDING OFF
除了各个ID和NAME都是动态生成字段。。
create table t2(a nvarchar(10),e nvarchar(10),f nvarchar(10),id int)
create table t3(b nvarchar(10),c nvarchar(10),g nvarchar(10),h nvarchar(10), id int)insert t1
select '1','2','3','4',0insert t2
select '5','6','7',0 insert t3
select '8','9','10','0',0declare @id int
set @id=0update t1 set @id=@id+1, id=@id
update t2 set @id=@id+1, id=@id
update t3 set @id=@id+1, id=@idselect t.name as col,object_name(c.id) as tableName
into #
from (
select name
from syscolumns
where object_name(id) in ('t1','t2','t3') and name<>'id'
group by name
having count(*)>1
) t join syscolumns as c on c.name=t.name
where object_name(c.id) in ('t1','t2','t3')
declare @str varchar(8000),@grp int, @str2 varchar(8000),@col varchar(255),@cols varchar(1000)
set @str2=''
set @grp=1
set @cols=''select @cols=@cols+','+name
from (
select name
from syscolumns
where object_name(id) in ('t1','t2','t3')
group by name
having count(*)=1
) t
select *,grp=(select count(distinct col) from # where col<=t.col)
into #2
from # as twhile exists(select * from #2 where grp=@grp)
begin
set @str=''
select @str=@str+','+tableName+'.'+col,
@col=col
from #2
where grp=@grp set @str=',COALESCE('+stuff(@str,1,1,'')+') as '+@col
set @str2=@str2+@str
set @grp=@grp+1
end
set @str2=stuff(@str2,1,1,'')exec('select '+ @str2+@cols+' from t1 full join t2 on t1.id=t2.id full join t3 on t1.id=t3.id')drop table t1,t2,t3,#,#2
/*
a b c d e f g h
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
1 2 3 4 NULL NULL NULL NULL
5 NULL NULL NULL 6 7 NULL NULL
NULL 8 9 NULL NULL NULL 10 0
*/