Question

需要一个SQL查询语句多表多字段查询

表1 数据如下
老公 老婆
张某 李某
高某 赵某
王某 吕某

表2
姓名 年龄 月薪
张某 27 500
高某 27 700
王某 25 600
李某 26 800
赵某 25 900
吕某 28 800

查询得到的数据需要如下
老公 lg年龄 lg月薪 老婆 lp年龄 lp月薪 合计年龄 合计工资
张某 27 500 李某 26 800 53 1300

Answer 1

select distinct top 1 husband,a.age as lg年龄,a.money as lg月薪,
wife,b.age as lp年龄,b.money as lp月薪,
a.age+b.age as 合计年龄,a.money+b.money 合计工资
from
FamilyInfo,Detail as a,Detail as b
where a.name='王某' and b.name='赵某'

----------------------
USE [test_123]
GO
/****** 对象: Table [dbo].[Detail] 脚本日期: 09/16/2010 19:07:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Detail](
[ID] [int] NOT NULL,
[name] [nchar](10) COLLATE Chinese_PRC_CI_AS NOT NULL,
[age] [int] NOT NULL,
[money] [int] NOT NULL
) ON [PRIMARY]
-------------------------------
USE [test_123]
GO
/****** 对象: Table [dbo].[FamilyInfo] 脚本日期: 09/16/2010 19:07:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[FamilyInfo](
[ID] [int] NOT NULL,
[husband] [nchar](10) COLLATE Chinese_PRC_CI_AS NOT NULL,
[wife] [nchar](10) COLLATE Chinese_PRC_CI_AS NOT NULL
) ON [PRIMARY]

Answer 2

select 老公,a.年龄 as lg年龄,a.月薪 as lg月薪,
老婆,b.年龄 as lp年龄,b.月薪 as lp月薪,
a.年龄+b.年龄 as 合计年龄,a.月薪+b.月薪 合计工资
from
表1 ,表2 as a ,表2 as b
where a.姓名=老公 and b.姓名=老婆

Answer 3

select 表1.*,表2.* from 表1,表2 where 表1.字段A='' And 表2.字段A=''