There are three tables as follows
I would like to join these three and get the following results, but I don't know how to do it
When I do OUTER JOIN, I get duplicate records, but I want to return only one value, and after the second one, I want to return NULL.
(I didn't return NULL for the ID to be easy to understand, but it's the same here.)
If anyone knows how to implement this, please let me know.
Thank you for your cooperation.
If the value is the same as the above line, it would be a bad idea to set the value of the next line to NULL.No, it's an evil way.
SQL extraction results do not take into account the order of output and do not compare the values in the previous and last rows (except Order By), so I think it is a requirement that the program should deal with.
However, MySQL8 can use a window function, so you can use the lag function to return null if it is the same value as the previous line.
select
case when lag(order_id_1) over w = order_id_1 then null else order_id_1 end as order_id_1,
case when lag(order_id_2)over w=order_id_2then null else order_id_2end as order_id_2,
・・・・・
from table1 inner join table2...
I'm sure you're doing the same thing as Hirapon, but you've determined ROW_NUMBER(), and you can branch out depending on whether it's 1.
SELECT
CASE order_rank_1 WHEN 1THEN order_id_1 END,
CASE order_rank_2WHEN1THEN order_id_2END,
order_id_3,
CASE order_rank_1 WHEN 1 THEEN order_value_1 END,
CASE order_rank_2WHEN1THEN order_value_2END,
order_value_3
FROM(
SELECT
order_1.order_id_1,
order_2.order_id_2,
order_3.order_id_3,
order_value_1,
order_value_2,
order_value_3,
ROW_NUMBER()OVER(PARTION BY ORDER_1.order_id_1)AS order_rank_1,
ROW_NUMBER() OVER(Partition BY order_2.order_id_2)AS order_rank_2
FROM order_3
LEFT OUTER JOIN ORDER_2 ON ORDER_3.order_id_2=order_2.order_id_2
LEFT OUTER JOIN ORDER_1 ON ORDER_2.order_id_1 = order_1.order_id_1
ASX;
© 2024 OneMinuteCode. All rights reserved.