代码之家 › 专栏 › 技术社区 › Kuri

内部联接+选择最近的

sql-server-ce sql

Kuri · 技术社区 · 10 年前

我一直在试着回答下面的问题,但老实说,这让我发疯了。

我在MS SQL CE 4.0上有两个表

表1名称:项目

身份证件
项目_代码
记录者(_D)
描述

ID | Item_Code | Logged_by | Description

1  |    A      |   Pete    |    just an A
2  |    B      |   Mary    |    Seams like a B
3  |    C      |   Joe     |    Obviously this is a C
4  |    D      |   Pete    |    This is another A

表2名称:项目注释

身份证件
项目_代码
议论
日期

ID | Item_Code | Comment         |   Date

1  |    B      |    Done         |   2014/08/08
2  |    A      |    Nice A       |   2014/08/08
3  |    B      |    Send 1 More  |   2014/08/09
4  |    C      |    Done         |   2014/08/10
5  |    D      |    This is an A |   2014/08/10
6  |    D      |    Opps Sorry   |   2014/08/11

想要的结果:我希望加入来自 项目_注释 到项目桌子

ID | Item_Code | Logged_by | Description               | Comment

1  |     A    |     Pete   |    just an A              |  Nice A
2  |     B    |     Mary   |    Seams like a B         |  Send 1 More    
3  |     C    |     Joe    |    Obviously this is a C  |  Done           
4  |     D    |     Pete   |    This is another A      |  Opps Sorry

我做了这个查询,但我得到了所有的信息。

SELECT * 
FROM Items t1 
JOIN
     (SELECT Item_Code, Comment, MAX(date) as MyDate
      FROM Item_Comments
      Group By Item_Code, Comment, Date           
     ) t2
  ON Item_Code= Item_Code
ORDER BY t1.Item_Code;

你知道怎么做吗?

3 回复 | 直到 10 年前

Brian DeMilia 10 年前

尝试:

select x.*, z.comment
  from items x
  join (select item_code, max(date) as latest_dt
          from item_comments
         group by item_code) y
    on x.item_code = y.item_code
  join item_comments z
    on y.item_code = z.item_code
   and y.latest_dt = z.date

Fiddle测试: http://sqlfiddle.com/#!6/d387f/8/0

您已经完成了查询,但在别名为t2的内联视图中,您是按注释分组的,因此max函数实际上根本没有聚合任何内容。在t2中,您应该只选择了item_code和max(日期),并仅按item_code分组,然后您可以使用它来加入item_comments(上面查询中的y和z)。

这是使用子查询实现这一点的第二种方法,但是我将坚持上面的方法(带有内联视图的连接):

select i.*, c.comment
  from items i
  join item_comments c
    on i.item_code = c.item_code
 where c.date = (select max(x.date)
                   from item_comments x
                  where x.item_code = c.item_code)
 order by i.id

Fiddle测试: http://sqlfiddle.com/#!6/d387f/11/0

Nick.Mc 10 年前

注意,如果你运行这个内部片段,你会得到每个记录:

  SELECT Item_Code, Comment, MAX(date) as MyDate
  FROM Item_Comments
  Group By Item_Code, Comment, Date

您只需要最新的评论。假设这是SQL Server 2008或更早版本,则会显示每个Item_Code的最新日期:

  SELECT Item_Code, MAX(date) as MyDate
  FROM Item_Comments
  Group By Item_Code

现在,您需要加入到后面,并查找该日期的评论:

SELECT C.* 
FROM Item_Comments C 
INNER JOIN 
 (SELECT Item_Code, MAX(date) as MyDate
  FROM Item_Comments
  Group By Item_Code 
 ) t2
 ON  C.Item_Code= t2.Item_Code
 AND C.date = t2.MyDate

现在,您可以使用它连接回原始表:

SELECT t1.*, LatestComment.*
FROM Items t1 
INNER JOIN
(
SELECT C.* 
FROM Item_Comments C 
INNER JOIN 
 (SELECT Item_Code, MAX(date) as MyDate
  FROM Item_Comments
  Group By Item_Code 
 ) t2
 ON  C.Item_Code= t2.Item_Code
 AND C.date = t2.MyDate
 ) LatestComment
 On LatestComment.Item_Code = t1.Item_Code

根据您使用的实际数据库,这可能会变得更简单。这就是为什么您需要标记数据库和版本。

Vinoth_S 10 年前

试试这个,

create table items (id int, item_code char(1), logged_by varchar(10), description varchar(30));

insert into items values (1, 'A', 'Pete', 'just an A');
insert into items values (2, 'B', 'Mary', 'Seams like a B');
insert into items values (3, 'C', 'Joe', 'Obviously this is a C');
insert into items values (4, 'D', 'Pete', 'This is another A');


create table item_comments (id int, item_code char(1), comment varchar(20), date date);

insert into item_comments values (1, 'B', 'Done', '2014/08/08');
insert into item_comments values (2, 'A', 'Nice A', '2014/08/08');
insert into item_comments values (3, 'B', 'Send 1 More', '2014/08/09');
insert into item_comments values (4, 'C', 'Done', '2014/08/10');
insert into item_comments values (5, 'D', 'This is an A', '2014/08/10');
insert into item_comments values (6, 'D', 'Opps Sorry', '2014/08/11');

select * from items;
select * from item_comments;

select * from (select i.logged_by,i.id,i.item_code,i.description,ic.comment
,row_number() over(partition by i.id order by i.id )as Rnk
from items i inner join item_comments ic
on i.item_code=ic.item_code and i.id in(1,3)) x
where x.Rnk=1
union
select * from (select i.logged_by,i.id,i.item_code,i.description,ic.comment
,row_number() over(partition by i.id order by i.id )as Rnk
from items i inner join item_comments ic
on i.item_code=ic.item_code and i.id in(2,4)
) x where x.Rnk=2 order by item_code