# serverl列求和 sql_分组后分组合计以及总计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)统计分组后的种类数：

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)

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

1

1        2001/12/12

2

2

3        2002/01/01

3

3

3        2003/12/12

4

4

1        2001/12/12

2

2

3        2003/12/12

4

4

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

)

)

city列里面只有5个城市

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

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

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

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 ;

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

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

B,       C

b1,     c1

a2,     b2,

c2

a2,     b3,

c3

a3,     b4,

c4

a3,     b5,

c5

A,

B,       C

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

a2,     b2,

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

a3,     b4,

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

select   *   from   表   tem   where   c=(select

top   1   c   from   表   where

a=tem.a)

zj

th

bj

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

03106666666 00001 03101111111

13711111111 00001 031122222222

03108898888 950000

031177778777 950000

031155955555 00001 031187888876

1、bj分组

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

as   呼叫总量   from   call

group   by   substr(bj,1,4);

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

0310

1

0311

2

2

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

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

as   呼叫总量   from   call

where   th=950000

group   by   substr(zj,1,4);

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

0310

1

0311

1

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

0310

2

0311

3

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

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

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

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

from表A as t1

where主键in(

select top 3主键from表A as t2

where t1.B=t2.B

order by t2.C)

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 #

create table abc(

i nvarchar(10),

ii int,

iii int,

iiii int,

price money)

go

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

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

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

from abc a) tem

where ids<3

order by i,price

3.

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种方法类似，大家可以自己试试

a

WHERE  ItemID Not in( select top 1 ItemID from TB_WorkflowTask where

= a.BillID And Status =a.Status AND WFStatus =a.WFStatus )

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

*/

sql的分类与分组统计

#1：分类排序

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

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

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

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

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

SELECT ZIP

FROM Customers

GROUP BY ZIP

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

BY不支持聚合函数。)

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

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

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

#3：分组前限定数据

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

SELECT ZIP

FROM Customers

WHERE State = 'KY'

GROUP BY ZIP

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

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

#4：返回所有组

SELECT ZIP

FROM Customers

WHERE State = 'KY'

GROUP BY ALL ZIP

SELECT ZIP, Count(ZIP) AS KYCustomersByZIP

FROM Customers

WHERE State = 'KY'

GROUP BY ALL ZIP

BY ALL。

#5：分组后限定数据

WHERE 子句(#3)在GROUP

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

SELECT ZIP, Count(ZIP) AS CustomersByZIP

FROM Customers

GROUP BY ZIP

HAVING Count(ZIP) = 1

#6：详细了解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

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

#8：总计聚合

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

FROM Orders

GROUP BY Customer, OrderNumber

WITH 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不支持GROUP

BY ALL。

#10：给总计排序

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

FROM Orders

GROUP BY Customer, OrderNumber

WITH CUBE

BY子句产生的详细记录。

• 0
点赞
• 2
收藏
• 打赏
• 0
评论
09-09
08-30
12-27 2045
10-28 408
12-29 1012
07-13 5569
01-15 9286
02-25 641
08-13 1421
08-24 852
02-08 1045
08-29 330

### “相关推荐”对你有帮助么？

• 非常没帮助
• 没帮助
• 一般
• 有帮助
• 非常有帮助

lissssssll

¥2 ¥4 ¥6 ¥10 ¥20

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、C币套餐、付费专栏及课程。