serverl列求和 sql_分组后分组合计以及总计SQL语句--收集未整理

最新推荐文章于 2024-05-24 18:23:43 发布

lissssssll

最新推荐文章于 2024-05-24 18:23:43 发布

阅读量2.6k

点赞数

文章标签： serverl列求和 sql

本文链接：https://blog.csdn.net/weixin_36296064/article/details/112829837

版权

本文详细介绍了SQL中的分组、总计操作，包括GROUP BY、COMPUTE、HAVING等用法，以及如何获取分组后的第一条、最后一条记录。还展示了如何统计分组后的总计、分组后的种类数，以及如何在分组基础上进行二次汇总。示例丰富，涵盖了各种常见场景，适合SQL初学者和进阶者学习。

摘要由CSDN通过智能技术生成

分组后分组合计以及总计SQL语句

1)想一次性得到分组合计以及总计，sql：

SELECT分组字段FROM表

GROUP

BY分组字段

compute

sum(COUNT(*))

=====

2)分组合计1：

SELECT

COUNT(*)

FROM(SELECT分组字段FROM表

GROUP

BY分组字段

)别名

3)分组合计2：

SELECT

COUNT(*)

FROM(SELECT distinct分组字段FROM表)别名

4)统计分组后的种类数：

例子1：分组合计

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)

上面的语句已经可以满足要求分组了.假设执行后有3条记录,怎么才能把这个COUNT值求出?

selectcount(*)from(SELECTJSSKQK_JGHFROMSJ_JSSKQKWHEREJSSKQK_JGHIN(SELECTJSJBXX_JGHFROMSJ_JSJBXXWHEREJSJBXX_JSLXM1=1)GROUPBYJSSKQK_JGHHAVING((SUM(JSSKQK_SSKCXS1)/40)>5)) t

TABLE：A

A B

C D

1 2001/01/01

1 2001/12/12

3 2002/01/01

3 2003/12/12

按列A分组，请问如何得到每组中时间最大的数据？

1 2001/12/12

3 2003/12/12

我的笨方法：

SELECT *

FROM A

WHERE (A,B) IN(

SELECT A,MAX(B)

FROM A

GROUP BY A

)

有更好的方法吗？

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(partion A order by B

desc)rk from test

) where rk=1

)

例子3：SQL语句分组获取记录的第一条数据的方法

使用Northwind

数据库

首先查询Employees表

查询结果：

city列里面只有5个城市

使用ROW_NUMBER()

OVER(PARTITION BY COL1 ORDER BY COL2) 先进行分组注：根据COL1分组，在分组内部根据COL2排序，而此函数计算的值就表示每组内部排序后的顺序编号(组内连续的唯一的).

sql语句为：

select EmployeeID,LastName,FirstName,Title,TitleOfCourtesy,City,ROW_NUMBER()

over(partition by City order by EmployeeID) as new_index

from Employees

执行结果图:

可以看到是按照City分组，EmployeeID排序。

select出分组中的第一条记录

执行语句：

select * from

