一个统计查询

Question

数据：name num1 num2
lucy 25 42
lucky 741 35
smith 35 621
hello 253 42要求结果：name num1 num2 num3
lucy 25 42 0.595238095
lucky 741 35 0.047233468
smith 35 621 0.134099617
hello 253 42 6.023809524
1054 740 1.424324324

Answer 1

----------------------------------------------------------------
-- Author  :SQL77(只为思齐老)
-- Date    :2010-01-25 18:11:48
-- Version:
--      Microsoft SQL Server  2000 - 8.00.194 (Intel X86) 
-- Aug  6 2000 00:57:48 
-- Copyright (c) 1988-2000 Microsoft Corporation
-- Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据：#TB
if object_id('tempdb.dbo.#TB') is not null drop table #TB
go 
create table #TB([name] varchar(5),[num1] int,[num2] int)
insert #TB
select 'lucy',25,42 union all
select 'lucky',741,35 union all
select 'smith',35,621 union all
select 'hello',253,42
--------------开始查询--------------------------select *,NUM1*1.00/NUM2 from #TB
UNION ALL
SELECT '合计',SUM(NUM1),SUM(NUM2),SUM(NUM1)*1.00/SUM(NUM2) FROM #TB
----------------结果----------------------------
/* 
（所影响的行数为 4 行）name  num1        num2                                    
----- ----------- ----------- --------------------------- 
lucy  25          42          .5952380952380
lucky 741         35          21.1714285714285
smith 35          621         .0563607085346
hello 253         42          6.0238095238095
合计    1054        740         1.4243243243243（所影响的行数为 5 行）
*/

Answer 2

----------------------------------------------------------------
-- Author  :SQL77(只为思齐老)
-- Date    :2010-01-25 18:11:48
-- Version:
--      Microsoft SQL Server  2000 - 8.00.194 (Intel X86) 
-- Aug  6 2000 00:57:48 
-- Copyright (c) 1988-2000 Microsoft Corporation
-- Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据：#TB
if object_id('tempdb.dbo.#TB') is not null drop table #TB
go 
create table #TB([name] varchar(5),[num1] int,[num2] int)
insert #TB
select 'lucy',25,42 union all
select 'lucky',741,35 union all
select 'smith',35,621 union all
select 'hello',253,42
--------------开始查询--------------------------select *,NUM1*1.0000/NUM2 NUM3 from #TB
UNION ALL
SELECT '合计',SUM(NUM1),SUM(NUM2),SUM(NUM1)*1.0000/SUM(NUM2) FROM #TB
----------------结果----------------------------
/* 
（所影响的行数为 4 行）name  num1        num2        NUM3                          
----- ----------- ----------- ----------------------------- 
lucy  25          42          .595238095238095
lucky 741         35          21.171428571428571
smith 35          621         .056360708534621
hello 253         42          6.023809523809523
合计    1054        740         1.424324324324324（所影响的行数为 5 行）
*/

Answer 3

----------------------------------------------------------------
-- Author  :SQL77(只为思齐老)
-- Date    :2010-01-25 18:11:48
-- Version:
--      Microsoft SQL Server  2000 - 8.00.194 (Intel X86) 
-- Aug  6 2000 00:57:48 
-- Copyright (c) 1988-2000 Microsoft Corporation
-- Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据：#TB
if object_id('tempdb.dbo.#TB') is not null drop table #TB
go 
create table #TB([name] varchar(5),[num1] int,[num2] int)
insert #TB
select 'lucy',25,42 union all
select 'lucky',741,35 union all
select 'smith',35,621 union all
select 'hello',253,42
--------------开始查询--------------------------select *,CONVERT(DEC(18,9),NUM1*1.0/NUM2) NUM3 from #TB
UNION ALL
SELECT '合计',SUM(NUM1),SUM(NUM2),CONVERT(DEC(18,9),SUM(NUM1)*1.0/SUM(NUM2)) FROM #TB
----------------结果----------------------------
/* （所影响的行数为 4 行）name  num1        num2        NUM3                 
----- ----------- ----------- -------------------- 
lucy  25          42          .595238095
lucky 741         35          21.171428571
smith 35          621         .056360709
hello 253         42          6.023809524
合计    1054        740         1.424324324（所影响的行数为 5 行）
*/小数位数修改一下

Answer 4

--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb]([name] varchar(5),[num1] int,[num2] int)
insert [tb]
select 'lucy',25,42 union all
select 'lucky',741,35 union all
select 'smith',35,621 union all
select 'hello',253,42select * from [tb]--
select *,[num3]=convert(decimal(18,9),num1*0.01/num2)
from [tb]
union all
SELECT 'SUM',SUM(NUM1),SUM(NUM2),convert(decimal(18,9),SUM(NUM1)*1.0000/SUM(NUM2)) FROM [tb]
--结果
/*
name num1 num2 num3
lucy 25 42 0.005952381
lucky 741 35 0.211714286
smith 35 621 0.000563607
hello 253 42 0.060238095
SUM 1054 740 1.424324324
*/

Answer 5

--表结构及记录：
create table #tb1(name varchar(10),num1 int,num2 int)
insert into #tb1
select 'lucy',25,42 union all
select 'lucky',741,35 union all
select 'smith',35,621 union all
select 'hello',253,42语句一：
select name,num1,num2,((case when num1>num2 then num2 else num1 end)*1.00000/(case when num1>num2 then num1 else num2 end)) num3
from #tb1
union all
select '合计',sum(num1),sum(num2),((case when sum(num1)>sum(num2) then sum(num2) else sum(num1) end)*1.00000/(case when sum(num1)>sum(num2) then sum(num1) else sum(num2) end)) num3
from #tb1结果一：[b][/b]
lucy 25 42 0.5952380952380952
lucky 741 35 0.0472334682860998
smith 35 621 0.0563607085346215
hello 253 42 0.1660079051383399
合计 1054 740 0.7020872865275142
语句二：
select name,num1,num2,num1*1.00000/num2 num3 from #tb1
union all
select '合计',sum(num1),sum(num2),sum(num1)*1.00000/sum(num2) num3 from #tb1结果二：
lucy 25 42 0.5952380952380952
lucky 741 35 21.1714285714285714
smith 35 621 0.0563607085346215
hello 253 42 6.0238095238095238
合计 1054 740 1.4243243243243243

Answer 6

SELECT CASE WHEN (grouping(name) = 1) THEN '总计' ELSE name END AS name, 
      SUM(num1) AS num1, SUM(num2) AS num2, SUM(num1) * 1.0000 / SUM(num2) 
      AS NUM3
FROM TB
GROUP BY name WITH ROLLUP