sqlgroupbywith

㈠ 為什麼在sql語句的GROUP BY裡面不可以使用別名

這個跟SQL語句執行順序有關系

1
2
3
4
5
6
7
8
9

(8)SELECT (9) DISTINCT (11) <TOP_specification> <select_list>
(1)FROM <left_table>
(3)<join_type> JOIN <right_table>
(2)ON <join_condition>
(4)WHERE <where_condition>
(5)GROUP BY <group_by_list>
(6)WITH {CUBE|ROLLUP}
(7)HAVING <having_condition>
(10)ORDER BY <order_by_list>

你看,GROUP BY執行的順序在 SELECT前面,所以他不可能使用到別名

㈡ mysql sql優化之 優化GROUP BY 和 DISTINCT

創建表tb_point 表

准備空的tb_box表

函數

編寫存儲過程，給tb_box表添加100萬條數據

修改關聯數據

好於

優於

在執行以下語句時會報錯：

前面在 https://www.jianshu.com/p/95e50fd017ea 文章中有提到這個問題，是直接修改sql_mode將 ONLY_FULL_GROUP_BY直接幹掉。但是在《高性能mysql》中有一段話是這樣的：

那麼既然指出不要直接修改 sql_mode，那麼我們應該如何讓沖突的GRUOPBY語句正確執行呢？

文中有提到，可以使用max()和min()函數來實現；但是這種方式使用max和min函數較真的人可能會說這樣寫的分組查詢有問題，確實如此。但是如果更加在乎查詢效率，這樣做也無可厚非。

如果，實在無法接受使用上面那種方式的話，可以這樣使用子查詢的方式來進行查詢：

書上對於這種方式有描述如下：
這樣寫更滿足關系理論，但是成本有點高，因為子查詢需要填充臨時表，而子查詢中創建的臨時表是沒有任何索引的。
作者認為這樣寫對性能有影響。

但是從我測得結果來看，子查詢的耗時反而更少。性能反而更佳。這個子查詢耗時0.4秒。而使用max方式耗時0.8秒。幾乎一倍。我的mysql版本是 5.7.22-log

為了解其中的原因，我們查看它的執行計劃：
可見，因為子查詢而產生了一層 DERIVED 臨時表，但是這個臨時表的Extra欄位有顯示 Using index、key裡面顯示自建索引。說明用到了索引。這是查詢性能可觀的一個重要原因吧；

另外我分別使用 SHOW PROFILE命令查看各部分耗時，對比之下。沒看到有哪部分耗時差別特別大，使用JOIN、MAX 耗時比上子查詢耗時都差不多是1倍

有些時候對一沒有建立索引的欄位，進行GRUOP BY時。會產生Using filesort 文件內排序。因為GRUOP BY是在排序的基礎上進行分組的。

如下面sql：

如果業務上不對排序有要求。那麼就可以禁止GRUOP BY的排序：

這樣就把Using filesort給幹掉了！ 執行時間 1.237

當然，多數情況是多排序有要求的。此時也可以在GRUOP BY後面使用DESC和ASC關鍵字，使分組的結果集按需要的方向排序。如下：

分組查詢的一個變種就是要求mysql對分組結果再進行一次超級聚合。可以使用GROUP BY WITH ROLLUP 來實現這種邏輯，但可能性能不佳。因為通過查詢計劃分析出它是使用 Using temporary; Using filesort 來實現的。

使用WITH ROLLUP，查詢時間2.531秒。不使用0.774 秒。

1、所以，很多時候。我們在應用程序中做超級聚合是最好的！

2、當然也可使用UNION ALL 來實現：

3、還可以通過FROM子句嵌套使用子查詢：

㈢ sql語句中的groupby是什麼意思

一、sql語句中的groupby是：GROUP BY 語句用於結合合計函數，根據一個或多個列對結果集進行分組。

二、sql語句group by的高級語法：

1、寫上一個創建表的 sql語句. 當然,我們也可以用設計圖創建；

㈣ SQL中的Group By的查詢過程多列分組的查詢過程是怎樣的