(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

执行结果图：

例子4：sql 获取分组结果后，如何每一组的第一条记录

Eric

red 20

eric blue 30

andy red 10

andy blue 5

例如，只获取黑体的记录。

1，declare @fTable table (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)-- 只获取红色select * from @fTable where fColor = 'red'-- 每个 fColor 取一条记录(按 fOrder 正序)select * from @fTable A where fName = (select top 1 fName from @fTable where fColor = A.fColor order by fOrder )-- 每个 fColor 取一条记录(按 fOrder 反序)select * from @fTable A where fName = (select top 1 fName from @fTable where fColor = A.fColor order by fOrder desc)

2，SQL2005以上版本

select * from (select *,row=row_number()over(partition by Color order by Color)

from table1)t where row=1 and color='xx'--加上條件

SQL2000用

top 1

如果我想得到这样一个结果集：分组，并且每组限定记录集的数量，用一条SQL语句能办到吗？

比如说，我想找出学生期末考试中，每科的前3名，只用一条SQL语句，该怎么写？

表[TScore]的结构

code学号char

subject科目int

score成绩int

可以这样写：

SELECT [code]

,[subject]

,[score]

FROM (

SELECT *

,RANK()

OVER(PARTITION BY subject ORDER BY score DESC) ASRow

FROM TScore

) AS a

WHERE Row <= 3 ;

例子6：SQL获取每个分组的第一条记录

SQL查询以下伪数据获取粗体字行的记录

ID,Name,ItemID,Price,CreatedOn

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

获取每个分组中的第一条记录,当ItemID有多条记录时,选取Price最高的

--sql2000select *from tbname kwhere not exists(select * from tbname wherename=k.name and ITemID=K.ITemID and k.price

例子7：分组后取第一条记录的SQL语句

有如下表结构：

字段 A,

B, C

值为 a1,

b1, c1

a2, b2,

a2, b3,

a3, b4,

a3, b5,

想要得到的结果集以A字段为分组条件，并取出每一个分组中的第一条记录，如下：

B, C

值为 a1, b1,

c1 --a1分组的第一条记录。

a2, b2,

c2 --a2分组的第一条记录。

a3, b4,

c4 --a3分组的第一条记录。

select * from 表 tem where c=(select

top 1 c from 表 where

a=tem.a)

现有数据表call如下：

------------- -------- -------------

03106666666 00001 03101111111

13711111111 00001 031122222222

03108898888 950000

031177778777 950000

031155955555 00001 031187888876

注：th如为950000，则bj为空，th如为00001，则bj不是空。

1、bj分组

select substr(bj,1,4) as 区号,count(*)

as 呼叫总量 from call

group by substr(bj,1,4);

执行结果

区号

呼叫总量

------------ --------------

0310

0311

2、zj分组，条件是th为950000的记录

select substr(zj,1,4) as 区号,count(*)

as 呼叫总量 from call

where th=950000

group by substr(zj,1,4);

执行结果：

区号

呼叫总量

------------ --------------

0310

0311

能否有一个语句就能实现如下结果：

区号

呼叫总量

------------ --------------

0310

0311

注：想要得到结果是1对应的行加2对应的行。

union起来再求和

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 区号;

这个应该在oracle中运行

select

decode(th,'950000',substr(zj,1,4),substr(bj,1,4)) as 区号,

count(*) as 呼叫总量

from

call

group by

decode(th'950000',substr(zj,1,4),substr(bj,1,4))

decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值)

该函数的含义如下：

IF 条件=值1

THEN

RETURN(翻译值1)

ELSIF 条件=值2

THEN

RETURN(翻译值2)

......

ELSIF 条件=值n

THEN

RETURN(翻译值n)

ELSE

RETURN(缺省值)

END IF

假设有一个表，SQL语句如下：

CREATETABLE[dbo].[scan]([km][int]NULL,[kh][int]NULL,[cj][int]NULL)ON[PRIMARY]

其中km为科目号、kh为考生号、cj为成绩，现对km和kh进行分组，并获得每组前2条记录(按cj从高到低排序)。基本思想是为每组加一个序号列，再用where取序号小于等于2的。SQL语句如下：

select*from(selecta.km,a.kh,cj,row_number()over(partitionbya.kmorderbya.km,a.cjdesc) nfrom(selectkm,kh,SUM(cj) cjfromscangroupbykm,kh) a

) bwheren<=2orderbykm, cjdesc

最后得到的结果集如下图所示。

在表A中根据字段B分组、根据字段C排序并查询出每组中的前三条记录，查询结果要求包含所有字段，请问sql语句该怎么写？下面的sql语句虽然可以实现，但由于数据量比较大，耗费时间太长，有没有不通过表联接而直接分组取记录的方法呢？多谢！select *

from表A as t1

where主键in(

select top 3主键from表A as t2

where t1.B=t2.B

order by t2.C)

答案1作者：邹建

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 #

答案2作者：aierong

求每组前2名，你有几种方法？(MS SQL2000)

create table abc(

i nvarchar(10),

ii int,

iii int,

iiii int,

price money)

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

要求的结果是：以i分组，求每组price最大的前2条记录

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

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

连接查询，判断数量

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

from abc a) tem

where ids<3

order by i,price

生产一个内部表，给内部表tem中的每一组排序，并把排序号放入新列ids中

declare @looptime int

declare @count int

declare @i nvarchar(10)

/定义表变量@abc，和表ABC中的所有列类型相同/

declare @abc table(

i nvarchar(10),

ii int,

iii int,

iiii int,

price money)

declare @tem table(

ids int identity,

class nvarchar(10))

/把表ABC中的所有组全部查询出来，暂时存在表变量@tem中/

insert into @tem(class)

