English | 简体中文 | 繁體中文 | Русский язык | Français | Español | Português | Deutsch | 日本語 | 한국어 | Italiano | بالعربية
Today I saw this article and felt that the content was quite a lot, but it was quite disorganized, and it was really not easy to organize. The editor of the呐喊 tutorial simply organized it, hoping everyone can make do with it
SQL statement for grouping total and total after grouping
1) Want to get the grouping total and total at one time, sql:
SELECT Grouping field FROM Table
GROUP BY Grouping field
compute sum(COUNT(*))
2) Grouping Total 1:
SELECT COUNT(*) FROM (SELECT Grouping field FROM Table GROUP BY Grouping field ) Alias
3) Grouping Total 2:
SELECT COUNT(*) FROM (SELECT distinct Grouping field FROM Table) Alias
4) Count the number of types after grouping:
Example 1: Grouping Total
SELECT JSSKQK_JGH FROM SJ_JSSKQK WHERE JSSKQK_JGH IN (SELECT JSJBXX_JGH FROM SJ_JSJBXX WHERE JSJBXX_JSLXM1=1) GROUP BY JSSKQK_JGH HAVING ((SUM(JSSKQK_SSKCXS1) /40)>5)
The above statement can already meet the requirements for grouping. Suppose there are 3 records after execution, how can we calculate this COUNT value?
select count(*) from ( SELECT JSSKQK_JGH FROM SJ_JSSKQK WHERE JSSKQK_JGH IN (SELECT JSJBXX_JGH FROM SJ_JSJBXX WHERE JSJBXX_JSLXM1=1) GROUP BY JSSKQK_JGH HAVING ((SUM(JSSKQK_SSKCXS1) /40)>5) ) t
Esempio 2: [PL/SQL] Come ottenere il record con la data più grande del gruppo dopo la raggruppamento
TABLE: A
A B C D
1 2001/01/01 1 1
1 2001/12/12 2 2
3 2002/01/01 3 3
3 2003/12/12 4 4
Come posso ottenere i dati con la data più grande di ciascun gruppo, raggruppando per colonna A?
1 2001/12/12 2 2
3 2003/12/12 4 4
Il mio metodo ingenuo:
SELEZIONA * FROM A WHERE (A, B) IN( SELECT A, MAX(B) FROM A GROUP BY A )
C'è un metodo migliore?
1, select * from a out
where b = (select max(b) from a in
where in.a = out.a)
2, Select * from
(select a, row_number() over (partition by a
order by b desc) rn
from a)
where rn=1
3, Select a, b, c, d from
(select a, b, c, d, row_number() over (partition by a
order by b desc) rn
from a)
where rn=1
4, select A, B, C, D from test
where rowid in
(
select rd from
(
select rowid rd, rank() over(partition A order by B desc)rk from test
where rk=1
)
)
Esempio 3: metodo per ottenere il primo record di ogni gruppo utilizzando SQL
Usare il database Northwind
Prima di cercare la tabella Employees
Risultato della query:
La colonna city contiene solo 5 città
Usare ROW_NUMBER() OVER(PARTITION BY COL1 ORDER BY COL2) per raggruppare prima di notare: secondo COL1 raggruppare, all'interno del raggruppamento secondo COL2 ordinare, e il valore calcolato da questa funzione rappresenta il numero di sequenza ordinato all'interno di ogni gruppo (unico e contiguo all'interno del gruppo).
La query SQL è:
select EmployeeID,LastName,FirstName,Title,TitleOfCourtesy,City,ROW_NUMBER() over(partition by City order by EmployeeID) as new_index
from Employees
Immagine del risultato dell'esecuzione:
Si può vedere che è raggruppato per City e ordinato per EmployeeID.
Seleziona il primo record del gruppo
Esegui la query:
seleziona * da
(select EmployeeID,LastName,FirstName,Title,TitleOfCourtesy,City,ROW_NUMBER() over(partition by City order by EmployeeID) as new_index
from Employees) a where a.new_index=1
Immagine del risultato dell'esecuzione:
Esempio 4: come ottenere il primo record di ogni gruppo dopo il raggruppamento SQL
Eric red 20
eric blue 30
andy red 10
andy blue 5
Ad esempio, ottenere solo le registrazioni in grassetto.
1, dichiarare @fTable come tabella (fName varchar(10), fColor varchar(10), fOrder int)
insert into @fTable values('Eric', 'red', 20)
insert into @fTable values('eric', 'blue', 30)
insert into @fTable values('andy', 'red', 10)
insert into @fTable values('andy', 'blue', 5)
-- Solo ottenere rosso
seleziona * da @fTable dove fColor = 'red'
-- Ogni fColor prende un record (per fOrder in ordine crescente)
seleziona * da @fTable A dove fName = (seleziona top 1 fName da @fTable dove fColor = A.fColor ordine per fOrder )
-- Ogni fColor prende un record (per fOrder in ordine inverso)
seleziona * da @fTable A dove fName = (seleziona top 1 fName da @fTable dove fColor = A.fColor ordine per fOrder decrescente)
2, SQL2005 e versioni successive
seleziona * da (seleziona *, row=row_number()over(partition by Colore order by Colore) da tabella1)t dove row=1 e colore='xx'--aggiungi condizione
SQL2000 con top 1
Esempio 5: una sola query SQL per raggruppare e limitare il numero di record per ciascun gruppo
Se voglio ottenere un insieme di risultati come questo: raggruppare e limitare il numero di record per ciascun gruppo, posso farlo con una sola query SQL?
Ad esempio, se voglio trovare i primi tre della classe per ogni materia agli esami finali degli studenti, come posso scrivere con una sola query SQL?
Struttura della tabella [TScore]
codice Codice char
materia Materia int
punteggio Punteggio int
Puoi scriverlo così:
SELEZIONA [codice]
, [materia]
, [punteggio]
DA (
SELEZIONA *
, RANK() OVER(PARTITION BY materia ORDER BY punteggio DESC) come Riga
DA TScore
) come a
WHERE Riga <= 3 ;
Esempio 6: SQL per ottenere il primo record di ogni gruppo
Query SQL per ottenere le righe in grassetto dei dati di pseudo
ID, Nome, ItemID, Prezzo, CreataIl
1 a 1 10.00 xxx1
2 a 1 12.00 xxx2
3 b 1 9.00 xxx1
4 b 1 11.50 xxx2
5 c 1 20.00 xxx1
6 a 2 21.00 xxx1
7 a 2 23.00 xxx2
8 b 2 35.00 xxx1
9 c 2 31.00 xxx1
10 c 2 30.50 xxx2
Ottenere il primo record di ogni raggruppamento, selezionando il Price più alto quando ItemID ha più record
--sql2000
select *
from tbname k
where not exists(select * from tbname where
name=k.name and ITemID=K.ITemID and k.price<price
)
--sql2005
select ID,Name,ItemID,Price,CreatedOn from (select *,rn=ROW_NUMBER()over(PARTITION by name,ITemID order by price desc) from tb) k where k.rn=1
Esempio 7: SQL per prelevare il primo record dopo il raggruppamento
Condivisione
La struttura della tabella è la seguente:
Il campo A, B, C
Il valore è a1, b1, c1
a2, b2, c2
a2, b3, c3
a3, b4, c4
a3, b5, c5
Il risultato desiderato è raggruppato in base al campo A e si preleva il primo record di ogni raggruppamento, come segue:
A, B, C
Il valore è a1, b1, c1 --Il primo record del raggruppamento a1.
a2, b2, c2 --Il primo record del raggruppamento a2.
a3, b4, c4 --Il primo record del raggruppamento a3.
select * from tabella tem where c=(select top 1 c from tabella where a=tem.a)
L'elenco dei dati attuali è come segue:
zj th bj
------------- -------- -------------
03106666666 00001 03101111111
13711111111 00001 031122222222
03108898888 950000
031177778777 950000
031155955555 00001 031187888876
Note: If th is 950000, then bj is empty, if th is 00001, then bj is not empty.
1、bj分组
select substr(bj,1,4) as 部署区号, count(*) as 呼叫总量 from call
group by substr(bj,1,4);
Execution result
部署区号 呼叫总量
------------
0310 1
0311 2
1
2、zj分组，条件是th为950000的记录
select substr(zj,1,4) as 部署区号, count(*) as 呼叫总量 from call
where th=950000
group by substr(zj,1,4);
Execution result:
部署区号 呼叫总量
------------
0310 1
0311 1
Can one statement achieve the following result:
部署区号 呼叫总量
------------
0310 2
0311 3
Note: To get the result, add the rows corresponding to 1 and 2.
union together and sum
select 部署区号, sum(呼叫总量) from
(select substr(bj,1,4) as 部署区号, count(*) as 呼叫总量 from call
group by substr(bj,1,4))
union all
(select substr(zj,1,4) as 部署区号, count(*) as 呼叫总量 from call
where th=950000
group by substr(zj,1,4))
group by 部署区号;
Questo dovrebbe essere eseguito in Oracle
seleziona
decode(th,'950000',substr(zj,1,4),substr(bj,1,4)) come prefisso_comune,
count(*) come totale_chiamate
da
chiama
group by
decode(th'950000',substr(zj,1,4),substr(bj,1,4))
decode(condizione, valore1, valore tradotto1, valore2, valore tradotto2,...valore n, valore tradotto n, valore predefinito)
Il significato di questa funzione è il seguente:
SE condizione=valore 1 THEN
RETORNA(valore tradotto 1)
ELSIF condizione=valore 2 THEN
RETORNA(valore tradotto 2)
......
ELSIF condizione=valore n THEN
RETORNA(valore tradotto n)
ALTRIMENTI
RETORNA(valore predefinito)
FINE SECONDA
Esempio 8: Raggruppa i record in SQL Server2005/2008 e ottieni i primi N record di ogni gruppo
Supponiamo di avere una tabella, la query SQL è la seguente:
CREATE TABLE [dbo].[scan](
[km] [int] NULL,
[kh] [int] NULL,
[cj] [int] NULL
) ON [PRIMARY]
Dove km è il numero di materia, kh è il numero di candidato, cj è il punteggio. Ora raggruppa km e kh e ottieni le prime 2 registrazioni di ogni gruppo (ordinato per cj da alto a basso). L'idea di base è aggiungere una colonna di numero di sequenza a ogni gruppo e utilizzare where per prendere il numero di sequenza meno o uguale a 2. La query SQL è la seguente:
seleziona * da
(
seleziona a.km,a.kh,cj,row_number() over(partition by a.km ordina per a.km,a.cj decrescente) n
da
(seleziona km,kh,SUM(cj) cj da scan raggruppa per km,kh) a
) b dove n<=2 ordina per km, cj decrescente
L'insieme di risultati finale è illustrato nella figura seguente.
Esempio 9: Come implementare una query SQL per raggruppare e selezionare i primi N record
In tabellaA, raggruppa per campoB, ordina per campoC e seleziona le prime tre registrazioni di ogni gruppo. Il risultato della query deve includere tutti i campi. Come scrivere una query SQL? Anche se la query SQL può essere eseguita, a causa della grande quantità di dati, richiede troppo tempo. C'è un metodo per estrarre record raggruppati senza utilizzare join di tabelle? Grazie!
select *
from tabellaA come t1
where chiave_principale in(
select top 3 主键
from 表A as t2
where t1.B=t2.B
order by t2.C)
Commento (commento nascosto)
Risposta 1
Autore: 邹建
select id=identity(int,1,1),b, 主键 into # from 表A order by B,C
select a.*
from 表A a, # b,(select id1=min(id),id2=min(id)+2 from # group by b)c
where a.主键=b.主键
and b.id between c.id1 and c.id2
drop table #
Risposta 2
Autore: aierong
Quante modalità hai per trovare i primi 2 della categoria per ogni gruppo? (MS SQL2000)
create table abc(
i nvarchar(10),
ii int,
iii int,
iiii int,
price money)
Vai
insert into abc
select 'b',1,2,1,11
union all
select 'b',211,2,1,211
union all
select 'a',21,2,1,311
union all
select 'd',41,42,1,411
union all
select 'd',41,42,1,511
union all
select 'd',41,42,1,611
union all
select 'e',1,2,1,11
union all
select 'e',71,2,1,31
union all
select 'e',61,2,1,911
union all
select 'e',771,2,1,1
go
Il risultato richiesto è:
Cerca i primi 2 record con il prezzo più alto per ogni gruppo i
i ii iii iiii price
---------- ----------- ----------- ----------- ---------------------
a 21 2 1 311.0000
b 1 2 1 11.0000
b 211 2 1 211.0000
d 41 42 1 511.0000
d 41 42 1 611.0000
e 71 2 1 31.0000
e 61 2 1 911.0000
1.
select *
from abc a
where (
select count from abc b
where a.i=b.i and b.price>a.price)<2
order by i,price
Query di connessione, giudicare la quantità
2.
select i,ii,iii,iiii,price
from (
select (select isnull(sum(1),0)+1 from abc b where a.i=b.i and a.price<b.price) ids,*
from abc a) tem
where ids<3
order by i,price
Creare una tabella interna, ordinare ogni gruppo nella tabella interna tem e inserire il numero di ordinamento nella nuova colonna ids
3.
declare @looptime int
declare @count int
declare @i nvarchar(10)
/Definire la variabile di tabella @abc, e il tipo di colonna della tabella ABC/
declare @abc table(
i nvarchar(10),
ii int,
iii int,
iiii int,
price money)
declare @tem table(
ids int identity,
class nvarchar(10))
/Queryare tutte le gruppi nella tabella ABC e temporaneamente esistere nella variabile di tabella @tem/
insert into @tem(class)
select i
from abc
group by i
/Calcolare il numero di righe nella variabile di tabella @tem/
select @count=@@rowcount
/Assegnare il valore iniziale 1 alla variabile di ciclo @looptime/
select @looptime=1
while(@looptime<=@count)
begin
/Assegnare il nome del gruppo a variabile @i/
select @i=class
from @tem
where ids=@looptime
/Inserire i primi 2 di ogni gruppo nella variabile di tabella @abc/
insert into @abc
select top 2 *
from abc
where i=@i
order by price desc
/Aggiungere 1 alla variabile di ciclo @looptime/
select @looptime=@looptime+1
end
/Visualizzare i risultati/
select *
from @abc
order by i,price
4.
Utilizzare il cursore per elaborare
Il metodo è simile al mio metodo 3, potete provarlo voi stessi
Ho scritto 4, non so se avete altri metodi buoni, potete scambiarli, grazie.
L'ho usato oggi, utilizzando questo metodo è possibile risolvere un problema di eliminazione di record duplicati
Certo, la tabella deve avere un indice univoco, guardiamo attentamente il seguente codice
Delete From dbo.TB_WorkflowTask a
WHERE ItemID Not in( select top 1 ItemID from TB_WorkflowTask where TaskName=a.TaskName And EmpID = a.EmpID And BillTypeID =a.BillTypeID And BillID = a.BillID And Status =a.Status AND WFStatus =a.WFStatus )
Attenzione: Può essere utilizzato In o Not in, non può essere utilizzato Exists o Not Exists, perché? Pensi un po'
Esempio 10: Come ottenere il valore dell'ultimo record raggruppato?
È ancora confuso, riorganizziamolo di nuovo:
Riorganizziamo il formato:
Esiste una tabella Log: Day In Out Current
2012.4.5 10 0 10
2012.4.5 0 5 5
2012.4.6 30 20 15
2012.4.6 0 3 12
………………………………………………
Spero che venga visualizzato come
、
2012.4.5 10 5 5
2012.4.6 30 23 12
Codice SQL
con tb as(
select [day],sum([in]) as [in],sum(out) as out,sum([in])-sum(out) as [current],rank() over( order by [day]) as row from [log] group by [day]
)
select [day],[in],out,(select sum([current]) from tb b where b.row<=a.row)[current] from tb a
Codice SQL
2012.4.5 10 5 5
2012.4.6 30 23 12
Codice SQL
--> Dati di test: [Log]
Se l'oggetto_id('[Log]') non è nullo, elimina la tabella [Log]
crea tabella [Log]([Giorno] data,[Entrata] int,[Uscita] int,[Corrente] int)
inserisci [Log]
seleziona '2012.4.5',10,0,10 union all
seleziona '2012.4.5',0,5,5 union all
seleziona '2012.4.6',30,20,15 union all
seleziona '2012.4.6',0,3,12
seleziona
[Giorno],sum([Entrata]) [Entrata],sum([Uscita]) [Uscita],min([Corrente]) come [Corrente]
dal [Log] raggruppa per [Giorno]
/*
Giorno Entrata Uscita Corrente
2012-04-05 10 5 5
2012-04-06 30 23 12
*/
Esempio 11: riepilogo secondario dopo raggruppamento SQL
https://it.oldtoolbag.com/article/106074.htm
Esempio 12: statistica di classificazione e raggruppamento SQL
È necessario comprendere come utilizzare alcune clausole e operatori SQL per organizzare i dati SQL, al fine di analizzarli in modo efficiente. Le seguenti raccomandazioni vi diranno come costruire le espressioni per ottenere i risultati desiderati.
Organizzare i dati in modo significativo può essere una sfida. A volte, è sufficiente una semplice classificazione. Di solito, è necessario fare più elaborazioni - raggruppare per facilitare l'analisi e il conteggio. Fortunatamente, SQL fornisce una vasta gamma di clausole e operatori per la classificazione, il raggruppamento e il conteggio. Le seguenti raccomandazioni vi aiuteranno a capire quando classificare, quando raggruppare, quando e come fare il conteggio. Per ulteriori dettagli su ogni clausola e operatore, consultare
.
#1: ordinamento di classificazione
Di solito, abbiamo bisogno di ordinare tutti i dati. La clausola ORDER BY di SQL organizza i dati in ordine alfabetico o numerico. Pertanto, i dati omogenei sono chiaramente classificati in vari gruppi. Tuttavia, questi gruppi sono solo il risultato della classificazione, non sono veri gruppi. ORDER BY mostra ogni record, mentre un gruppo può rappresentare più record.
#2: ridurre i dati simili nel gruppo
La maggiore differenza tra classificazione e raggruppamento sta nel fatto che i dati di classificazione mostrano tutti i record (all'interno di qualsiasi standard limitato), mentre i dati di raggruppamento non mostrano questi record. La clausola GROUP BY riduce i dati simili in un record. Ad esempio, GROUP BY può restituire una lista unica di codici postali da un file sorgente che ripete quei valori:
SELECT ZIP
FROM Customers
GROUP BY ZIP
Includere solo le colonne che definiscono il gruppo sia nel GROUP BY che nella lista SELECT. In altre parole, la lista SELECT deve corrispondere alla lista GROUP. C'è un'eccezione solo in un caso: la lista SELECT può includere funzioni aggregate (mentre GROUP BY non supporta le funzioni aggregate).
Ricordatevi che GROUP BY non classifica i gruppi generati come risultato. Per ordinare i gruppi in ordine alfabetico o numerico, aggiungere una clausola ORDER BY (#1). Inoltre, non è possibile fare riferimento a un dominio con un alias nella clausola GROUP BY. Le colonne di gruppo devono essere nel dati di base, ma non devono apparire nel risultato.
#3: Limitare i dati prima del raggruppamento
È possibile aggiungere una clausola WHERE per limitare i dati raggruppati. Ad esempio, la seguente istruzione restituirà solo l'elenco dei codici postali dei clienti nella regione del Kentucky.
SELECT ZIP
FROM Customers
WHERE Stato = 'KY'
GROUP BY ZIP
Prima di calcolare i valori dei dati nella clausola GROUP BY, WHERE filtra i dati, è molto importante ricordare questo.
Come GROUP BY, WHERE non supporta le funzioni aggregate.
#4: Restituire tutti i gruppi
Quando si filtra i dati con WHERE, i gruppi mostrano solo le registrazioni specificate. I dati che soddisfano la definizione del gruppo ma non la condizione della clausola non appariranno nel gruppo. Indipendentemente dalla condizione WHERE, se si desidera includere tutti i dati, aggiungere un sottoclausolo ALL. Ad esempio, aggiungere un sottoclausolo ALL alla precedente istruzione restituirà tutti i gruppi di codice postale, non solo i gruppi della regione del Kentucky.
SELECT ZIP
FROM Customers
WHERE Stato = 'KY'
GROUP BY ALL ZIP
In questo modo, queste due clausole possono causare conflitti, potreste non utilizzare il sottoclausolo ALL in questo modo. Quando si utilizza l'aggregazione per calcolare il valore di una colonna, è comodo utilizzare il sottoclausolo ALL. Ad esempio, la seguente istruzione calcola il numero di clienti per ogni codice postale del Kentucky, visualizzando anche altri valori di codice postale.
SELECT ZIP, Count(ZIP) AS KYCustomersByZIP
FROM Customers
WHERE Stato = 'KY'
GROUP BY ALL ZIP
I gruppi ottenuti sono costituiti da tutti i valori di codice postale del dati di base. Tuttavia, la colonna aggregata (KYCustomerByZIP) viene visualizzata come 0, perché non ci sono altri gruppi oltre al gruppo di codice postale del Kentucky.
La query remota non supporta GROUP BY ALL.
#5: Limitare i dati dopo il raggruppamento
La clausola WHERE (#3) calcola i valori dei dati prima della clausola GROUP BY. Quando si desidera limitare i dati dopo la raggruppamento, utilizzare HAVING. Di solito, non importa se si utilizza WHERE o HAVING, i risultati sono gli stessi. Ma ricordatevi che queste due clausole non si possono scambiare, questo è molto importante. Se avete domande, ecco una guida applicativa: utilizzare WHERE per filtrare le registrazioni; utilizzare HAVING per filtrare i gruppi.
Di solito, utilizzerai HAVING per calcolare il valore di un gruppo utilizzando l'aggregazione. Ad esempio, la seguente query restituisce una lista di codici postali, ma la tabella potrebbe non includere tutti i codici postali della sorgente di dati originale:
SELECT ZIP, Count(ZIP) AS CustomersByZIP
FROM Customers
GROUP BY ZIP
HAVING Count(ZIP) = 1
Appariranno solo quei gruppi che contengono un singolo cliente.
#6: Scopri di più su WHERE e HAVING
Se sei ancora confuso sull'uso di WHERE e HAVING, segui le seguenti istruzioni:
WHERE appare prima di GROUP BY; SQL calcola il valore della clausola WHERE prima di raggruppare i record.
HAVING appare dopo GROUP BY; SQL calcola il valore della clausola HAVING dopo aver raggruppato i record.
#7: Totale aggregato dei valori raggruppati
Il raggruppamento dei dati aiuta nell'analisi dei dati, ma a volte hai bisogno di informazioni al di fuori del gruppo stesso. Puoi aggiungere una funzione aggregate per totalizzare i dati raggruppati. Ad esempio, la seguente query mostra un totale per ogni sortita:
SELECT OrderID, Sum(Cost * Quantity) AS OrderTotal
FROM Orders
GROUP BY OrderID
Come gli altri gruppi, la lista SELECT e GROUP BY deve corrispondere. L'unica eccezione a questa regola è l'inclusione di un'aggregazione nella clausola SELECT.
#8: Totale aggregato
Puoi totalizzare ulteriormente i dati visualizzando i totali di ciascun gruppo. L'operatore ROLLUP di SQL visualizza un record aggiuntivo per ogni gruppo, un totale. Questo record è il risultato dell'applicazione delle funzioni aggregate a tutti i record del gruppo. La seguente query totale OrderTotal per ciascun gruppo.
SELECT Customer, OrderNumber, Sum(Cost * Quantity) AS OrderTotal
FROM Orders
GROUP BY Customer, OrderNumber
WITH ROLLUP
Una riga ROLLUP per un gruppo contenente i valori OrderTotal 20 e 25 mostrerà il valore OrderTotal 45. Il primo valore del risultato di ROLLUP è unico perché calcola il valore di tutti i record del gruppo. Questo valore è la somma totale del set di record.
ROLLUP non supporta la clausola DISTINCT o GROUP BY ALL nelle funzioni aggregate.
#9: Totale di ogni colonna
L'operatore CUBE va oltre ROLLUP, restituendo il totale di ciascun valore per ogni gruppo. Il risultato è simile a quello di ROLLUP, ma CUBE include un record aggiuntivo per ogni colonna del gruppo. La seguente query mostra i totali di ciascun gruppo e un totale aggiuntivo per ogni cliente.
SELECT Customer, OrderNumber, Sum(Cost * Quantity) AS OrderTotal
FROM Orders
GROUP BY Customer, OrderNumber
WITH CUBE
用CUBE得到的总计最为复杂。不仅完成聚合与ROLLUP的工作，而且还求定义组的其它列的值。也就是说，CUBE总计每一个可能的列组合。
CUBE不支持GROUP BY ALL。
#10：给总计排序
当CUBE的结果杂乱无章时（一般都是这样），可以增加一个GROUPING函数，如下所示：
SELECT GROUPING(Customer), OrderNumber, Sum(Cost * Quantity) AS OrderTotal
FROM Orders
GROUP BY Customer, OrderNumber
WITH CUBE
其结果包括每一行的两个额外的值。
值1表明左边的值是一个总计值——ROLLUP或CUBE的运算符的结果。
值0表明左边的值是一个原始GROUP BY子句产生的详细记录。
在分组查询中还可以配合使用HAVING子句，定义查询条件。
使用group by进行分组查询
在使用group by关键字时，在select列表中可以指定的项目是有限制的，select语句中仅许以下几项：
〉被分组的列
〉为每个分组返回一个值得表达式，例如用一个列名作为参数的聚合函数
group by 有一个原则，就是 select 后面的所有列中，没有使用聚合函数的列，必须出现在 group by 后面（重要）
group by实例
实例一
数据表：
姓名 科目 分数
张三 语文 80
张三 数学 98
张三 英语 65
李四 语文 70
李四 数学 80
李四 英语 90
期望查询结果：
姓名 语文 数学 英语
张三 80 98 65
李四 70 80 90
Codice
create table testScore
(
tid int primary key identity(1,1),
tname varchar(30) null,
ttype varchar(10) null,
tscor int null
)
go
---插入数据
insert into testScore values ('张三','语文',80)
inserisci into punteggio_di_prova valori ('Zhang San', 'matematica', 98)
inserisci into punteggio_di_prova valori ('Zhang San', 'inglese', 65)
inserisci into punteggio_di_prova valori ('Li Si', 'chinese', 70)
inserisci into punteggio_di_prova valori ('Li Si', 'matematica', 80)
inserisci into punteggio_di_prova valori ('Li Si', 'inglese', 90)
seleziona tname as 'nome',
max(case when ttype = 'chinese' then tscor else 0 end) 'chinese',
max(case when ttype = 'matematica' then tscor else 0 end) 'matematica',
max(case when ttype = 'inglese' then tscor else 0 end) 'inglese'
dal punteggio_di_prova
group by tname
Esempio due
Ecco i dati:(Per una visione più chiara, non ho utilizzato il codice del paese, ma ho utilizzato il nome del paese come chiave primaria)
| Ecco i dati disponibili | Sesso (sex) |
| Femmina | 600 |
| 260 | 100 |
| 55 | 100 |
| Regno Unito | 200 |
| Francia | 300 |
| Giappone | 250 |
| Germania | 200 |
| Messico | 50 |
| India | 250 |
Secondo i dati della popolazione di questo paese, calcoliamo il numero di popolazione in Asia e America del Nord. Dovremmo ottenere il seguente risultato:
| continente | popolazione |
| Asia | 1100 |
| America del Nord | 250 |
| Altro | 700 |
Codice
SELEZIONA SUM(popolazione),
CASO paese
QUANDO 'Cina' ALLORA 'Asia'
QUANDO 'India' ALLORA 'Asia'
QUANDO 'Giappone' ALLORA 'Asia'
QUANDO 'Stati Uniti' ALLORA 'America del Nord'
QUANDO 'Canada' ALLORA 'America del Nord'
QUANDO 'Messico' ALLORA 'America del Nord'
ALTRimenti 'Altro' FINE
DAL Tabella_A
GRUPPO PER CASO paese
QUANDO 'Cina' ALLORA 'Asia'
QUANDO 'India' ALLORA 'Asia'
QUANDO 'Giappone' ALLORA 'Asia'
QUANDO 'Stati Uniti' ALLORA 'America del Nord'
QUANDO 'Canada' ALLORA 'America del Nord'
QUANDO 'Messico' ALLORA 'America del Nord'
ALTRimenti 'Altro' FINE;
Anche così, possiamo utilizzare questo metodo per determinare la classe di salario e contare il numero di persone in ogni classe. Ecco il codice SQL:
SELEZIONA
CASO salario <= 500 ALLORA '1'
WHEN salary > 500 AND salary <= 600 THEN '2'
WHEN salary > 500 AND salary <= 600 THEN '2'
WHEN salary > 600 AND salary <= 800 THEN '3'
ALTRimenti NULL FINE classe_salario,
CONTAGGIO(*)
DAL Tabella_A
GRUPPO PER
CASO salario <= 500 ALLORA '1'
WHEN salary > 500 AND salary <= 600 THEN '2'
WHEN salary > 500 AND salary <= 600 THEN '2'
WHEN salary > 600 AND salary <= 800 THEN '3'
WHEN salary > 800 AND salary <= 1000 THEN '4'
ELSE NULL END;
Per groupby di solito segue un nome di colonna, ma in questo esempio la group by è resa più potente attraverso l'uso dello statement case.
Esempio tre
| Ecco i dati disponibili | Nazione (country) | Sesso (sex) |
| Femmina | Popolazione (population) | Cina |
| Femmina | 1 | 340 |
| 260 | Popolazione (population) | Stati Uniti |
| 260 | 1 | 45 |
| 55 | Popolazione (population) | Canada |
| 55 | 1 | 51 |
| Regno Unito | Popolazione (population) | 40 |
| Regno Unito | 1 | 60 |
2
| Raggruppati per nazione e sesso, i risultati sono i seguenti | Nazione | Maschio |
| Femmina | Cina | 340 |
| 260 | Stati Uniti | 45 |
| 55 | Canada | 51 |
| Regno Unito | 40 | 60 |
Codice
SELECT country,
SUM( CASE WHEN sex = '1' THEN
population ELSE 0 END), -- popolazione maschile
SUM( CASE WHEN sex = '2' THEN
population ELSE 0 END) -- popolazione femminile
FROM Table_A
GROUP BY country;
Gestione dei valori NULL nella clausola GROUP BY
Quando il valore NULL appare nella colonna utilizzata per il raggruppamento nella clausola GROUP BY, come viene effettuato il raggruppamento? Nel SQL, NULL non è uguale a NULL (è stato introdotto nella clausola WHERE). Tuttavia, nella clausola GROUP BY, tutti i valori NULL vengono raggruppati nello stesso gruppo, considerandoli come 'uguali'.
Clausola HAVING
La clausola GROUP BY raggruppa i dati semplicemente in base alle colonne selezionate, raggruppando le righe che hanno lo stesso valore in una singola colonna. Tuttavia, nell'applicazione pratica, spesso è necessario eliminare quei gruppi di righe che non soddisfano i requisiti. Per implementare questa funzione, SQL fornisce la clausola HAVING. La grammatica è la seguente.
SELECT column, SUM(column) FROM table GROUP BY column HAVING SUM(column) condizione valore
Descrizione: HAVING viene utilizzato di solito insieme alla clausola GROUP BY. Certo, la funzione SUM() nella grammatica può essere qualsiasi altra funzione di aggregazione. Il DBMS applica la condizione di ricerca nella clausola HAVING ai gruppi di righe generati dalla clausola GROUP BY, e se i gruppi di righe non soddisfano la condizione di ricerca, li elimina dalla tabella di risultato.
Applicazione della clausola HAVING
Eseguire una query sulla tabella TEACHER per trovare i dipartimenti che hanno almeno due insegnanti e il numero di insegnanti.
Codice di implementazione:
SELECT DNAME, COUNT(*) AS num_teacher FROM TEACHER GROUP BY DNAME HAVING COUNT(*)>=2
La differenza tra la clausola HAVING e la clausola WHERE
La somiglianza tra la clausola HAVING e la clausola WHERE sta nel fatto che entrambe definiscono condizioni di ricerca. Ma a differenza della clausola WHERE, la clausola HAVING è relativa ai gruppi, non alle singole righe.
1. Se si specifica la clausola GROUP BY, la condizione di ricerca definita dalla clausola HAVING agisce sui gruppi creati dalla clausola GROUP BY.
2. Se si specifica la clausola WHERE ma non la clausola GROUP BY, la condizione di ricerca definita dalla clausola HAVING agisce sull'output della clausola WHERE e considera questo output come un gruppo.
3. Se non si specifica né la clausola GROUP BY né la clausola WHERE, la condizione di ricerca definita dalla clausola HAVING agisce sull'output della clausola FROM e considera questo output come un gruppo.
4. L'ordine di esecuzione delle clausole WHERE e HAVING nel comando SELECT è diverso. Dal passo dell'esecuzione del comando SELECT presentato nella sezione 5.1.2 di questo libro, si può sapere che la clausola WHERE può accettare l'input dalla clausola FROM, mentre la clausola HAVING può accettare l'input dalla clausola GROUP BY, WHERE e FROM.