Group By子句
Group By子句可以將表的行劃分為不同的組。分別總結每個組，這樣就可以控制想要看見的詳細信息的級別。

語法：

[ Group By [ ALL ] Group_By_expression[ ,...n ]

[ WITH { CUBE | ROLLUP } ] ]

參數說明：

ALL：包含所有組和結果集，甚至包含那些任何行都不滿足WHERE子句指定的搜索條件的組和結果集。如果指定了ALL，將對組中不滿足搜索條件的匯總列返回空值。不能用CUBE或ROLLUP運算符指定ALL。如果訪問遠程表的查詢中有WHERE子句，則不支持Group By ALL操作。

Group_By_expression：對其執行分組的表達式。Group_By_expression也稱為分組列。Group_By_expression可以是列或引用列的非聚合表達式。在選擇列表內定義的列的別名不能用於指定分組列。對於不包含CUBE或ROLLUP的Group By子句，Group_By_ expression的項數受查詢所涉及的Group By列的大小、聚合列和聚合值的限制。該限制從8060位元組的限制開始，對保存中間查詢結果所需的中間級工作表有8060位元組的限制。如果指定了CUBE或ROLLUP，則最多隻能有10個分組表達式。

CUBE：指定在結果集內不僅包含由Group By提供的正常行，還包含匯總行。在結果集內返回每個可能的組和子組組合的Group By匯總行。Group By匯總行在結果中顯示為NULL，但可用來表示所有值。使用GroupING函數確定結果集內的空值是否是Group By匯總值。結果集內的匯總行數取決於Group By子句內包含的列數。Group By子句中的每個操作數（列）綁定在分組NULL下，並且分組適用於所有其他操作數（列）。由於CUBE返回每個可能的組和子組組合，因此，不論指定分組列所使用的是什麼順序，行數都相同。

ROLLUP：指定在結果集內不僅包含由Group By提供的正常行，還包含匯總行。按層次結構順序，從組內的最低級別到最高級別匯總組。組的層次結構取決於指定分組列時所使用的順序。更改分組列的順序會影響在結果集內生成的行數。

使用Group By子句的注意事項。

（1）在SELECT子句的欄位列表中，除了聚集函數外，其他所出現的欄位一定要在Group By子句中有定義才行。例如「Group By A，B」，那麼「SELECT SUM（A），C」就有問題，因為C不在Group By中，但是SUM（A）是可以的。

（2）SELECT子句的欄位列表中不一定要有聚集函數，但至少要用到Group By子句列表中的一個項目。例如「Group By A，B，C」，則「SELECT A」是可以的。

（3）在SQL Server中text、ntext和image數據類型的欄位不能作為Group By子句的分組依據。

（4）Group By子句不能使用欄位別名。
1．按單列進行分組

Group By子句可以基於指定某一列的值將數據集合劃分為多個分組，同一組內所有記錄在分組屬性上具有相同值。

示例：

把「student」表按照「性別」這個單列進行分組。在查詢分析器中輸入的SQL語句如下：

use student

select 性別

from student

Group By 性別

但仍然要強調SELECT子句必須與Group By後的子句或者是分組函數列相一致。

例如，由於下列查詢中「姓名」列既不包含在Group By子句中，也不包含在分組函數中，所以是錯誤的。錯誤的SQL語句如下：

use student select 姓名,性別 from student Group By 性別

例如，在「grade」表中，按「學期」分組查詢。SQL語句如下：

use studnet select 學期 from grade Group By 學期

2．按多列進行分組

Group By子句可以基於指定多列的值將數據集合劃分為多個分組。

示例：

在「student」表中，按照「性別」和「年齡」列進行分組。在查詢分析中輸入的SQL語句如下：

use student

select 性別,年齡

from student

Group By 性別,年齡

在「student」表中，首先按照「性別」分組，然後再按照「年齡」分組。

再舉一個例子，例如，在「grade」表中，按照「學號」和「課程代號」列進行分組。SQL語句如下：

use student

select 學號,課程代號 from grade Group By 學號,課程代號