select i

from abc

group by i

/求出表变量@tem中行数量/

select @count=@@rowcount

/循环变量@looptime赋初值＝1/

select @looptime=1

while(@looptime<=@count)

begin

/将每组名赋值到变量@i/

select @i=class

from @tem

where ids=@looptime

/将每组前2名插入到表变量@abc中/

insert into @abc

select top 2 *

from abc

where i=@i

order by price desc

/循环变量@looptime累加1/

select @looptime=@looptime+1

end

/显示结果/

select *

from @abc

order by i,price

4.用游标来处理方法和我的第3种方法类似，大家可以自己试试

我共写了4种，不知道大家还有什么其他好方法，可以交流，谢谢。

今天用到了，利用此方法可以解决一个删除重复记录的问题

当然表必须带有唯一索引,仔细看以下代码

Delete From dbo.TB_WorkflowTask

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 )

注意：只能用In或Not in，不能用Exists或Not Exists，至于为什么，大家思考一下？

例子10：如何取得分组后最后一条记录的值？

还是很混乱，再排一下：

重新整理一下格式：

现有一表

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

………………………………………………

希望显示为

、

2012.4.5 10 5 5

2012.4.6 30 23 12

SQL code

withtbas(

select[day],sum([in])as[in],sum(out)asout,sum([in])-sum(out)as[current],rank()over(orderby[day])asrowfrom[log]groupby[day]

)

select[day],[in],out,(selectsum([current])fromtb bwhereb.row<=a.row)[current]fromtb a

SQL code

2012.4.51055

2012.4.6302312

SQL code

-->测试数据：[Log]

ifobject_id('[Log]')isnotnulldroptable[Log]

createtable[Log]([Day]date,[In]int,[Out]int,[Current]int)

insert[Log]

select'2012.4.5',10,0,10unionall

select'2012.4.5',0,5,5unionall

select'2012.4.6',30,20,15unionall

select'2012.4.6',0,3,12

select

[Day],sum([In])[In],sum([Out])[Out],min([Current])as[Current]

from[Log]groupby[Day]

DayInOutCurrent

2012-04-051055

2012-04-06302312

例子11：sql分组后二次汇总

例子12：

sql的分类与分组统计

您需要了解如何使用某些SQL子句和运算符来安排SQL数据，从而对它进行高效分析。下面这些建议告诉您如何建立语句，获得您希望的结果。

以有意义的方式安排数据可能是一种挑战。有时您只需进行简单分类。通常您必须进行更多处理——进行分组以利于分析与总计。可喜的是，SQL提供了大量用于分类、分组和总计的子句及运算符。下面的建议将有助于您了解何时进行分类、何时分组、何时及如何进行总计。欲了解每个子句和运算符的详细信息，请查看

。

#1：分类排序

通常，我们确实需要对所有数据进行排序。SQL的ORDER

BY子句将数据按字母或数字顺序进行排列。因此，同类数据明显分类到各个组中。然而，这些组只是分类的结果，它们并不是真正的组。ORDER

BY显示每一个记录，而一个组可能代表多个记录。

#2：减少组中的相似数据

分类与分组的最大不同在于：分类数据显示(任何限定标准内的)所有记录，而分组数据不显示这些记录。GROUP

BY子句减少一个记录中的相似数据。例如，GROUP

BY能够从重复那些值的源文件中返回一个唯一的邮政编码列表：

SELECT ZIP

FROM Customers

GROUP BY ZIP

仅包括那些在GROUP

BY和SELECT列列表中字义组的列。换句话说，SELECT列表必须与GROUP列表相匹配。只有一种情况例外：SELECT列表能够包含聚合函数。(而GROUP

BY不支持聚合函数。)

记住，GROUP

BY不会对作为结果产生的组分类。要对组按字母或数字顺序排序，增加一个ORDER

