SQL 五（重复数据、统计）

逃离我推掉我的手 2022-06-12 10:15 178阅读 0赞

转自 [http://blog.csdn.net/yangyi22/article/details/7493041][http_blog.csdn.net_yangyi22_article_details_7493041]

**题一:**表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

[http_blog.csdn.net_yangyi22_article_details_7493041]: http://blog.csdn.net/yangyi22/article/details/7493041