sql 经典查询问题

最新推荐文章于 2024-05-20 23:56:18 发布

深夜程序猿

最新推荐文章于 2024-05-20 23:56:18 发布

阅读量1k

点赞数

分类专栏： mysql 文章标签： sql操作 mysql

本文链接：https://blog.youkuaiyun.com/hyy147/article/details/90287923

版权

mysql 专栏收录该内容

48 篇文章

订阅专栏

查询没有订单的用户

1.
SELECT U.ID AS UserID 
FROM Users U
LEFT JOIN Order O ON O.UserID = U.ID
WHERE O.ID IS NULL

2.
select userid 
from user 
where userid not in (select distinct userid from order)

查询总订单价格大于100的订单号和总订单价格，SQL语句如下：

SELECT o_num,  SUM(quantity *item_price) AS orderTotal

FROM orderitems

GROUP BY o_num

HAVING SUM(quantity*item_price) >= 100;

查询订单前10数据：

订单
图片描述

orderinfo
图片描述

select sum(a.price * a.count) as total, a.order_id, b.user_id 
from `orderinfo` as a, `order` as b 
where a.order_id=b.id 
group by a.order_id
order by total DESC 
limit 10;

去重：

一、数据库中的去重操作（删除数据库中重复记录的SQL语句）主要有三种方法

（1）、rowid方法

（2）、group by 方法

（3）、distinct方法

1、用rowid方法

根据Oracle带的rowid属性，可以进行判断是否存在重复语句；

（1）、查出表1和表2中name相同的数据

Select * from table1 a

Where rowid !=(select max(rowid)

       from table2 b

  Where  a.name1 = b.name1

  And  a.name2 = b.name2......)

（2）、删除表1和表2 中name相同的所有数据

Delete from table1 a

Where rowid !=(select max(rowid)

 From table2 b

 Where  a.name1 = b.name1

 And  a.name2 = b.name2.......)

2、用group by方法

主要用于分组统计,一般都是使用在聚合函数中使用；

（1）、查数据

Select count(num), max(name) from student 列出表中的重复的记录数和学生名字的属性，

Group by num

Having count(num)>1 并按照num分组后找出表中num列出现次数大于一次的。

（2）、删除数据

Delete from student

Group by num

Having count(num)>1

//删除表中num列所有重复的数据

3、用distinct方法

一般用于比较小的表进行去重,会过滤掉多余的重复记录，返回不重复的记录或字段；

（1）、select distinct name

 From student

给查询出的SQL记录添加序号列，解决方法有以下两种

第一：

select ROW_NUMBER() OVER (ORDER BY a.字段 ASC) AS XUHAO,a.* from table a

--(table 为表名，字段为表a中的字段名)

第二：

select RANK()  OVER (ORDER BY a.字段 ASC) AS XUHAO,a.* from table a
--(table 为表名，字段为表a中的字段名)

mysql互换表中两列数据方法

1.创建表及记录用于测试

CREATE TABLE `product` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '产品id',
 `name` varchar(50) NOT NULL COMMENT '产品名称',
 `original_price` decimal(5,2) unsigned NOT NULL COMMENT '原价',
 `price` decimal(5,2) unsigned NOT NULL COMMENT '现价',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `product` (`id`, `name`, `original_price`, `price`) VALUES 
(NULL, '雪糕', '5', '3.5'), 
(NULL, '鲜花', '18', '15'), 
(NULL, '甜点', '25', '12.5'), 
(NULL, '玩具', '55', '45'), 
(NULL, '钱包', '285', '195');

mysql> select * from product;
+----+--------+----------------+--------+
| id | name   | original_price | price  |
+----+--------+----------------+--------+
|  1 | 雪糕   |           5.00 |   3.50 |
|  2 | 鲜花   |          18.00 |  15.00 |
|  3 | 甜点   |          25.00 |  12.50 |
|  4 | 玩具   |          55.00 |  45.00 |
|  5 | 钱包   |         285.00 | 195.00 |
+----+--------+----------------+--------+
5 rows in set (0.00 sec)

2.互换original_price与price的值
新手可能会使用以下方法进行互换

update product set original_price=price,price=original_price;

但这样执行的结果只会使original_price与price的值都是price的值，因为update有顺序的，
先执行original_price=price , original_price的值已经更新为price，
然后执行price=original_price，这里相当于没有更新。

执行结果：

mysql> select * from product;
+----+--------+----------------+--------+
| id | name   | original_price | price  |
+----+--------+----------------+--------+
|  1 | 雪糕   |           5.00 |   3.50 |
|  2 | 鲜花   |          18.00 |  15.00 |
|  3 | 甜点   |          25.00 |  12.50 |
|  4 | 玩具   |          55.00 |  45.00 |
|  5 | 钱包   |         285.00 | 195.00 |
+----+--------+----------------+--------+
5 rows in set (0.00 sec)

