sql 怎么取子表中其中一行的一个字段

[ajdkjalj]

比如主表：

id name
1 张三


子表：
cid id age
1 1 10
2 1 10

然后我想得到
id name age
1 张三 10

这里子表的age在id相同时，age是肯定相同的，不用考虑不同，只是做个例子

[吉普赛的歌]

USE tempdb
GO
IF OBJECT_ID('main') IS NOT NULL DROP TABLE main
IF OBJECT_ID('sub') IS NOT NULL DROP TABLE sub
GO
CREATE TABLE main (id INT, [name] NVARCHAR(10))
CREATE TABLE sub (cid INT, id INT, age int)
INSERT INTO main(id,name) VALUES(1,N'张三')
INSERT INTO sub(cid,id,age) VALUES(1,1,10)
INSERT INTO sub(cid,id,age) VALUES(2,1,10)

SELECT 
*
,(SELECT TOP 1 age FROM sub WHERE sub.id=main.id) AS age 
FROM main

[吉普赛的歌]

/*
id	name	age
1	张三	10
*/

[二月十六]

--测试数据
if not object_id(N'Tempdb..#T1') is null
	drop table #T1
Go
Create table #T1([id] int,[name] nvarchar(22))
Insert #T1
select 1,N'张三'
GO
if not object_id(N'Tempdb..#T2') is null
	drop table #T2
Go
Create table #T2([cid] int,[id] int,[age] int)
Insert #T2
select 1,1,10 union all
select 2,1,10
Go
--测试数据结束
SELECT  #T1.* ,
        age
FROM    #T1
        JOIN ( SELECT   * ,
                        ROW_NUMBER() OVER ( PARTITION BY id ORDER BY cid ) rn
               FROM     #T2
             ) t2 ON t2.id = #T1.id
                     AND rn = 1

[卖水果的net]

-- 借 1# 数据，凑个人数

USE tempdb
GO
IF OBJECT_ID('main') IS NOT NULL DROP TABLE main
IF OBJECT_ID('sub') IS NOT NULL DROP TABLE sub
GO
CREATE TABLE main (id INT, [name] NVARCHAR(10))
CREATE TABLE sub (cid INT, id INT, age int)
INSERT INTO main(id,name) VALUES(1,N'张三')
INSERT INTO sub(cid,id,age) VALUES(1,1,10)
INSERT INTO sub(cid,id,age) VALUES(2,1,10)

SELECT *
FROM main
cross apply (SELECT TOP 1 age FROM sub WHERE sub.id=main.id) x
go
drop table main, sub
go

[xity]

SELECT [主表].[dbo].[id],[主表].[dbo].[name],[子表].[dbo].[age] FROM [主表] left join [子表] on [主表].[id]=[子表].[id] where 条件

[xity]

你把表名改过来和加上条件限制就可以了