Geek asks and answers

Search for PC model pairs

I am trying to solve a sql exercise.

Here is the circuit

PC

code     int  
model    varchar(50)  
speed    smallint  
ram      smallint  
hd       real  
cd       varchar(10)  
price    money

Problem:

Find pairs of PC models that have similar speeds and RAM. As a result, each resulting pair is shown only once, i.e. (i, j), but not (j, i).

I wrote a query, but it displays (i, j) along with (j, i).

My request:

select  t1.model,t2.model,t1.speed,t1.ram from pc t1 , pc t2
where t1.speed = t2.speed and t1.ram = t2.ram and t1.model != t2.model

Output:

model   model   speed   ram
1121    1233    750     128
1232    1233    500     64
1232    1260    500     32
1233    1121    750     128
1233    1232    500     64
1260    1232    500     32

Required Conclusion:

model   model   speed   ram
1233    1121    750     128
1233    1232    500     64
1260    1232    500     32

So how do I avoid (j, i) in my output?

Thank.

+3
source share
6 answers

The difference between your output and the required output is exactly the lines that have t1.model < t2.model. To remove them, just add another one AND t1.model >= t2.model. But since you already require that t1.model != t2.model, a full request

select  t1.model,t2.model,t1.speed,t1.ram 
from pc t1 , pc t2 
where t1.speed = t2.speed and t1.ram = t2.ram and t1.model > t2.model
+5
source

, code , , t1.code < t2.code.

+4

SELECT als1.model, als2.model, als1.speed, als1.ram FROM PC als1, PC als2 WHERE (als1.speed = als2.speed) AND (als1.ram = als2.ram) AND (als1. > als2.model)

(j, i). , , , (als1.model > als2.model), , (i, j) (j, i).

+2
SELECT DISTINCT t.model, l.model, t.speed, t.ram
FROM PC as t JOIN PC as l
ON t.speed = l.speed AND t.ram = l.ram
AND t.model>l.model

+2
     select pc1.model,pc2.model,pc1.speed,pc1.ram
     from pc pc1,pc pc2
     where pc1.speed=pc2.speed and pc1.ram= pc2.ram 
     group by pc1.model, pc2.model, pc1.speed, pc1.ram
     having pc1.model> pc2.model
+1

, , , t1.model > t2.model:

SELECT t1.model,
       t2.model,
       t1.speed,
       t1.ram
FROM pc t1,
     pc t2
WHERE t1.speed = t2.speed
  AND t1.ram= t2.ram
GROUP BY t1.model,
         t2.model,
         t1.speed,
         t1.ram
HAVING t1.model > t2.model
+1

More articles:


All Articles