SQL面试题 五（重复数据、统计）

题一:表stu

+----+------+-----+-------+
| id | sno  | cno | score |
+----+------+-----+-------+
|  1 | 1001 |   1 |    89 |
|  3 | 1001 |   1 |    89 |
|  2 | 1002 |   1 |    87 |
|  4 | 1002 |   1 |    90 |
|  5 | 1003 |   1 |    86 |
+----+------+-----+-------+

1、查询出sno,cno重复的数据：

> select * from rs where (sno,cno) in (select sno,cno from rs group by sno,cno having count(*)>1);

2、查询sno,cno,score都重复的数据，且除id最小的一条数据外：

> select * from rs where (sno,cno,score) in(select sno,cno,score from rs group by sno,cno,score having count(*)>1)and id not in (select min(id) from rs group by sno,cno,score having count(*)>1);

3、删除sno,cno,score都重复的数据，保留id最小的一条数据：

>（SQL Server未验证）delete  from rs where (sno,cno,score) in(select sno,cno,score from rs group by sno,cno,score having count(*)>1)and id not in (select min(id) from rs group by sno,cno,score having count(*)>1);

>（MySQL）

create table temp as select * from rs where (sno,cno,score) in(select sno,cno,score from rs group by sno,cno,score having count(*)>1)and id not in (select min(id) from rs group by sno,cno,score having count(*)>1);

delete  from rs where id in(select  id  from temp);

drop table temp;

题二:有以下三张表

商品表：tp 
+-------+-------+
| pcode | pname |
+-------+-------+
|     1 | cpu   |
|     2 | 内存  |
|     3 | 硬盘  |

城市表：tc 
+-----+-------+
| cno | cname |
+-----+-------+
| 101 | 广州  |
| 102 | 深圳  |
| 103 | 上海  |
+-----+-------+

销售表：ts 
+-------+-----+-------+
| pcode | cno | count |
+-------+-----+-------+
|     1 | 101 | 10000 |
|     1 | 102 |   500 |
|     1 | 103 | 20000 |
|     2 | 101 | 40000 |
|     2 | 103 | 30000 |
|     3 | 102 | 90000 |
+-------+-----+-------+

问题：需要得到以下结构的统计结果，写出SQL语句：

            广州      深圳       上海        总计 

CPU  10000   5000      20000    35000 
内存   40000   0             30000    70000 
硬盘   0            90000    0             90000 
总计   50000   95000    50000   195000 

>