如何以相同的速度获得PC机型？

Question

我试图获得相同的速度和相同的内存容量的PC模型对。每个结果对只应该显示一次，即(i，j)，而不是(j，i)。

所需结果如下:模型的数目较大，模型的数目、速度和RAM都较小。我期待的结果是

╔═══════╦═══════╦═══════╦═════╗
║ model ║ model ║ speed ║ ram ║
╠═══════╬═══════╬═══════╬═════╣
║  1233 ║  1121 ║   750 ║ 128 ║
║  1233 ║  1232 ║   500 ║  64 ║
║  1260 ║  1232 ║   500 ║  32 ║
╚═══════╩═══════╩═══════╩═════╝

表结构是

╔═══════╦═══════╦═════╗
║ model ║ speed ║ ram ║
╠═══════╬═══════╬═════╣
║  1121 ║   600 ║ 128 ║
║  1121 ║   600 ║ 128 ║
║  1121 ║   750 ║ 128 ║
║  1232 ║   450 ║  32 ║
║  1232 ║   450 ║  64 ║
║  1232 ║   500 ║  32 ║
║  1232 ║   500 ║  64 ║
║  1233 ║   500 ║  64 ║
║  1233 ║   750 ║ 128 ║
║  1233 ║   800 ║ 128 ║
║  1233 ║   900 ║ 128 ║
║  1260 ║   500 ║  32 ║
╚═══════╩═══════╩═════╝

我正在尝试的查询是这样的，我使用的是MySQL

select pca.model, pcb.model, pca.speed, pca.ram 
from pc pca 
where pca.speed in (
    select pcb.speed 
    from pc pcb 
    where pca.speed = pcb.speed
);

Answer 1

这应该能起作用：

select distinct 
  pca.model, pcb.model, pca.speed, pca.ram
from pc pca 
join pc pcb 
  on (pca.speed = pcb.speed 
  and pca.ram = pcb.ram 
  and pca.model > pcb.model)

Answer 2

不是完全相同的格式，但实现了目标：

SELECT GROUP_CONCAT(DISTINCT model ORDER BY model) AS 'model(s)',
       speed,
       ram
    FROM tbl
    GROUP BY speed, ram;

Answer 3

另一种创造性的方式：

select distinct a.model, b.model, a.speed, a.ram from TblComputer a join TblComputer b on a.speed = b.speed and a.ram=b.ram where a.model > b.model
 union
 select distinct b.model, a.model, a.speed, a.ram from TblComputer a join TblComputer b on a.speed = b.speed and a.ram=b.ram where b.model > a.model

这是我的数据：

create table TblComputer
(
    model int,
    speed int,
    ram int
)

insert into TblComputer(model, speed, ram) values (1121,   600, 128) 
insert into TblComputer(model, speed, ram) values (1121,   600, 128) 
insert into TblComputer(model, speed, ram) values (1121,   750, 128 )
insert into TblComputer(model, speed, ram) values (1232,   450,  32 )
insert into TblComputer(model, speed, ram) values (1232,   450,  64 )
insert into TblComputer(model, speed, ram) values (1232,   500,  32 )
insert into TblComputer(model, speed, ram) values (1232,   500,  64 )
insert into TblComputer(model, speed, ram) values (1233,   500,  64 )
insert into TblComputer(model, speed, ram) values (1233,   750, 128 )
insert into TblComputer(model, speed, ram) values (1233,   800, 128 )
insert into TblComputer(model, speed, ram) values (1233,   900, 128 )
insert into TblComputer(model, speed, ram) values (1260,   500,  32 )