求高级汇总SQL语句，路过千万不要错过

rings2000 2008-03-01 09:14:18

例如：数据如下:
id product inorout account
1 主板进仓 60
2 声卡进仓 45
3 主板出仓 20
4 网卡进仓 20
5 网卡出仓 6
6 主板进仓 9
7 声卡进仓 5
8 主板出仓 5
9 主板报废 2

求SQL统计语句,通过这SQL语句查询后得到：

产品进仓总数量出仓总数量报废总数量合计
主板 69 25 2 42
声卡 50 0 0 50
网卡 20 6 0 14

如果SQL语句实现不了，用什么方法实现呢？如果用存储过程的话，该如何编写呢？

...全文

388 16 打赏收藏转发到动态举报

写回复

用AI写文章

16 条回复

切换为时间正序

请发表友善的回复…

发表回复

zhouln2002 2008-03-02

打赏
举报

只是个交叉表汇总需求嘛,

记得SQL里面有个存储过程脚本,直接就可以传递表名,交叉字段, 汇总字段, 行标题字段自动形成新的交叉表数据.

-狙击手- 2008-03-01

打赏
举报

create table tb(id int,product nvarchar(20),inorout nvarchar(20),account int)

insert tb select 1 ,  '主板'   ,    '进仓'   ,      60 

insert tb select 2 ,  '声卡'   ,    '进仓'   ,      45 

insert tb select 3 ,  '主板'   ,    '出仓'   ,      20 

insert tb select 4 ,  '网卡'   ,    '进仓'   ,      20 

insert tb select 5 ,  '网卡'   ,    '出仓'   ,       6 

insert tb select 6 ,  '主板'   ,    '进仓'   ,       9 

insert tb select 7 ,  '声卡'   ,    '进仓'   ,       5 

insert tb select 8 ,  '主板'   ,    '出仓'   ,       5 

insert tb select 9 ,  '主板'   ,    '报废'   ,       2

go

select isnull(product,'合计') as 产品,

       进仓总数量 = sum(case when inorout='进仓' then account else 0 end),

       出仓总数量 = sum(case when inorout='出仓' then account else 0 end),

       报废总数量 = sum(case when inorout='报废' then account else 0 end),

       [合 计] = sum(case when inorout='进仓' then account else 0 end -

                  case when inorout='出仓' then account else 0 end -

                  case when inorout='报废' then account else 0 end)

from Tb

group by product 

with rollup

/*

产品                   进仓总数量       出仓总数量       报废总数量       合 计         

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

声卡                   50          0           0           50

网卡                   20          6           0           14

主板                   69          25          2           42

合计                   139         31          2           106



（所影响的行数为 4 行）



*/



drop table tb

rings2000 2008-03-01

打赏
举报

如果还在要查询最后一行加(合计),即查询结果如下,那SQL语句怎么写呢?
产品进仓总数量出仓总数量报废总数量合计
主板 69 25 2 42
声卡 50 0 0 50
网卡 20 6 0 14
(合计) 139 31 2 106

zghua851004 2008-03-01

打赏
举报

学习

中国风 2008-03-01

打赏
举报

楼上是Access方法，在SQL不行的

--group by inorout--少了一段



create table tb(id int,product nvarchar(20),inorout nvarchar(20),account int)

insert tb select 1 ,  '主板'   ,    '进仓'   ,      60 

insert tb select 2 ,  '声卡'   ,    '进仓'   ,      45 

insert tb select 3 ,  '主板'   ,    '出仓'   ,      20 

insert tb select 4 ,  '网卡'   ,    '进仓'   ,      20 

insert tb select 5 ,  '网卡'   ,    '出仓'   ,       6 

insert tb select 6 ,  '主板'   ,    '进仓'   ,       9 

insert tb select 7 ,  '声卡'   ,    '进仓'   ,       5 

insert tb select 8 ,  '主板'   ,    '出仓'   ,       5 

insert tb select 9 ,  '主板'   ,    '报废'   ,       2



go



declare @s nvarchar(4000)

set @s='select product'