BY子句(#1)。另外，在GROUP

BY子句中您不能引用一个有别名的域。组列必须在根本数据中，但它们不必出现在结果中。

#3：分组前限定数据

您可以增加一个WHERE子句限定由GROUP

BY分组的数据。例如，下面的语句仅返回肯塔基地区顾客的邮政编码列表。

SELECT ZIP

FROM Customers

WHERE State = 'KY'

GROUP BY ZIP

在GROUP

BY子句求数据的值之前，WHERE对数据进行过滤，记住这一点很重要。

和GROUP

BY一样，WHERE不支持聚合函数。

#4：返回所有组

当您用WHERE过滤数据时，得到的组只显示那些您指定的记录。符合组定义但不满足子句条件的数据将不会出现在组中。不管WHERE条件如何，如果您想包括所有数据，增加一个ALL子句。例如，在前面的语句中增加一个ALL子句会返回所有邮政编码组，而不仅仅是肯塔基地区的组。

SELECT ZIP

FROM Customers

WHERE State = 'KY'

GROUP BY ALL ZIP

照这个样子，这两个子句会造成冲突，您可能不会以这种方式使用ALL子句。当您用聚合求一个列的值时，应用ALL子句很方便。例如，下面的语句计算每个肯塔基邮政编码的顾客数目，同时显示其它邮政编码值。

SELECT ZIP, Count(ZIP) AS KYCustomersByZIP

FROM Customers

WHERE State = 'KY'

GROUP BY ALL ZIP

得到的组由根本数据中的所有邮政编码值构成。但是，聚合列(KYCustomerByZIP)显示为0，因为除肯塔基邮政编码组外没有别的组。

远程查询不支持GROUP

BY ALL。

#5：分组后限定数据

WHERE 子句(#3)在GROUP

BY子句之前求数据的值。当您希望在分组以后限定数据时，使用HAVING。通常，不管您使用WHERE还是HAVING，得到的结果相同。但要记住，这两个子句不能互换，这点很重要。如果您存在疑问，这里有一条应用指南：过滤记录时使用WHERE；过滤组时使用HAVING。

一般，您会用HAVING，利用聚合来求一个组的值。例如，下面的语句返回一个邮政编码列表，但这个表内可能不包含根本数据源中的每个邮政编码：

SELECT ZIP, Count(ZIP) AS CustomersByZIP

FROM Customers

GROUP BY ZIP

HAVING Count(ZIP) = 1

仅仅那些只有一名顾客的组出现在结果中。

#6：详细了解WHERE和HAVING

如果您仍然对WHERE和HAVING的用法感到迷惑，应用下面的指导方法：

WHERE出现在GROUP

BY之前；SQL在它分组记录前求WHERE子句的值。

HAVING出现在GROUP

BY之后；SQL在它分组记录后求HAVING子句的值。

#7：用聚合总计分组值

分组数据有助于对数据进行分析，但有时您还需要组本身以外的其它信息。您可以增加一个聚合函数来总计分组数据。例如，下面的语句为每次排序显示一个小计：

SELECT OrderID, Sum(Cost * Quantity) AS OrderTotal

FROM Orders

GROUP BY OrderID

与其它的组一样，SELECT和GROUP

BY列表必须相匹配。在SELECT子句中包含一个聚合是这一规则的唯一例外。

#8：总计聚合

您可以通过显示每个组的小计进一步总计数据。SQL的ROLLUP运算符为每个组显示一个额外的记录，一个小计。那个记录是用聚合函数在每个组中求所有记录的值的结果。下面的语句为每个组合计OrderTotal列。

SELECT Customer, OrderNumber, Sum(Cost * Quantity) AS OrderTotal

FROM Orders

GROUP BY Customer, OrderNumber

WITH ROLLUP

一个包含20和25这两个OrderTotal值的组的ROLLUP行将显示OrderTotal值45。ROLLUP结果的第一个值是唯一的，因为它求所有组记录的值。那个值是整个记录集的总和。

ROLLUP不支持聚合函数中的DISTINCT或GROUP

BY ALL子句。

#9：总计每一列

CUBE运算符比ROLLUP更进一步，它返回每个组中每个值的总数。得到的结果与ROLLUP相似，但CUBE包括组中每一列的一个额外记录。下面的语句显示每个组的小计和每名顾客的一个额外总数。

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子句产生的详细记录。

lissssssll

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
serverl列求和 sql_分组后分组合计以及总计SQL语句--收集未整理

分组后分组合计以及总计SQL语句1)想一次性得到分组合计以及总计，sql：SELECT分组字段FROM表GROUPBY分组字段computesum(COUNT(*))=====2)分组合计1：SELECTCOUNT(*)FROM(SELECT分组字段FROM表GROUPBY分组字段)别名3)分组合计2：SELECTCOUNT(*)FROM(SELECT distinct分组字段FROM表)别名4)...
复制链接

扫一扫