【Mysql】
group_concat()函數總結
group_concat(),手冊上說明:該函數傳回帶有來自一個組的連接配接的非NULL值的字元串結果。
比較抽象,難以了解。
通俗點了解,其實是這樣的:group_concat()會計算哪些行屬于同一組,将屬于同一組的列顯示出來。要傳回哪些列,由函
數參數(就是字段名)決定。分組必須有個标準,就是根據group by指定的列進行分組。
group_concat函數應該是在内部執行了group by語句,這是我的猜測。
1.測試語句:SELECT group_concat(town) FROM `players` group by town
結果去查找town中去查找哪些值是一樣的,如果相等,就全部列出來,以逗号分割進行列出,如下:
group_concat(town)
北京,北京
長沙
2.測試:SELECT group_concat( town )
FROM players
結果:
group_concat(town)
長沙,北京,北京,
上面是否可以證明,group_concat隻有與group by語句同時使用才能産生效果? 下面進行了實際測驗
3.測試常量對group_concat()的配置影響:
SET @@GROUP_CONCAT_MAX_LEN=4
手冊中提到設定的文法是這樣的:
SET [SESSION | GLOBAL] group_concat_max_len = val;
兩種有什麼差別?
SET @@global.GROUP_CONCAT_MAX_LEN=4;
global可以省略,那麼就變成了:SET @@GROUP_CONCAT_MAX_LEN=4;
4.使用語句 SELECT group_concat(town) FROM `players`。結果得到:
group_concat(town)
長沙,北京,長沙,北京
結論:group_concat()函數需要與group by語句在一起使用,才能得到需要的效果。
原因可以這樣了解:group_concat()得到是屬于x組的所有成員(函數裡面列參數指定需要顯示哪些字段)。x組從哪裡來?如
果沒有group by進行指定,那麼根本不知道group_concat()根據哪個分組進行顯示出成員。 是以,像上面沒有group by子句
的時候,就顯示了長沙和北京。
實際中什麼時候需要用到這個函數?
假如需要查詢的結果是這樣:左邊顯示組名,右邊想顯示該組别下的所有成員資訊。用這個函數,就可以省去很多事情了。
另外,假如我這樣使用:SELECT group_concat( name, sex ) FROM `players` town。意義不大。group_concat()指定一個
列是最好的情況。如果指定了多個列。那麼顯示結果類似這樣:
group_concat(name,sex)
王滔男,王小明男,劉惠女,舒明女。
【sqlserver】
很多人知道ISNULL函數,但是很少人知道Coalesce函數,人們會無意中使用到Coalesce函數,并且發現它比ISNULL更加強大,其實到目前為止,這個函數的确非常有用,本文主要講解其中的一些基本使用:
首先看看聯機叢書的簡要定義:
傳回其參數中第一個非空表達式
文法
COALESCE ( expression [ ,...n ] )
如果所有參數均為 NULL,則 COALESCE 傳回 NULL。至少應有一個 Null 值為 NULL 類型。盡管 ISNULL 等同于 COALESCE,但它們的行為是不同的。包含具有非空參數的 ISNULL 的表達式将視為 NOT NULL,而包含具有非空參數的 COALESCE 的表達式将視為 NULL。在 SQL Server 中,若要對包含具有非空參數的 COALESCE 的表達式建立索引,可以使用 PERSISTED 列屬性将計算列持久化,如以下語句所示:
[sql] view plain copy print ?
- CREATE TABLE #CheckSumTest
- (
- ID int identity ,
- Num int DEFAULT ( RAND() * 100 ) ,
- RowCheckSum AS COALESCE( CHECKSUM( id , num ) , 0 ) PERSISTED PRIMARY KEY
- );
下面來看幾個比較有用的例子:
首先,從MSDN上看看這個函數的使用方法,coalesce函數(下面簡稱函數),傳回一個參數中非空的值。如:
[sql] view plain copy print ?
- SELECT COALESCE(NULL, NULL, GETDATE())
由于兩個參數都為null,是以傳回getdate()函數的值,也就是目前時間。即傳回第一個非空的值。由于這個函數是傳回第一個非空的值,是以參數裡面必須最少有一個非空的值,如果使用下面的查詢,将會報錯:
[sql] view plain copy print ?
- SELECT COALESCE(NULL, NULL, NULL)
![](https://img.laitimes.com/img/9ZDMuAjOiMmIsIjOiQnIsIyZuBnL0cDNx8lM3UTO5UTN1MTMvwlNx8CXyEjMxAjMvw1ckF2bsBXdvwFdl5mLuR2cj5Set1yZtl2Lc9CX6MHc0RHaiojIsJye.png)
然後來看看把函數應用到Pivot中,下面語句在AdventureWorks 資料庫上運作:
[sql] view plain copy print ?
- SELECT Name
- FROM HumanResources.Department
- WHERE ( GroupName= 'Executive Generaland Administration' )
會得到下面的結果:
如果想扭轉結果,可以使用下面的語句:
[sql] view plain copy print ?
- DECLARE @DepartmentName VARCHAR(1000)
- SELECT @DepartmentName = COALESCE(@DepartmentName, '') + Name + ';'
- FROM HumanResources.Department
- WHERE ( GroupName= 'Executive Generaland Administration' )
- SELECT @DepartmentName AS DepartmentNames
使用函數來執行多條SQL指令:
當你知道這個函數可以進行扭轉之後,你也應該知道它可以運作多條SQL指令。并且使用分号來區分獨立的操作。下面語句是在Person架構下,有名字為Name的列的值:
[sql] view plain copy print ?
- DECLARE @SQL VARCHAR(MAX)
- CREATE TABLE #TMP
- (Clmn VARCHAR(500),
- Val VARCHAR(50))
- SELECT @SQL=COALESCE(@SQL,'')+CAST('INSERT INTO #TMP Select ''' + TABLE_SCHEMA + '.' + TABLE_NAME + '.'
- + COLUMN_NAME + ''' AS Clmn, Name FROM ' + TABLE_SCHEMA + '.[' + TABLE_NAME +
- '];' AS VARCHAR(MAX))
- FROM INFORMATION_SCHEMA.COLUMNS
- JOIN sysobjects B ON INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = B.NAME
- WHERE COLUMN_NAME = 'Name'
- AND xtype = 'U'
- AND TABLE_SCHEMA = 'Person'
- PRINT @SQL
- EXEC(@SQL)
- SELECT * FROM #TMP
- DROP TABLE #TMP
還有一個很重要的功能:。當你嘗試還原一個庫,并發現不能獨占通路時,這個功能非常有效。我們來打開多個視窗,來模拟一下多個連接配接。然後執行下面的腳本:
[sql] view plain copy print ?
- DECLARE @SQL VARCHAR(8000)
- SELECT @SQL = COALESCE(@SQL, '') + 'Kill ' + CAST(spid AS VARCHAR(10)) + '; '
- FROM sys.sysprocesses
- WHERE DBID = DB_ID('AdventureWorks')
- PRINT @SQL --EXEC(@SQL) Replace the print statement with exec to execute
結果如下:
然後你可以把結果複制出來,然後一次性殺掉所有session。