代码之家 › 专栏 › 技术社区 › Neal Stublen

只查询每个主记录的第一个明细记录

linq-to-sql sql

Neal Stublen · 技术社区 · 15 年前

如果我有以下主从关系:

owner_tbl     auto_tbl
---------     --------
owner    ---> owner
              auto
              year

我有以下表格数据:

owner_tbl     auto_tbl
---------     --------
john          john, corvette, 1968
              john, prius, 2008
james         james, f-150, 2004
              james, cadillac, 2002
              james, accord, 2009
jeff          jeff, tesla, 2010
              jeff, hyundai, 1996

现在,我要执行一个返回以下结果的查询:

john, corvette, 1968
jeff, hyundai, 1996
james, cadillac, 2002

查询应该连接这两个表,并对“年”字段中的所有记录进行排序,但只返回每个主记录的第一条明细记录。我知道如何连接表并对“year”字段进行排序,但不清楚如何(或是否)只能检索每个所有者的第一个连接记录。

三个相关问题:

我可以使用linq to sql执行这种查询吗?
我可以使用t-sql执行查询吗?
考虑到可能的复杂性,最好为查询创建一个存储过程吗?

6 回复 | 直到 6 年前

Adriaan Stander 15 年前

使用 SQL Server 2005+ 你可以试试(完整的例子)

DECLARE @owner_tbl TABLE(
        [owner] VARCHAR(50)
)

DECLARE @auto_tbl TABLE(
        [owner] VARCHAR(50),
        [auto] VARCHAR(50),
        [year]VARCHAR(4)
)

INSERT INTO @owner_tbl SELECT 'john'
INSERT INTO @owner_tbl SELECT 'james'
INSERT INTO @owner_tbl SELECT 'jeff'        

INSERT INTO @auto_tbl SELECT 'john','corvette','1968'
INSERT INTO @auto_tbl SELECT 'john','prius','2008'
INSERT INTO @auto_tbl SELECT 'james','f-150','2004'
INSERT INTO @auto_tbl SELECT 'james','cadillac','2002'
INSERT INTO @auto_tbl SELECT 'james','accord','2009'
INSERT INTO @auto_tbl SELECT 'jeff','tesla','2010'
INSERT INTO @auto_tbl SELECT 'jeff','hyundai','1996'

;WITH Autos AS(
        SELECT  *,
                ROW_NUMBER() OVER(PARTITION BY a.owner ORDER BY a.year) ROWID
        FROM    @auto_tbl a
)
SELECT  *
FROM    Autos
WHERE   ROWID = 1
ORDER BY owner

Gary W 15 年前

可以使用交叉应用:

SELECT Owner, X.Car,X.Year
FROM Owener_tbl O
CROSS APPLY(
SELECT TOP 1 * FROM Auto_tbl WHERE Owner=O.Owner ORDER BY Year ASC
) X

Francisco 15 年前

C语言LIQ2SQL:

var results = db.owner_tbls.Select(p=>p.auto_tbls.OrderBy(q=>q.year).First());

Terry 13 年前

示例linq/linqpad脚本…返回所需的结果,而不必对可能筛选/选择的汽车上的每个字段进行大量嵌套查询。

var Owners = new [] { new { Name = "John" }, new { Name = "James" }, new { Name = "Jeff" } };
var Autos = new [] { 
    new { Owner = "John", Auto = "Corvette", Year = 1968 },
    new { Owner = "John", Auto = "Prius", Year = 2008 },
    new { Owner = "James", Auto = "F-150", Year = 2004 },
    new { Owner = "James", Auto = "Cadillac", Year = 2002 },
    new { Owner = "James", Auto = "Accord", Year = 2009 },
    new { Owner = "Jeff", Auto = "Tesla", Year = 2010 },
    new { Owner = "Jeff", Auto = "Hyundai", Year = 1996 }
};

var results = from o in Owners
              join c in Autos.OrderBy( a => a.Year )
                             .GroupBy( g => g.Owner )
                             .Select( a => a.FirstOrDefault() )
                     on o.Name equals c.Owner
              orderby o.Name descending
              select new { o.Name, c.Auto, c.Year };

results.Dump();

Lev Levitsky 12 年前

像这样的:

SELECT o.owner, a.auto, a.EarliestYear
FROM owner_tbl o
INNER JOIN (SELECT owner, auto, min(year) As EarliestYear FROM auto_tbl 
GROUP BY owner, auto) a ON o.owner = a.owner

Biggles 6 年前

下面是一个使用子查询的简单解决方案,该子查询只为每个拥有至少一辆车的车主返回最旧的车。

SELECT owner_tbl.owner
, auto_tbl.auto
, auto_tbl.year
FROM owner_tbl
, auto_tbl
WHERE owner_tbl.owner = auto_tbl.owner
AND auto_tbl.auto IN
  (SELECT TOP 1 auto 
  FROM auto_tbl 
  WHERE owner = owner_tbl.owner
  ORDER BY year)
ORDER BY owner_tbl.owner