按多列進行分組時有NULL組的是如何處理的。當表按多列進行分組時有NULL組，這時NULL被作為一個特定值處理，就像其他任何值一樣。也就是說，如果在某個分組列中存在兩個NULL，則按它們有相同的值那樣處理，並將它們放在相同的組中。

示例：

在「grade」表中，按「學期」和「課程代號」列進行分組。在查詢分析器中輸入的SQL語句如下：

use student

select 學期,課程代號

from grade

Group By 學期,課程代號

3．與聚集函數一起使用

Group By子句是經常與聚集函數一起使用。如果SELECT子句中包含聚集函數，則計算每組的匯總值，當用戶指定Group By時，選擇列表中任一非聚集表達式內的所有列都應包含在Group By列表中，或者Group By表達式必須與選擇列表表達式完全匹配。

示例：

在「student」表中，分別求男女生的平均年齡。在查詢分析器中輸入的SQL語句如下：

use student

select 性別,avg(年齡) as 平均年齡

from student

Group By 性別

例如，在「student」表中，分別求有多少個男生和女生。SQL語句如下：

use student

select 性別,count(性別) as 人數 from student Group By 性別

說明：關於聚合函數的詳細講解可參閱9.2.1節。

4．與HAVING子句一起使用

HAVING子句對Group By子句選擇出來的結果進行再次篩選，最後輸出符合HAVING子句中條件的記錄。HAVING子句的語法與WHERE子句的語法相類似，惟一不同的是HAVING子句中可以包含聚合函數。

語法：

[HAVING <search_condition>]

參數說明：

<search_condition>：指定組或聚合應滿足的搜索條件。當HAVING與Group By ALL一起使用時，HAVING 子句替代ALL。

示例：

在「student」表中，按「性別」分組求平均年齡，並且查詢其平均年齡大於21的學生信息。在查詢分析器中輸入的SQL語句如下：

use student

select avg(年齡), 性別

from student

Group By 性別

having avg(年齡)>21

在「grade」表中，按「學期」分組求平均成績，並且查詢「平均成績」大於93的課程信息。在查詢分析器中輸入的SQL語句如下：
+1 已贊過

㈤ sql中group by with rollup怎麼用

CREATE TABLE #test (
Name VARCHAR(10),
[procere] CHAR(1),
model VARCHAR(5),
quantity INT
);

INSERT INTO #test
SELECT 'A', '1', 'φ50', 500 UNION ALL
SELECT 'A', '1', 'φ50', 600 UNION ALL
SELECT 'A', '1', 'φ100', 500 UNION ALL
SELECT 'A', '2', 'φ50', 700 UNION ALL
SELECT 'A', '2', 'φ100', 200 UNION ALL
SELECT 'B', '1', 'φ50', 1000;

SELECT
CASE
WHEN GROUPING(Name) = 1 THEN '總計'
WHEN GROUPING(Name) = 0 AND GROUPING([procere]) = 1 THEN Name + '合計'
WHEN GROUPING(Name) = 0 AND GROUPING([procere]) = 0 AND GROUPING([model]) = 1 THEN Name + '的' + [procere] + '小計'
ELSE Name
END AS Name,
CASE
WHEN GROUPING([model]) = 1 THEN ''
ELSE [procere]
END AS [procere],
ISNULL(model, '') AS model,
SUM(quantity) AS quantity
FROM
#test
group by
Name,
[procere],
model with rollup;

Name procere model quantity
----------------- --------- ----- -----------
A 1 φ100 500
A 1 φ50 1100
A的1小計 1600
A 2 φ100 200
A 2 φ50 700
A的2小計 900
A合計 2500
B 1 φ50 1000
B的1小計 1000
B合計 1000
總計 3500

(11 行受影響)

SQL Server 2008 Express 下測試通過.

㈥ SQL如何能在查詢時，將一列數據加和，但返回數據時包含明細，也包含這個加合的數據

在sqlserver中這個是靠的group by的子句完成此功能的，比如with cube或者是with
rollup。