select 

    @s=@s+',['+inorout+'总数量]=sum(case when inorout='''+inorout+''' then account else 0 end)'

from 

    Tb

group by inorout--少了一段



set @s=@s+',sum(case when inorout=''进仓'' then account else -account end)合计'+

    ' from tb group by product'



exec(@s)

/*

product              报废总数量       出仓总数量       进仓总数量       合计          

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

声卡                   0           0           50          50

网卡                   0           6           20          14

主板                   2           25          69          42



*/

liuyann 2008-03-01

打赏
举报

select product as 产品,

	sum(iif(inorout='进仓',account,0)) as 进仓总数量,

	sum(iif(inorout='出仓',account,0)) as 出仓总数量,

	sum(iif(inorout='报废',account,0)) as 报废总数量,

	sum(iif(inorout='进仓',account,-account)) as 合计,

	0 as sordno

from yourTable

group by product

union

select '(合计)' as 产品,

	sum(iif(inorout='进仓',account,0)) as 进仓总数量,

	sum(iif(inorout='出仓',account,0)) as 出仓总数量,

	sum(iif(inorout='报废',account,0)) as 报废总数量,

	sum(iif(inorout='进仓',account,-account)) as 合计,

	1 as sordno

from yourTable

order by sordno

== 思想重于技巧 ==

中国风 2008-03-01

打赏
举报

create table tb(id int,product nvarchar(20),inorout nvarchar(20),account int)

insert tb select 1 ,  '主板'   ,    '进仓'   ,      60 

insert tb select 2 ,  '声卡'   ,    '进仓'   ,      45 

insert tb select 3 ,  '主板'   ,    '出仓'   ,      20 

insert tb select 4 ,  '网卡'   ,    '进仓'   ,      20 

insert tb select 5 ,  '网卡'   ,    '出仓'   ,       6 

insert tb select 6 ,  '主板'   ,    '进仓'   ,       9 

insert tb select 7 ,  '声卡'   ,    '进仓'   ,       5 

insert tb select 8 ,  '主板'   ,    '出仓'   ,       5 

insert tb select 9 ,  '主板'   ,    '报废'   ,       2



go



declare @s nvarchar(4000)

set @s='select product'

select 

	@s=@s+',['+inorout+'总数量]=sum(case when inorout='''+inorout+''' then account else 0 end)'

from 

	Tb



set @s=@s+',sum(case when inorout=''进仓'' then account else -account end)合计'+

	' from tb group by product'



exec(@s)

	



product              进仓总数量       进仓总数量       出仓总数量       进仓总数量       出仓总数量       进仓总数量       进仓总数量       出仓总数量       报废总数量       合计          

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

声卡                   50          50          0           50          0           50          50          0           0           50

网卡                   20          20          6           20          6           20          20          6           0           14

主板                   69          69          25          69          25          69          69          25          2           42

-狙击手- 2008-03-01

打赏
举报

加这个N有什么作用的?与不加N有什么区别?

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

使用 Unicode 数据
Unicode 标准为全球商业领域中广泛使用的大部分字符定义了一个单一编码方案。所有的计算机都用单一的 Unicode 标准 Unicode 数据中的位模式一致地翻译成字符。这保证了同一个位模式在所有的计算机上总是转换成同一个字符。数据可以随意地从一个数据库或计算机传送到另一个数据库或计算机，而不用担心接收系统是否会错误地翻译位模式。

对于用一个字节编码每个字符的数据类型，存在的问题之一就是此数据类型只能表示 256 个不同的字符。这就迫使对于不同的字母表（例如相对较小的欧洲字母表）采用多重编码规格（或者代码页）。而且也不可能处理象日文汉字或韩国文字这样具有数千个字符的字母表。

每个 Microsoft® SQL Server™ 排序规则都有一个对表示 char、varchar 和 text 值中的每个字符定义位模式进行定义的代码页。可为个别的列和字符常量指派不同的代码页。客户端计算机使用与操作系统区域设置相关联的代码页解释字符位模式。有很多种不同的代码页。一些字符出现在某些代码页上，但并不出现在其它的代码页上。某些字符在一些代码页上用一个位模式定义，而在其它的代码页上却用另一个位模式定义。当您设计必须处理不同语言的国际性系统时，为了满足不同国家/地区的语言需求，给所有的计算机挑选代码页就变得困难了。要保证每一台计算机与使用不同代码页的系统交互时都进行正确的翻译也是困难的。

Unicode 规格通过采用两个字节编码每个字符使这个问题迎刃而解。转换最通用商业语言的单一规格具有足够多的 2 字节的模式 (65,536)。因为所有的 Unicode 系统均一致地采用同样的位模式来代表所有的字符，所以当从一个系统转到另一个系统时，将不会存在未正确转换字符的问题。通过在整个系统中使用 Unicode 数据类型，可尽量减少字符转换问题。

在 Microsoft SQL Server 中，以下数据类型支持 Unicode 数据：

nchar

nvarchar

ntext

说明这些数据类型的前缀 n 来自 SQL-92 标准中的 National（Unicode）数据类型。

nchar、nvarchar 和 ntext 的用法分别与 char、varchar 和 text 的用法一样，但在以下方面不同：

Unicode支持的字符范围更大。

存储 Unicode 字符所需要的空间更大。

nchar 和 nvarchar 列最多可以有 4,000 个字符，而不象 char 和 varchar 字符那样可以有 8,000 个字符。

Unicode 常量使用 N 开头来指定：N'A Unicode string'。

所有 Unicode 数据都使用相同的 Unicode 代码页。排序规则不控制用于 Unicode 列的代码页，仅控制比较规则和是否区分大小写等特性。

Keambala 2008-03-01

打赏
举报

又学到了一个好的查询思想:sum套case，
学习中...

rings2000 2008-03-01

打赏
举报

加这个N有什么作用的?与不加N有什么区别?

-狙击手- 2008-03-01

打赏
举报

create table tb(id int,product nvarchar(20),inorout nvarchar(20),account int)

insert tb select 1 ,  '主板'   ,    '进仓'   ,      60 

insert tb select 2 ,  '声卡'   ,    '进仓'   ,      45 

insert tb select 3 ,  '主板'   ,    '出仓'   ,      20 

insert tb select 4 ,  '网卡'   ,    '进仓'   ,      20 

insert tb select 5 ,  '网卡'   ,    '出仓'   ,       6 

insert tb select 6 ,  '主板'   ,    '进仓'   ,       9 

insert tb select 7 ,  '声卡'   ,    '进仓'   ,       5 

insert tb select 8 ,  '主板'   ,    '出仓'   ,       5 

insert tb select 9 ,  '主板'   ,    '报废'   ,       2

go

select product as 产品,

       进仓总数量 = sum(case when inorout='进仓' then account else 0 end),

       出仓总数量 = sum(case when inorout='出仓' then account else 0 end),

       报废总数量 = sum(case when inorout='报废' then account else 0 end),

       [合 计] = sum(case when inorout='进仓' then account else 0 end -

                  case when inorout='出仓' then account else 0 end -

                  case when inorout='报废' then account else 0 end)

from Tb

group by product 

/*

产品                   进仓总数量       出仓总数量       报废总数量       合 计         

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

声卡                   50          0           0           50

网卡                   20          6           0           14

主板                   69          25          2           42



（所影响的行数为 3 行）

*/



drop table tb

-狙击手- 2008-03-01

打赏
举报

Unicode 数据的数据类型

rings2000 2008-03-01

打赏
举报

sum(case when inorout=N'进仓' then account else 0 end) [进仓总数量],
sum(case when inorout=N'出仓' then account else 0 end) [出仓总数量],
sum(case when inorout=N'报废' then account else 0 end) [报废总数量]
为什么'进仓'，'出仓'，'报废' 前面要加个N呢?

liangCK 2008-03-01

打赏
举报

create table tb(id int,product nvarchar(20),inorout nvarchar(20),account int)

insert tb select 1 ,  '主板'   ,    '进仓'   ,      60 

insert tb select 2 ,  '声卡'   ,    '进仓'   ,      45 

insert tb select 3 ,  '主板'   ,    '出仓'   ,      20 

insert tb select 4 ,  '网卡'   ,    '进仓'   ,      20 

insert tb select 5 ,  '网卡'   ,    '出仓'   ,       6 

insert tb select 6 ,  '主板'   ,    '进仓'   ,       9 

insert tb select 7 ,  '声卡'   ,    '进仓'   ,       5 

insert tb select 8 ,  '主板'   ,    '出仓'   ,       5 

insert tb select 9 ,  '主板'   ,    '报废'   ,       2



select *,[合计]=进仓总数量-出仓总数量-报废总数量

from 

(

    select product,

      sum(case when inorout=N'进仓' then account else 0 end) [进仓总数量],

      sum(case when inorout=N'出仓' then account else 0 end) [出仓总数量],

      sum(case when inorout=N'报废' then account else 0 end) [报废总数量] 

    from tb

    group by product

) t



order by product



drop table tb



/*

product              进仓总数量       出仓总数量       报废总数量       合计

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

声卡                   50          0           0           50

网卡                   20          6           0           14

主板                   69          25          2           42



(3 行受影响)

*/

-狙击手- 2008-03-01

打赏
举报

产品    进仓总数量    出仓总数量   报废总数量     合 计 



select product as 产品,

       进仓总数量 = sum(case when inorout='进仓' then account else 0 end),

       出仓总数量 = sum(case when inorout='出仓' then account else 0 end),

       报废总数量 = sum(case when inorout='报废' then account else 0 end),

       合 计 = sum(case when inorout='进仓' then account else 0 end -

                  case when inorout='出仓' then account else 0 end -

                  case when inorout='报废' then account else 0 end)

from TA

group by product

liangCK 2008-03-01

打赏
举报

/*

普通行列转换

(爱新觉罗.毓华 2007-11-18于海南三亚)



假设有张学生成绩表(tb)如下:

Name Subject Result

张三 语文　　74

张三 数学　　83

张三 物理　　93

李四 语文　　74

李四 数学　　84

李四 物理　　94

*/



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

/*

想变成 

姓名         语文        数学        物理          

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

李四         74          84          94

张三         74          83          93

*/



create table tb

(

   Name    varchar(10) ,

   Subject varchar(10) ,

   Result  int

)



insert into tb(Name , Subject , Result) values('张三' , '语文' , 74)

insert into tb(Name , Subject , Result) values('张三' , '数学' , 83)

insert into tb(Name , Subject , Result) values('张三' , '物理' , 93)

insert into tb(Name , Subject , Result) values('李四' , '语文' , 74)

insert into tb(Name , Subject , Result) values('李四' , '数学' , 84)

insert into tb(Name , Subject , Result) values('李四' , '物理' , 94)

go



--静态SQL,指subject只有语文、数学、物理这三门课程。

select name 姓名,

  max(case subject when '语文' then result else 0 end) 语文,

  max(case subject when '数学' then result else 0 end) 数学,

  max(case subject when '物理' then result else 0 end) 物理

from tb

group by name

/*

姓名         语文        数学        物理          

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

李四         74          84          94

张三         74          83          93

*/



--动态SQL,指subject不止语文、数学、物理这三门课程。

declare @sql varchar(8000)

set @sql = 'select Name as ' + '姓名'

select @sql = @sql + ' , max(case Subject when ''' + Subject + ''' then Result else 0 end) [' + Subject + ']'

from (select distinct Subject from tb) as a

set @sql = @sql + ' from tb group by name'

exec(@sql) 

/*

姓名         数学        物理        语文          

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

李四         84          94          74

张三         83          93          74

*/



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

/*加个平均分，总分

姓名         语文        数学        物理        平均分                总分          

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

李四         74          84          94          84.00                252

张三         74          83          93          83.33                250

*/



--静态SQL,指subject只有语文、数学、物理这三门课程。

select name 姓名,

  max(case subject when '语文' then result else 0 end) 语文,

  max(case subject when '数学' then result else 0 end) 数学,

  max(case subject when '物理' then result else 0 end) 物理,

  cast(avg(result*1.0) as decimal(18,2)) 平均分,

  sum(result) 总分

from tb

group by name

/*

姓名         语文        数学        物理        平均分                总分          

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

李四         74          84          94          84.00                252

张三         74          83          93          83.33                250

*/



--动态SQL,指subject不止语文、数学、物理这三门课程。

declare @sql1 varchar(8000)

set @sql1 = 'select Name as ' + '姓名'

select @sql1 = @sql1 + ' , max(case Subject when ''' + Subject + ''' then Result else 0 end) [' + Subject + ']'

from (select distinct Subject from tb) as a

set @sql1 = @sql1 + ' , cast(avg(result*1.0) as decimal(18,2)) 平均分,sum(result) 总分 from tb group by name'

exec(@sql1) 

/*

姓名         数学        物理        语文        平均分                总分          

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

李四         84          94          74          84.00                252

张三         83          93          74          83.33                250

*/



drop table tb    



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

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

/*

如果上述两表互相换一下：即



姓名 语文 数学 物理

张三 74　　83　　93

李四 74　　84　　94



想变成 

Name       Subject Result      

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

李四         语文      74

李四         数学      84

李四         物理      94

张三         语文      74

张三         数学      83

张三         物理      93

*/



create table tb1

(

   姓名 varchar(10) ,

   语文 int ,

   数学 int ,

   物理 int

)



insert into tb1(姓名 , 语文 , 数学 , 物理) values('张三',74,83,93)

insert into tb1(姓名 , 语文 , 数学 , 物理) values('李四',74,84,94)



select * from

(

  select 姓名 as Name , Subject = '语文' , Result = 语文 from tb1 

  union all

  select 姓名 as Name , Subject = '数学' , Result = 数学 from tb1

  union all

  select 姓名 as Name , Subject = '物理' , Result = 物理 from tb1

) t

order by name , case Subject when '语文' then 1 when '数学' then 2 when '物理' then 3 when '总分' then 4 end



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

/*加个平均分,总分

Name       Subject     Result               

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

李四         语文      74.00

李四         数学      84.00

李四         物理      94.00

李四         平均分    84.00

李四         总分      252.00

张三         语文      74.00

张三         数学      83.00

张三         物理      93.00

张三         平均分    83.33

张三         总分      250.00

*/



select * from

(

  select 姓名 as Name , Subject = '语文' , Result = 语文 from tb1 

  union all

  select 姓名 as Name , Subject = '数学' , Result = 数学 from tb1

  union all

  select 姓名 as Name , Subject = '物理' , Result = 物理 from tb1

  union all

  select 姓名 as Name , Subject = '平均分' , Result = cast((语文 + 数学 + 物理)*1.0/3 as decimal(18,2)) from tb1

  union all

  select 姓名 as Name , Subject = '总分' , Result = 语文 + 数学 + 物理 from tb1

) t

order by name , case Subject when '语文' then 1 when '数学' then 2 when '物理' then 3 when '平均分' then 4 when '总分' then 5 end



drop table tb1