问题描述
我正在从sql-ex.ru开始练习16。该问题提出以下问题: 找到具有相同速度和RAM的PC机型号对。
结果,每个结果对只显示一次,即(i,j)但不显示(j,i)。
结果集:具有较高号码的型号,具有较低数量,速度和RAM的型号。
数据库模式是:
产品(制造商,型号,型号)
PC(代码,型号,速度,RAM,高清,cd,价格)
笔记本电脑(代码,型号,速度, hd,屏幕,价格)
打印机(代码,型号,颜色,类型,价格)
我写了以下查询:
SELECT A.model,B.model,A.speed,A.ram
从PC A
JOIN PC B ON(A.model&B; B.model)
WHERE A.speed = B.speed
AND A.ram = B.ram
但这显示i,j的重复项为j,i。这是我的输出:
型号型号速度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
如您所见,i,j的值被翻转,并被计入不同的值。有没有一个简单的方法来摆脱像这样的重复?我在这方面很失落。
我认为问题陈述是一个线索,你需要一个 A.model> B.model
条件某处。加入的 ON
条件听起来像一个很好的候选人:
SELECT A.model ,B.model,A.speed,A.ram
FROM PC A
JOIN PC B ON(A.model> B.model) - << === Here
WHERE A.speed = B.speed
AND A.ram = B.ram
<>
是对称的; >
不是。切换到>
确保如果 {i,j}
在,则 {j ,i}
肯定会出来。
I am working on exercise 16 from sql-ex.ru. The problem asks the following:
Find the pairs of PC models having identical speeds and RAM.
As a result, each resulting pair is shown only once, i.e. (i, j) but not (j, i).
Result set: model with higher number, model with lower number, speed, and RAM.
The database schema is :
Product(maker, model, type)
PC(code, model, speed, ram, hd, cd, price)
Laptop(code, model, speed, ram, hd, screen, price)
Printer(code, model, color, type, price)
I wrote the following query:
SELECT A.model, B.model, A.speed, A.ram
FROM PC A
JOIN PC B ON (A.model<>B.model)
WHERE A.speed=B.speed
AND A.ram=B.ram
But this displays duplicates of i,j as j,i. Here is my 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
As you can see, the values of i,j are flipped and counted as distinct values. Is there an easy way to get rid of duplicates like this? I am kind of lost on that part.
I think the "model with higher number, model with lower number" in the problem statement is a clue that you need to have a A.model > B.model
condition somewhere. Join's ON
condition sounds like a fine candidate:
SELECT A.model, B.model, A.speed, A.ram
FROM PC A
JOIN PC B ON (A.model > B.model) -- <<<=== Here
WHERE A.speed=B.speed
AND A.ram=B.ram
The <>
is symmetrical; the >
is not. Switching to >
ensures that if {i, j}
is in, then {j, i}
will be out for sure.
这篇关于SQL不包括2列的组合上的重复值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!