已知下表
+-----+-----------+-------+
| sno | gradeName | score |
+-----+-----------+-------+
| 1 | 语文 | 80 |
| 2 | 数学 | 80 |
| 3 | 英语 | 100 |
| 4 | 英语 | 90 |
+-----+-----------+-------+
显示的结果
gradeName score score
英语 100 90
sql怎么写
+-----+-----------+-------+
| sno | gradeName | score |
+-----+-----------+-------+
| 1 | 语文 | 80 |
| 2 | 数学 | 80 |
| 3 | 英语 | 100 |
| 4 | 英语 | 90 |
+-----+-----------+-------+
显示的结果
gradeName score score
英语 100 90
sql怎么写
sql="select * from tablename where gradeName='英语'";
ResultSet rs=smt.executeQuery(sql);
System.out.print("英语 ");
while(rs.next()){
System.out.print(rs.getInt("score")+" ");
}
(
select a.sno sno1,a.gradeName,a.score score1,b.sno sno2,b.score score2
from tbl1 a inner join tbl1 b
on a.gradeName=b.gradeName and a.sno!=b.sno
) c
-> ( sno int primary key auto_increment,
-> gradeName varchar(4),
-> score int
-> );mysql> insert into testcsdn (gradeName,score) values('Chinese',80);
Query OK, 1 row affected, 1 warning (0.00 sec)mysql> insert into testcsdn (gradeName,score) values('math',80);
Query OK, 1 row affected (0.00 sec)mysql> insert into testcsdn (gradeName,score) values('English',100);
Query OK, 1 row affected, 1 warning (0.00 sec)mysql> insert into testcsdn (gradeName,score) values('English',90);
Query OK, 1 row affected, 1 warning (0.00 sec)mysql> select *from testcsdn;
+-----+-----------+-------+
| sno | gradeName | score |
+-----+-----------+-------+
| 1 | Chin | 80 |
| 2 | math | 80 |
| 3 | Engl | 100 |
| 4 | Engl | 90 |
+-----+-----------+-------+
4 rows in set (0.00 sec)mysql> select a.gradeName,a.score,b.score from testcsdn a,testcsdn b
-> where a.gradeName=b.gradeName and
-> a.score !=b.score;
+-----------+-------+-------+
| gradeName | score | score |
+-----------+-------+-------+
| Engl | 90 | 100 |
| Engl | 100 | 90 |
+-----------+-------+-------+
2 rows in set (0.00 sec)这里出来两条,因为你没有告诉我成绩怎么排,低在前还还高在前======================
不过,一般是没有这种用法的,
出现这样的情况可能是设计数据库的时候没有用三范式规范起来!给分吧.谢谢!
所以楼上直接用score不等判断觉得不太严谨。另外如果特定情况下 比如说只有一条数据重复的话
select top 1 c.gradeName,c.score1 score,c.score2 score from
(
select a.sno sno1,a.gradeName,a.score score1,b.sno sno2,b.score score2
from tbl1 a inner join tbl1 b
on a.gradeName=b.gradeName and a.sno!=b.sno
) c
这个sql是可以查出来的
但是如果重复的数据很多的话就不可以