mySql 查询最大、最小值及子查询

素颜马尾好姑娘i 2022-03-18 03:40 392阅读 0赞

准备数据：

创建表：

create table gpChatRecord(  
id int primary key auto\_increment,  
content varchar(50),  
date\_time datetime);

插入单条数据：

insert into gpChatRecord(content, date\_time) values(1, current\_time());

插入多条数据：

insert into gpChatRecord(content, date\_time) values(2, current\_time()), (3, current\_time()), (4, current\_time()), (5, current\_time()), (6, current\_time()), (7, current\_time()), (8, current\_time()), (9, current\_time()),(10, current\_time());

查询全部数据

select \* from gpChatRecord;

<table> 
 <tbody> 
  <tr> 
   <td> <p>id</p> </td> 
   <td>content</td> 
   <td>date_time</td> 
  </tr> 
  <tr> 
   <td>1</td> 
   <td>1</td> 
   <td>2019-02-18 15:09:53</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>2</td> 
   <td>2019-02-18 15:10:18</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>3</td> 
   <td>2019-02-18 15:10:22</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>4</td> 
   <td>2019-02-18 15:24:37</td> 
  </tr> 
  <tr> 
   <td>5</td> 
   <td>5</td> 
   <td>2019-02-18 15:24:37</td> 
  </tr> 
  <tr> 
   <td>6</td> 
   <td>6</td> 
   <td>2019-02-18 15:24:37</td> 
  </tr> 
  <tr> 
   <td>7</td> 
   <td>7</td> 
   <td>2019-02-18 15:24:37</td> 
  </tr> 
  <tr> 
   <td>8</td> 
   <td>8</td> 
   <td>2019-02-18 15:24:37</td> 
  </tr> 
  <tr> 
   <td>9</td> 
   <td>9</td> 
   <td>2019-02-18 15:24:37</td> 
  </tr> 
  <tr> 
   <td>10</td> 
   <td>10</td> 
   <td>2019-02-18 15:24:37</td> 
  </tr> 
 </tbody> 
</table>

where 查询

select id, content from gpChatRecord where id < 8;

<table> 
 <tbody> 
  <tr> 
   <td>id</td> 
   <td>content</td> 
  </tr> 
  <tr> 
   <td>1</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>2</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>3</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>4</td> 
  </tr> 
  <tr> 
   <td>5</td> 
   <td>5</td> 
  </tr> 
  <tr> 
   <td>6</td> 
   <td>6</td> 
  </tr> 
  <tr> 
   <td>7</td> 
   <td>7</td> 
  </tr> 
 </tbody> 
</table>

max函数查询最大值

select max(id) from gpChatRecord where id < 8;

\# max(id)  
'7'

min函数查询最小值

select min(id) from gpChatRecord where id > 8;

\# min(id)  
'9'

子查询：

select \* from gpChatRecord where id in (select id from gpChatRecord where id < 8);

<table> 
 <tbody> 
  <tr> 
   <td>id</td> 
   <td>content</td> 
   <td>date_time</td> 
  </tr> 
  <tr> 
   <td>1</td> 
   <td>1</td> 
   <td>2019-02-18 15:09:53</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>2</td> 
   <td>2019-02-18 15:10:18</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>3</td> 
   <td>2019-02-18 15:10:22</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>4</td> 
   <td>2019-02-18 15:24:37</td> 
  </tr> 
  <tr> 
   <td>5</td> 
   <td>5</td> 
   <td>2019-02-18 15:24:37</td> 
  </tr> 
  <tr> 
   <td>6</td> 
   <td>6</td> 
   <td>2019-02-18 15:24:37</td> 
  </tr> 
  <tr> 
   <td>7</td> 
   <td>7</td> 
   <td>2019-02-18 15:24:37</td> 
  </tr> 
 </tbody> 
</table>