时间戳

首页 > 解决方案 > 访问子查询连接

sql - 访问子查询连接

问题描述

我需要在 Access 中加入多个表格以生成报告：

CustomerID| Name
----------+-----------
1         | Jim
2         | Bob


OrderID | CustomerID 
--------+------------
1       | 1          
2       | 2  
3       | 2                 

OrderID| ItemID
-------+-----
1      | 1
1      | 1
2      | 3
3      | 2

ItemID | Cost
-------+-----
1      | 5
2      | 30
3      | 15

OrderID| ShippingCost
-------+-----
1      | 25
2      | 25
3      | 25

我想将结果返回为：

Name | Total ItemCost | Total ShippingCost | Total Cost|
-----+----------------+--------------------+-----------
JIM  | 10             | 25                 |35
BOB  | 30             | 25                 |55
BOB  | 15             | 25                 |40

本质上，我想将客户 ID 链接到他们的订单。然后将项目成本链接到订单，并对每个订单的结果求和。然后我想链接每个订单的运费。最后，我希望所有内容都按 orderID 分组，并将所有值汇总。

我简化了示例以使其更容易回答，但我能够在其他 ORACLE SQL DEVELOPER 中执行的查询如下（这在 Access 中不起作用）：

select 
person.first_name, 
person.last_name,
"Total Damages",
("Room Rate" * ROUND((A.CHECK_OUT_DATE - A.CHECK_IN_DATE))) as "Total Room Cost", 
"Total Deposit",
SUM("Total Deposit" + "Total Damages" + ("Room Rate" * ROUND((A.CHECK_OUT_DATE - A.CHECK_IN_DATE)))) as "Total Cost Exluding Total Room Cost"
from person
join CUSTOMER
on person.personid = customer.personid
join Hotel_Booking A
on A.customerid = customer.customerid
join (
select damage_charge.HOTEL_BOOKINGID as "DAMAGE", SUM(DAMAGE_RATE) as "Total Damages"
from damage_charge
join hotel_damages
on hotel_damages.HOTEL_DAMAGESID = damage_charge.HOTEL_DAMAGESID
where hotel_bookingid = damage_charge.HOTEL_BOOKINGID
group by damage_charge.HOTEL_BOOKINGID
)
on A.hotel_bookingid = DAMAGE
join (
select HOTEL_BOOKINGID as "ROOM", ROOM_RATE as "Room Rate"
from Room_Charge
join Hotel_Room_Rate 
on Room_Charge.HOTEL_ROOM_RATEID = Hotel_Room_Rate.HOTEL_ROOM_RATEID
)
on A.hotel_bookingid = ROOM
join (
select HOTEL_BOOKINGID as DEPOSIT, Deposit_Rate as "Total Deposit"
from Room_Charge
join Hotel_Deposit_Rate  
on Room_Charge.HOTEL_DEPOSIT_RATEID = Hotel_Deposit_Rate.HOTEL_DEPOSIT_RATEID
)
on A.hotel_bookingid = DEPOSIT
where ("Total Deposit" + "Total Damages" + ("Room Rate" * ROUND((A.CHECK_OUT_DATE - A.CHECK_IN_DATE)))) > 50
group by person.first_name, person.last_name, A.HOTEL_BOOKINGID, ROUND((A.CHECK_OUT_DATE - A.CHECK_IN_DATE)), "Total Damages", "Room Rate", "Total Deposit"
order by SUM("Total Deposit" + "Total Damages") DESC
;

标签： sql

解决方案

嗯。. . 根据您的描述，enter code here他应该为您工作：

select c.name, sum(itemcost) as itemcost, sum(shippingcost) as shippingcost,
       sum(itemcost)+ sum(shippingcost)
from ((customer as c left join
       orders as o
      ) on o.customerid = c.customerid left join
      (select s.orderid, sum(i.cost) as itemcost
       from sales as s inner join
            items as i
            on i.itemid = s.itemid
       group by s.orderid
      ) as si
     ) on si.orderid = o.orderid left  join
     shipping sh
     on sh.orderid = o.orderid
group by c.name;

我不知道您的示例查询与您的问题有什么关系。表不同，列不同，都有过滤条件。

推荐阅读