Carvin_Zh
安静的求学者
采纳率50%
2015-08-04 05:03 阅读 1.5k

SQL新手求教一个关于数据库的问题,着急!在线等!各位大牛们,感激不尽!

20

实习生,TeamLeader给了那么一个数据库题目:
给了如下两张独立的表:
Person
图片说明

Orders
图片说明

要求从这两张表中选出以下结果:
图片说明

ItemCount是购买的总数!
求救!在线等!

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

5条回答 默认 最新

  • 已采纳
    taomanman 暖枫无敌 2015-08-04 05:47
     select t.Id_p,t.LastName,COUNT(OrderNo) as ItemCount from 
    (
    select t1.OrderNo,ISNULL(t1.Id_p,t2.Id_p) as Id_p,isnull(t2.LastName,'Unknown') as LastName from Orders t1
    full join  Person t2
    on t1.Id_p = t2.Id_p
    ) t group by t.Id_p,t.LastName
    

    图片说明

    点赞 2 评论 复制链接分享
  • tangguo123_li tangguo123_li 2015-08-04 05:11

    ItemCount是购买的总数

    点赞 评论 复制链接分享
  • danielinbiti danielinbiti 2015-08-04 05:15

    SELECT Person.*,(SELECT COUNT(*) FROM Orders WHERE Orders.ID_P=PERSON.ID_P) ItemCount from Person

    点赞 评论 复制链接分享
  • yupeigu 阳泉酒家小当家 2015-08-04 05:39

    这个应该可以:

     SELECT p.lastname, o.ItemCount 
    from Person p
    left join
    (
    SELECT id_p ,COUNT(*) as ItemCount FROM Orders group by id_p)
    )o
     on o.ID_P=p.ID_P
    
    点赞 评论 复制链接分享
  • yupeigu 阳泉酒家小当家 2015-08-04 05:41

    修改了一下:

    SELECT isnull(p.lastname,'Unknow') lastname,
    o.ItemCount
    from Person p
    full join
    (
    SELECT id_p ,COUNT(*) as ItemCount FROM Orders group by id_p)
    )o
    on o.ID_P=p.ID_P

    点赞 评论 复制链接分享

相关推荐