我猜想 , 表 B 和表 C 的 ID 都是重复的的。 比如都是 1 ; 这样出来的结果,就是 2 * 3 = 6 行。
SELECT A.ID,B.ID,C.ID --这边各表的字段你随便加 FROM (SELECT ROW_NUMBER()OVER(GETDATE())RN,C.ID FROM C)T3 LEFT JOIN(SELECT ROW_NUMBER()OVER(GETDATE())RN,B.ID FROM B)T2 ON B.RN=C.RN LEFT JOIN(SELECT ROW_NUMBER()OVER(GETDATE())RN,A.ID FROM A)T1 ON A.RN=C.RN 数据多的左连接少的 就是你要的数据
我没用你的这个序号 我用的是 行号 ROW_NUMBER()OVER(ORDER BY GETDATE()) 上面写错了要加个ORDER BY你需要用你的序号顺序合并吗? 要的话,参考如下 SELECT A.序号,B.序号,C.序号 FROM (SELECT ROW_NUMBER()OVER(ORDER BY C.序号)RN,C.* FROM C)T3 LEFT JOIN(SELECT ROW_NUMBER()OVER(ORDER BY B.序号)RN,B.* FROM B)T2 ON B.RN=C.RN LEFT JOIN(SELECT ROW_NUMBER()OVER(ORDER BY A.序号)RN,A.* FROM A)T1 ON A.RN=C.RN
create table TA(id int) insert into ta select 1 create table TB(Aid int,val varchar(30)) insert into TB select 1,'甲' union all select 1,'已' create table TC(Aid int,val varchar(30)) insert into TC select 1,'A' union all select 1,'B' union all select 1,'C' select c.id,b.val,a.val from (select *,row_number() over(partition by Aid order by getdate()) as rn from TC) a left join (select *,row_number() over(partition by Aid order by getdate()) as rn from TB) b on a.Aid=b.Aid and a.rn=b.rn left join (select *,row_number() over(order by id) as rn from TA) c on a.aid=c.id and a.rn=c.rn
抱歉。这是初始化数据脚本。以及我的SQL。 USE [tempDB1] GO /****** Object: Table [dbo].[A001] Script Date: 10/24/2014 14:30:22 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[A001]( [Id] [int] NOT NULL, [W9001] [varchar](32) NULL, [A0101] [varchar](32) NULL, [U1156] [varchar](64) NULL, [C0107] [varchar](32) NULL, [ChildDepart] [varchar](64) NULL, [U0001] [varchar](32) NULL, [A0177] [varchar](32) NULL, [A0107] [varchar](16) NULL, [A0111] [datetime] NULL, [U0002] [int] NULL, CONSTRAINT [PK_A001] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO INSERT [dbo].[A001] ([Id], [W9001], [A0101], [U1156], [C0107], [ChildDepart], [U0001], [A0177], [A0107], [A0111], [U0002]) VALUES (1089, N'200001', N'张三', N'rylx00030008', N'18', N'', N'JMY', N'111111111111111', N'2', CAST(0x0000545100000000 AS DateTime), 25) INSERT [dbo].[A001] ([Id], [W9001], [A0101], [U1156], [C0107], [ChildDepart], [U0001], [A0177], [A0107], [A0111], [U0002]) VALUES (1230, N'200002', N'李四', N'rylx00020000', N'18', N'', N'THX', N'111111111111111', N'2', CAST(0x0000779E00000000 AS DateTime), 25) /****** Object: Table [dbo].[A002] Script Date: 10/24/2014 14:30:22 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[A002]( [Id] [int] NOT NULL, [A001_id] [int] NOT NULL, [A0405] [varchar](32) NULL, [A0410] [varchar](32) NULL, [A0415] [datetime] NULL, [A0420] [varchar](32) NULL, [A0425] [varchar](32) NULL, [A0430] [datetime] NULL, [A0435] [varchar](64) NULL, [A0440] [varchar](32) NULL, [A0445] [datetime] NULL, [A0450] [varchar](32) NULL, [A0455] [varchar](128) NULL, [C0206] [varchar](128) NULL, [C0207] [varchar](128) NULL, [Y0201] [varchar](32) NULL, [Y0202] [varchar](128) NULL, CONSTRAINT [PK_A002] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO INSERT [dbo].[A002] ([Id], [A001_id], [A0405], [A0410], [A0415], [A0420], [A0425], [A0430], [A0435], [A0440], [A0445], [A0450], [A0455], [C0206], [C0207], [Y0201], [Y0202]) VALUES (865, 1230, N'41', N'100701', CAST(0x00008E3200000000 AS DateTime), N'99', N'7', CAST(0x000093A900000000 AS DateTime), N'上海第二医科大学附属卫生学校', NULL, NULL, NULL, NULL, NULL, N'', N'1', NULL) INSERT [dbo].[A002] ([Id], [A001_id], [A0405], [A0410], [A0415], [A0420], [A0425], [A0430], [A0435], [A0440], [A0445], [A0450], [A0455], [C0206], [C0207], [Y0201], [Y0202]) VALUES (866, 1230, N'31', N'100701', CAST(0x0000977700000000 AS DateTime), N'99', N'4', CAST(0x00009ACC00000000 AS DateTime), N'上海交通大学网络教育学院医学院分院', NULL, NULL, NULL, NULL, NULL, N'', N'2', NULL) INSERT [dbo].[A002] ([Id], [A001_id], [A0405], [A0410], [A0415], [A0420], [A0425], [A0430], [A0435], [A0440], [A0445], [A0450], [A0455], [C0206], [C0207], [Y0201], [Y0202]) VALUES (867, 1230, N'21', N'100701', CAST(0x00009BBF00000000 AS DateTime), N'03', N'5', CAST(0x0000A2A600000000 AS DateTime), N'复旦大学继续教育学院', NULL, NULL, NULL, NULL, NULL, N'', N'2', NULL) INSERT [dbo].[A002] ([Id], [A001_id], [A0405], [A0410], [A0415], [A0420], [A0425], [A0430], [A0435], [A0440], [A0445], [A0450], [A0455], [C0206], [C0207], [Y0201], [Y0202]) VALUES (6277, 1089, N'60', N'010199', CAST(0x0000A3D300000000 AS DateTime), N'06', N'2', CAST(0x0000A3D200000000 AS DateTime), NULL, N'390', NULL, NULL, NULL, NULL, NULL, NULL, NULL) INSERT [dbo].[A002] ([Id], [A001_id], [A0405], [A0410], [A0415], [A0420], [A0425], [A0430], [A0435], [A0440], [A0445], [A0450], [A0455], [C0206], [C0207], [Y0201], [Y0202]) VALUES (6278, 1089, N'60', N'010201', CAST(0x0000A3CB00000000 AS DateTime), N'07', N'3', CAST(0x0000A3CB00000000 AS DateTime), NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL) /****** Object: Table [dbo].[A003] Script Date: 10/24/2014 14:30:22 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[A003]( [Id] [int] NOT NULL, [A001_id] [int] NOT NULL, [A3405] [varchar](32) NULL, [A3410] [varchar](32) NULL, [E3401] [varchar](32) NULL, CONSTRAINT [PK_A003] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO INSERT [dbo].[A003] ([Id], [A001_id], [A3405], [A3410], [E3401]) VALUES (215, 1089, N'EN', N'1', N'2') INSERT [dbo].[A003] ([Id], [A001_id], [A3405], [A3410], [E3401]) VALUES (216, 1089, N'EL', N'1', N'3') INSERT [dbo].[A003] ([Id], [A001_id], [A3405], [A3410], [E3401]) VALUES (217, 1089, N'EN', N'1', N'1') /*查询*/ SELECT * FROM tempDB1.dbo.A001 a JOIN dbo.A002 ON a.ID = A002.a001_ID JOIN dbo.A003 ON a.ID = A003.a001_ID WHERE w9001 = '200001'
from 表A a
inner join
(select AID,[其他字段列表] from 表B
union all
select AID,[其他字段列表] from 表C) d on a.ID=d.AID
B表和C表的结构是不一样的
消息 205,级别 16,状态 1,第 1 行
使用 UNION、INTERSECT 或 EXCEPT 运算符合并的所有查询必须在其目标列表中有相同数目的表达式。
B有数据为
1 甲
1 乙
C有数据为
1 一
1 二
1 三
照你的意思,最后的数据长什么样
NULL 已 二
NULL NULL 三1 甲 一
NULL 已 二
NULL NULL 三
这样出来的结果,就是 2 * 3 = 6 行。
A.ID,B.ID,C.ID
--这边各表的字段你随便加
FROM
(SELECT ROW_NUMBER()OVER(GETDATE())RN,C.ID FROM C)T3
LEFT JOIN(SELECT ROW_NUMBER()OVER(GETDATE())RN,B.ID FROM B)T2 ON B.RN=C.RN
LEFT JOIN(SELECT ROW_NUMBER()OVER(GETDATE())RN,A.ID FROM A)T1 ON A.RN=C.RN
数据多的左连接少的 就是你要的数据
我用的是 行号
ROW_NUMBER()OVER(ORDER BY GETDATE())
上面写错了要加个ORDER BY你需要用你的序号顺序合并吗?
要的话,参考如下
SELECT
A.序号,B.序号,C.序号
FROM
(SELECT ROW_NUMBER()OVER(ORDER BY C.序号)RN,C.* FROM C)T3
LEFT JOIN(SELECT ROW_NUMBER()OVER(ORDER BY B.序号)RN,B.* FROM B)T2 ON B.RN=C.RN
LEFT JOIN(SELECT ROW_NUMBER()OVER(ORDER BY A.序号)RN,A.* FROM A)T1 ON A.RN=C.RN
insert into ta
select 1
create table TB(Aid int,val varchar(30))
insert into TB
select 1,'甲' union all
select 1,'已'
create table TC(Aid int,val varchar(30))
insert into TC
select 1,'A' union all
select 1,'B' union all
select 1,'C' select c.id,b.val,a.val from
(select *,row_number() over(partition by Aid order by getdate()) as rn from TC) a
left join
(select *,row_number() over(partition by Aid order by getdate()) as rn from TB) b
on a.Aid=b.Aid and a.rn=b.rn
left join
(select *,row_number() over(order by id) as rn from TA) c
on a.aid=c.id and a.rn=c.rn
抱歉。这是初始化数据脚本。以及我的SQL。
USE [tempDB1]
GO
/****** Object: Table [dbo].[A001] Script Date: 10/24/2014 14:30:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[A001](
[Id] [int] NOT NULL,
[W9001] [varchar](32) NULL,
[A0101] [varchar](32) NULL,
[U1156] [varchar](64) NULL,
[C0107] [varchar](32) NULL,
[ChildDepart] [varchar](64) NULL,
[U0001] [varchar](32) NULL,
[A0177] [varchar](32) NULL,
[A0107] [varchar](16) NULL,
[A0111] [datetime] NULL,
[U0002] [int] NULL,
CONSTRAINT [PK_A001] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[A001] ([Id], [W9001], [A0101], [U1156], [C0107], [ChildDepart], [U0001], [A0177], [A0107], [A0111], [U0002]) VALUES (1089, N'200001', N'张三', N'rylx00030008', N'18', N'', N'JMY', N'111111111111111', N'2', CAST(0x0000545100000000 AS DateTime), 25)
INSERT [dbo].[A001] ([Id], [W9001], [A0101], [U1156], [C0107], [ChildDepart], [U0001], [A0177], [A0107], [A0111], [U0002]) VALUES (1230, N'200002', N'李四', N'rylx00020000', N'18', N'', N'THX', N'111111111111111', N'2', CAST(0x0000779E00000000 AS DateTime), 25)
/****** Object: Table [dbo].[A002] Script Date: 10/24/2014 14:30:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[A002](
[Id] [int] NOT NULL,
[A001_id] [int] NOT NULL,
[A0405] [varchar](32) NULL,
[A0410] [varchar](32) NULL,
[A0415] [datetime] NULL,
[A0420] [varchar](32) NULL,
[A0425] [varchar](32) NULL,
[A0430] [datetime] NULL,
[A0435] [varchar](64) NULL,
[A0440] [varchar](32) NULL,
[A0445] [datetime] NULL,
[A0450] [varchar](32) NULL,
[A0455] [varchar](128) NULL,
[C0206] [varchar](128) NULL,
[C0207] [varchar](128) NULL,
[Y0201] [varchar](32) NULL,
[Y0202] [varchar](128) NULL,
CONSTRAINT [PK_A002] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[A002] ([Id], [A001_id], [A0405], [A0410], [A0415], [A0420], [A0425], [A0430], [A0435], [A0440], [A0445], [A0450], [A0455], [C0206], [C0207], [Y0201], [Y0202]) VALUES (865, 1230, N'41', N'100701', CAST(0x00008E3200000000 AS DateTime), N'99', N'7', CAST(0x000093A900000000 AS DateTime), N'上海第二医科大学附属卫生学校', NULL, NULL, NULL, NULL, NULL, N'', N'1', NULL)
INSERT [dbo].[A002] ([Id], [A001_id], [A0405], [A0410], [A0415], [A0420], [A0425], [A0430], [A0435], [A0440], [A0445], [A0450], [A0455], [C0206], [C0207], [Y0201], [Y0202]) VALUES (866, 1230, N'31', N'100701', CAST(0x0000977700000000 AS DateTime), N'99', N'4', CAST(0x00009ACC00000000 AS DateTime), N'上海交通大学网络教育学院医学院分院', NULL, NULL, NULL, NULL, NULL, N'', N'2', NULL)
INSERT [dbo].[A002] ([Id], [A001_id], [A0405], [A0410], [A0415], [A0420], [A0425], [A0430], [A0435], [A0440], [A0445], [A0450], [A0455], [C0206], [C0207], [Y0201], [Y0202]) VALUES (867, 1230, N'21', N'100701', CAST(0x00009BBF00000000 AS DateTime), N'03', N'5', CAST(0x0000A2A600000000 AS DateTime), N'复旦大学继续教育学院', NULL, NULL, NULL, NULL, NULL, N'', N'2', NULL)
INSERT [dbo].[A002] ([Id], [A001_id], [A0405], [A0410], [A0415], [A0420], [A0425], [A0430], [A0435], [A0440], [A0445], [A0450], [A0455], [C0206], [C0207], [Y0201], [Y0202]) VALUES (6277, 1089, N'60', N'010199', CAST(0x0000A3D300000000 AS DateTime), N'06', N'2', CAST(0x0000A3D200000000 AS DateTime), NULL, N'390', NULL, NULL, NULL, NULL, NULL, NULL, NULL)
INSERT [dbo].[A002] ([Id], [A001_id], [A0405], [A0410], [A0415], [A0420], [A0425], [A0430], [A0435], [A0440], [A0445], [A0450], [A0455], [C0206], [C0207], [Y0201], [Y0202]) VALUES (6278, 1089, N'60', N'010201', CAST(0x0000A3CB00000000 AS DateTime), N'07', N'3', CAST(0x0000A3CB00000000 AS DateTime), NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)
/****** Object: Table [dbo].[A003] Script Date: 10/24/2014 14:30:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[A003](
[Id] [int] NOT NULL,
[A001_id] [int] NOT NULL,
[A3405] [varchar](32) NULL,
[A3410] [varchar](32) NULL,
[E3401] [varchar](32) NULL,
CONSTRAINT [PK_A003] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[A003] ([Id], [A001_id], [A3405], [A3410], [E3401]) VALUES (215, 1089, N'EN', N'1', N'2')
INSERT [dbo].[A003] ([Id], [A001_id], [A3405], [A3410], [E3401]) VALUES (216, 1089, N'EL', N'1', N'3')
INSERT [dbo].[A003] ([Id], [A001_id], [A3405], [A3410], [E3401]) VALUES (217, 1089, N'EN', N'1', N'1')
/*查询*/
SELECT *
FROM tempDB1.dbo.A001 a
JOIN dbo.A002 ON a.ID = A002.a001_ID
JOIN dbo.A003 ON a.ID = A003.a001_ID
WHERE w9001 = '200001'