什么是order by和group by之间的差异？ [英] Whats the different between order by and group by ?

问题描述

我正在学习SQL函数，所以我尝试这些语句：



SELECT`umeta_id` FROM`wp_usermeta`其中umeta_id< ; 7通过meta_key订购

，输出为：



6

4

2

3

1

5



我也尝试：



从`wp_usermeta`中选择`umeta_id`其中umeta_id< 7组meta_key

，输出为：

6

4

2

3

1

5



那么这两个功能有什么不同呢？当我应该使用group by或order by？



我尝试过：



我在上面的两个陈述中写的内容。

解决方案

好吧， ORDER 订单，而 GROUP ，... 群组！



尝试，例如：

  CREATE  表 PERSON（
 NAME  VARCHAR （ 20 ） NOT   NULL ，
 AGE  INT   NOT   NULL  
）; 
 
  INSERT   INTO  PERSON  VALUES （'  JOHN'， 40 ）; 
  INSERT   INTO  PERSON  VALUES  （'  JACK'， 20 ）; 
  INSERT   INTO  PERSON  VALUES  （'  JIM'， 40 ）; 
  INSERT   INTO  PERSON  VALUES  （'  JANE'， 20 ）; 

然后是以下差异：

  SELECT  AGE  FROM  PERSON  ORDER   BY  AGE ; 

和

  SELECT  AGE  FROM  PERSON  GROUP   BY  AGE; 

解决方案1实际上是一个非常好的示例，但您必须运行SQL才能看到它。



您的问题应该被标记为MySQL，就像您使用MS Sql Server运行第二个查询一样，您将收到错误消息

引用：

列'wp_usermeta.umeta_id'在选择列表中无效，因为它不包含在聚合函数或GROUP BY子句中。

这实际上可能会给你一个提示差异。



ORDER BY 更改订单返回行。根据您提供的列名称 SORTS 数据。



GROUP BY 将数据分组在一起 - 它聚合（组）信息。您通常会看到它与 SUM ， COUNT ， AVG 等



使用与解决方案1相同的表，此查询

  SELECT  [NAME]，AGE  FROM  PERSON  ORDER   BY  AGE; 

给出结果

 JACK 20 
 JANE 20 
 JOHN 40 
 JIM 40 

20在40之前出现所以所有排在年龄为20的行都排在第一位，接着是所有排在40岁的行。



此查询< pre lang =SQL> SELECT AGE，COUNT（[NAME]） FROM PERSON GROUP BY AGE;

给出结果

 20 2 
 40 2 

换句话说，有2行有Age = 20和2行有Age = 40




I'm learning THE SQL Function , so i try these statments :

SELECT `umeta_id` FROM `wp_usermeta` where umeta_id < 7 order by meta_key
and the output was :

6
4
2
3
1
5

and i also try :

SELECT `umeta_id` FROM `wp_usermeta` where umeta_id < 7 group by meta_key
and the output was :
6
4
2
3
1
5

so what is the different between these two functions ? and when i should use group by or order by ?

What I have tried:

what i write above in the two statement's .

解决方案

Well, ORDER orders while GROUP, ... groups!

Try, for instance:

CREATE TABLE PERSON(
   NAME VARCHAR (20)     NOT NULL,
   AGE  INT              NOT NULL
);

INSERT INTO PERSON VALUES ('JOHN', 40);
INSERT INTO PERSON VALUES ('JACK', 20);
INSERT INTO PERSON VALUES ('JIM', 40);
INSERT INTO PERSON VALUES ('JANE', 20);

and then the differnece between:

SELECT AGE FROM PERSON ORDER BY AGE;

and

SELECT AGE FROM PERSON GROUP BY AGE;




Solution 1 is actually a very good example, but you would have to run the SQL to see that.

Your question should have been tagged MySQL as if you run your second query with MS Sql Server you will get an error message

Quote:

Column 'wp_usermeta.umeta_id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

That might actually give you a hint as to the difference.

ORDER BY changes the order in which the rows are returned. It SORTS the data based on the column name you give it.

GROUP BY groups the data together - it aggregates (groups) information. You usually see it used with functions such as SUM, COUNT, AVG etc.

Using the same table as Solution 1, this query

SELECT [NAME],AGE FROM PERSON ORDER BY AGE;

gives the results

JACK	20
JANE	20
JOHN	40
JIM	40

20 comes before 40 so all of the rows with an Age of 20 are listed first, followed by all the rows with an Age of 40.

This query

SELECT AGE, COUNT([NAME]) FROM PERSON GROUP BY AGE;

gives the results

20	2
40	2

In other words there are 2 rows that have Age = 20 and 2 rows that have Age = 40




这篇关于什么是order by和group by之间的差异？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！