mysql> update product set original_price=price,price=original_price;
Query OK, 5 rows affected (0.00 sec)
Rows matched: 5  Changed: 5  Warnings: 0

mysql> select * from product;
+----+--------+----------------+--------+
| id | name   | original_price | price  |
+----+--------+----------------+--------+
|  1 | 雪糕   |           3.50 |   3.50 |
|  2 | 鲜花   |          15.00 |  15.00 |
|  3 | 甜点   |          12.50 |  12.50 |
|  4 | 玩具   |          45.00 |  45.00 |
|  5 | 钱包   |         195.00 | 195.00 |
+----+--------+----------------+--------+
5 rows in set (0.00 sec)

正确的互换方法如下：

update product as a, product as b set a.original_price=b.price, a.price=b.original_price where a.id=b.id;

执行结果：

mysql> select * from product;
+----+--------+----------------+--------+
| id | name   | original_price | price  |
+----+--------+----------------+--------+
|  1 | 雪糕   |           5.00 |   3.50 |
|  2 | 鲜花   |          18.00 |  15.00 |
|  3 | 甜点   |          25.00 |  12.50 |
|  4 | 玩具   |          55.00 |  45.00 |
|  5 | 钱包   |         285.00 | 195.00 |
+----+--------+----------------+--------+
5 rows in set (0.00 sec)

mysql> update product as a, product as b set a.original_price=b.price, a.price=b.original_price where a.id=b.id;
Query OK, 5 rows affected (0.01 sec)
Rows matched: 5  Changed: 5  Warnings: 0

mysql> select * from product;
+----+--------+----------------+--------+
| id | name   | original_price | price  |
+----+--------+----------------+--------+
|  1 | 雪糕   |           3.50 |   5.00 |
|  2 | 鲜花   |          15.00 |  18.00 |
|  3 | 甜点   |          12.50 |  25.00 |
|  4 | 玩具   |          45.00 |  55.00 |
|  5 | 钱包   |         195.00 | 285.00 |
+----+--------+----------------+--------+
5 rows in set (0.00 sec)

行转列：

--测试数据

  create   table   基础表(姓名   nvarchar(10),课程名   nvarchar(20),成绩   decimal(10,1))  

  insert   基础表      select   '王家喜','计算机基础',69.0  

  union     all       select   '王家喜','邓小平理论',74.0  

  union     all       select   '王家喜','英语（上）',86.0  

  union     all       select   '王家喜','普通逻辑学',91.0  

  union     all       select   '施春林','立法学教程',60.0  

  union     all       select   '施春林','经管原理'     ,73.0  

  union     all       select   '施春林','英语（上）',73.0  

  union     all       select   '施春林','普通逻辑学',90.0  

  go

--查询

declare @s varchar(8000),@i varchar(10)

  select   top   1   @s='',@i=count(*)  

  from   基础表   group   by   姓名   order   by   count(*)   desc  

  while   @i>0  

  select   @s=',[课程'+@i+']=max(case   when   id='+@i+'   then   课程名   else   ''''   end),

              [成绩'+@i+']=max(case   when   id='+@i+'   then   成绩   end)'+@s ,@i=@i-1  

  exec(' select   姓名,课程名,成绩,id=0   into   #t   from   基础表   order   by   姓名  

      declare   @i   int,@姓名   varchar(10)  

      update   #t   set   @i=case   when   @姓名=姓名   then   @i+1   else   1   end,id=@i,@姓名=姓名  

      select   姓名'+@s+'   from   #t   group   by   姓名')  

  go

--删除测试

  select * from   table_goods

  drop   table   基础表 

  select * from   table_goods

declare @str varchar(1000)

set @str='select id,visit '

select @str=@str+',min(case when goods='+ Cast(goods As Varchar) +' then goods_id else Null end) as goods_id'+ Cast(goods As Varchar)

+',min(case when goods='+ Cast(goods As Varchar) +' then name else Null end) as goods'+ Cast(goods As Varchar)

from table_goods group by goods

select @str=@str+' from table_goods group by id,visit order by id,visit'

print @str exec(@str)

select id,visit ,max(case when goods=1 then goods_id else Null end) as goods_id1,

               max(case when goods=1 then name else Null end) as goods1,

               max(case when goods=2 then goods_id else Null end) as goods_id2,

               max(case when goods=2 then name else Null end) as goods2,

             max(case when goods=3 then goods_id else Null end) as goods_id3,

              max(case when goods=3 then name else Null end) as goods3,

               max(case when goods=4 then goods_id else Null end) as goods_id4,

                 max(case when goods=4 then name else Null end) as goods4

from table_goods

group by  id,visit order by id,visit