我有三个相关的表格，如下所示：
+-------------+---------+------------+
| 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中正确执行此操作？下一步将按姓氏搜索，并得到“ my target”之类的结果。
我的目标是：
+---------+---------------+
| 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 |
+---------+-------------------------+--------------------+
.....
最佳答案
试试这些查询
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
关于java - 通过HQL联接两个，三个相关表，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/42437631/