changsons
changsons
采纳率0%
2017-04-22 15:27 阅读 993

SQL 两张表的查询语句如何写

10

Table1
ID SN
1 AAAAA
2 BBBBB
3 CCC
Table2
ID TIME
1 2017.3.20
1 2017.3.21
1 2017.3.22
2 2017.4.20
2 2017.4.21
2 2017.4.22
3 2017.4.22
表1和表2的 ID 是关联的,表2的每个ID可能有多条记录,但是TIME列的值不同的。
要求输入多个SN,查询结果要求按时间排序,一个SN只保留最后一个日期的一条记录:
ID SN TIME
1 AAAAA 2017.3.22
2 BBBBB 2017.4.22
3 CCC 2017.4.22

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享

6条回答 默认 最新

  • hkmjia hkmjia 2017-04-22 16:22

    SELECT dbo.Table_1.Id, dbo.Table_1.SN, MAX(dbo.Table_2.Time) FROM dbo.Table_2 INNER JOIN dbo.Table_1 ON Table_1.Id = Table_2.Id
    GROUP BY dbo.Table_1.Id, dbo.Table_1.SN, dbo.Table_2.Id

    点赞 1 评论 复制链接分享
  • u013639980 我得乖乖 2017-04-22 15:41

    select max(t2.id) as id,(select t1.sn from table1 t1 where t1.id =max(t2.id) as sn,max(t2.time) as time from table2 t2 group by t2.id;

    点赞 评论 复制链接分享
  • qq503758762 点点滴滴的淡淡的 2017-04-24 02:32
     select sn,(select max(time) from table2 where id = q.id ) from table1 q
    
    点赞 评论 复制链接分享
  • JustWantToFly JustWantToFly 2017-04-24 08:05

    select * from table2 t2 left join table1 t1 on t2.id=t1.id
    where t1.sn=''
    order by t2.time desc

    点赞 评论 复制链接分享
  • m0_37648054 我最爱晓曼了 2017-04-27 12:22

    select table2.sn from table1 left join (select max(time) sn,id from table2) table2 on table2.id=table1.id

    点赞 评论 复制链接分享
  • m0_37648054 我最爱晓曼了 2017-04-27 12:23

    select table2.sn from table1 left join (select max(time) sn,id from table2) table2 on table2.id=table1.id order by table2.sn desc

    点赞 评论 复制链接分享

相关推荐