java - 通过HQL连接两、三个相关表

Question

我有三个相关的表格，如下所示:

+-------------+---------+------------+
| customer_id | name    | surname    |
+-------------+---------+------------+
|           1 | Jan     | Bielecki   |
|           2 | Adam    | Bielen     |
.....

+----------+--------+---------------------+-------------+
| order_id | amount | date                | customer_id |
+----------+--------+---------------------+-------------+
|        1 |  10.23 | 2017-02-15 00:00:00 |           1 |
|        2 |  20.56 | 2017-02-16 00:00:00 |           1 |
|        3 |  30.57 | 2017-02-17 00:00:00 |           2 |
|        4 |  40.52 | 2017-02-18 00:00:00 |           2 |
|        5 |  50.30 | 2017-02-19 00:00:00 |           1 |
.....

+-----------------+-----------+------------+----------+
| order_detail_id | item_name | item_price | order_id |
+-----------------+-----------+------------+----------+
|               1 | item 1    |       2.00 |        1 |
|               2 | item 2    |       2.50 |        1 |
|               3 | item 3    |       3.00 |        1 |
|               4 | item 4    |       4.00 |        2 |
|               5 | item 5    |       5.50 |        2 |
|               6 | item 6    |       7.60 |        3 |
|               7 | item 7    |       5.00 |        3 |
|               8 | item 8    |       3.00 |        4 |
|               9 | item 9    |       7.00 |        4 |
|              10 | item 10   |       8.00 |        4 |
|              11 | item 11   |       2.00 |        5 |
|              12 | item 12   |       2.50 |        5 |
.....

首先，我正在与连接第一个和第二个表进行斗争。用于连接姓氏与金额之和。

我正在尝试这样:

select sum(o.amount) as totalSum
from Order as o,
Customer as c
join c.surname as surname
where c.orders:=o.customer
group by o.customer
order by sum(o.amount) desc

本节以多种方式进行更改:where c.orders:=o.customer最常见的错误是 NullPointerException。

在 SQL 中完成此操作之前:表 customer_id <->total_amount

SELECT customer_id,
SUM(amount) as total_amount,
COUNT(amount) as orders_quantity
FROM softhis_db.orders
GROUP BY customer_id;

表 customer_id <-> 3 个最到期的。订单+日期

SELECT orders.customer_id, orders.amount, orders.date
FROM orders_details
RIGHT JOIN orders
ON orders.order_id = orders_details.order_id
ORDER BY amount DESC
LIMIT 3;

客户:

@Entity
@Table(name = "customers")
public class Customer {

@Id
@Column(name = "customer_id")
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;

@Column(name = "name", length = 50)
private String name;

@Column(name = "surname", length = 50)
private String surname;

@OneToMany(mappedBy = "customer")
private Set<Order> orders = new HashSet<>();

订单:

@Entity
@Table(name = "orders")
public class Order {

@Id
@Column(name = "order_id")
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;

@Column(name = "date")
private Date date;

@Digits(integer = 5, fraction = 2)
@Column(name = "amount")
private BigDecimal amount;

@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "customer_id")
private Customer customer;

@OneToMany(mappedBy = "order")
private Set<OrderDetail> ordersDetails = new HashSet<>();

订单详情:

@Entity
@Table(name = "orders_details")
public class OrderDetail {

@Id
@Column(name = "order_detail_id")
@GeneratedValue(strategy = GenerationType.AUTO)
private Lon id;

@Column(name = "item_name", length = 50)
private String itemName;

@Digits(integer = 5, fraction = 2)
@Column(name = "item_price")
private BigDecimal itemPrice;

@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "order_id")
private Order order;

重点是如何在 HQL 中正确执行此操作？下一步将按姓氏搜索并获得“我的目标”等结果。

我的目标是:

+---------+---------------+
| surname | sum of amount | 
+---------+---------------+
|Bielecki | 150.40        | 
|Bielen   | 130.34        |
......

+-----------------------------------+--------------------+
| surname | 3 most expensive orders | date               |
+-----------------------------------+--------------------+
|Bielecki | 120.23                  |2017-02-15 00:00:00 | 
|Bielecki | 80.20                   |2017-02-18 00:00:00 |
|Bielecki | 20.20                   |2017-02-19 00:00:00 |
+---------+-------------------------+--------------------+
|Bielen   | 190.23                  |2017-02-15 00:00:00 | 
|Bielen   | 80.20                   |2017-02-18 00:00:00 |
|Bielen   | 20.20                   |2017-02-19 00:00:00 |
+---------+-------------------------+--------------------+
.....

Answer 1

尝试这些查询

SELECT
   customers.surname
 , SUM(amount) "sum of amount"
FROM 
 customers
INNER JOIN
 orders 
ON
  customers.customer_id = orders.customer_id
GROUP BY
  customers.surname
ORDER BY
 customers.surname ASC

对于每个姓氏的 3 个最昂贵的订单，您需要使用用户变量来创建排名。并根据该排名进行筛选。

SELECT
   customers.surname
 , orders_ranked.amount AS "3 most expensive orders"
 , orders_ranked.date
FROM ( 
  SELECT 
   *
   , (
     CASE
         WHEN
           @customer_id = orders.customer_id
         THEN
           @rank := @rank + 1 
         ELSE
           @rank := 1 
       END
     )
     AS
       rank
    , @customer_id := orders.customer_id     
  FROM 
   orders
  CROSS JOIN (
    SELECT 
        @customer_id := 0
      , @rank := 0
    )
     AS
       init_user_variables 
   ORDER BY 
     orders.customer_id ASC
   , orders.amount DESC 
 ) 
  AS 
    orders_ranked
INNER JOIN 
 customers
ON
 orders_ranked.customer_id = customers.customer_id    
WHERE 
   orders_ranked.rank <= 3