Geek Answers Handbook

Inequality Combines

I have been doing SQL for several years, and throughout this time my thought about joins has been to combine equivalence, as in

select ... from t1 join t2 on t1.a = t2.b

Notice how the mix is ​​based on one or more equalities, here t1.a = t2.b. However, lately I don’t remember where I saw the joining to nonequivalence (I just made this term, please tell me if there is a real name), where the join condition contains at least one uneven one, as in

select ... from t1 join t2 on t1.a > t2.b

What can be done to do something nice, especially with external connections. Let me illustrate this with an example.

Consider a table called a product with the following data:

product   year  price
----------------------
apple   2009    4
apple   2008    2
apple   2007    5
apple   2006    6
apple   2005    2
banana  2009    9
banana  2008    12
banana  2007    16
banana  2006    15
banana  2005    10

" ", , , , , :

select t1.`name`, t1.`year`, t1.`price`
from products as t1 join
( select `name`, max(`price`) as `max_price` from products group by `name` ) as t2 on t1.`name`=t2.`name` and t1.`price`=t2.`max_price`

, t2 , , ( tiebreaking)

, , :

select t1.`name`, t1.`year`, t1.`price`
from products as t1 left join products as t2 on t1.`name`=t2.`name` and t1.`price` < t2.`price`
where t2.`name` is null

, t1 t2. , , , t2 , , .

, , . , t1/t2, . Tiebreaking, , , .

, :

- (, -), , , , ( , , ) ?

: , , , . , . , , . , .

+5
2

SQL Query SQL Server .

, "". on - . , , where .

, . SQL , .

0

" ", , , , ,

, ( , jonealres)

select name, 
       year, 
       price,
       max(price) over (partition by name) as most_expensive
from products
+2

More articles:


All Articles