22,210
社区成员
发帖
与我相关
我的任务
分享
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'
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
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
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
数据多的左连接少的 就是你要的数据
select [字段列表]
from 表A a
inner join
(select AID,[其他字段列表] from 表B
union all
select AID,[其他字段列表] from 表C) d on a.ID=d.AID