多表联合查询请教大神

小小小的程序员 2016-09-10 12:06:42

有三个表
Line LineOrder LineOrderPerson
Line 旅游线路表
LineOrder 旅游线路订单表
LineOrderPerson 旅游订单人员表

Line 字段 Id,Title
LineOrder字段 Id,LineTitle,LineId,OrderStatus,TotalPrice
LineOrderPerson字段 Id,OrderId

需求
获取所有线路的成功订单总量及线路名称及所有该线路的订单人员总数及该线路的订单总金额

...全文

130 3 打赏收藏转发到动态举报

写回复

用AI写文章

3 条回复

切换为时间正序

请发表友善的回复…

发表回复

小小小的程序员 2016-09-12

打赏
举报

引用 2 楼 ch21st 的回复:

你的需求没有描述清楚,比如：计算：该线路的订单人员总数及该线路的订单总金额是否包含失败的，我下面的例子未包含否则需要将left join lineorder的条件改下


;WITH Line(ID,Title) AS (
  select 1,'a' UNION ALL
  SELECT 2, 'b' UNION ALL
  SELECT 3,'c'
),LineOrder(Id,LineTitle,LineId,OrderStatus,TotalPrice) AS (
    select 1,'a',1,'s;',100 UNION ALL
    select 2,'a',1,'f',120 UNION ALL
    select 3,'b',2,'f',150 UNION ALL
    SELECT 4,'b',2,'s',155 UNION ALL
    select 5,'c',3,'s',250 UNION ALL
    SELECT 6,'c',3,'f',255 UNION ALL
    SELECT 7,'c',3,'f',200
),LineOrderPerson(ID,OrderId) AS (
  select 1,4 UNION ALL
  select 2,6 UNION ALL
  select 3,1 UNION ALL
  select 4,7
)
select l.Id,l.Title,count(0) as SuccedOrderCount
       ,sum(case WHEN lop.id is null then 0 else 1 END )as LinePersonCount     
       ,sum(lo.TotalPrice) as LineTotalPrice
from Line as l LEFT JOIN  LineOrder as lo on lo.LineId=l.ID
left join LineOrderPerson as lop on lop.OrderId=lo.Id AND lo.OrderStatus='s'
GROUP BY l.Id,l.Title

牛逼，接分

道素 2016-09-10

打赏
举报


;WITH Line(ID,Title) AS (
  select 1,'a' UNION ALL
  SELECT 2, 'b' UNION ALL
  SELECT 3,'c'
),LineOrder(Id,LineTitle,LineId,OrderStatus,TotalPrice) AS (
    select 1,'a',1,'s;',100 UNION ALL
    select 2,'a',1,'f',120 UNION ALL
    select 3,'b',2,'f',150 UNION ALL
    SELECT 4,'b',2,'s',155 UNION ALL
    select 5,'c',3,'s',250 UNION ALL
    SELECT 6,'c',3,'f',255 UNION ALL
    SELECT 7,'c',3,'f',200
),LineOrderPerson(ID,OrderId) AS (
  select 1,4 UNION ALL
  select 2,6 UNION ALL
  select 3,1 UNION ALL
  select 4,7
)
select l.Id,l.Title,count(0) as SuccedOrderCount
       ,sum(case WHEN lop.id is null then 0 else 1 END )as LinePersonCount     
       ,sum(lo.TotalPrice) as LineTotalPrice
from Line as l LEFT JOIN  LineOrder as lo on lo.LineId=l.ID
left join LineOrderPerson as lop on lop.OrderId=lo.Id AND lo.OrderStatus='s'
GROUP BY l.Id,l.Title

小小小的程序员 2016-09-10