------------------------------------
-- Author: happyflystone
-- Version:V1.001
-- Date:2008-12-06 17:33:36
-------------------------------------- Test Data: ta
If object_id('ta') is not null
Drop table ta
Go
Create table ta(ID int,PRO nvarchar(2),NUM int,PRICE numeric(3,2))
Go
Insert into ta
select 1,'AA',5,6.00 union all
select 2,'BB',10,2.00 union all
select 3,'CC',1,3.00
Go
--Start
Select id,pro,num,price, price*num as s
from ta
--Result:
/*
id pro num price s
----------- ---- ----------- ----- ----------------
1 AA 5 6.00 30.00
2 BB 10 2.00 20.00
3 CC 1 3.00 3.00(所影响的行数为 3 行)
*/
--End
-- Author: happyflystone
-- Version:V1.001
-- Date:2008-12-06 17:33:36
-------------------------------------- Test Data: ta
If object_id('ta') is not null
Drop table ta
Go
Create table ta(ID int,PRO nvarchar(2),NUM int,PRICE numeric(3,2))
Go
Insert into ta
select 1,'AA',5,6.00 union all
select 2,'BB',10,2.00 union all
select 3,'CC',1,3.00
Go
--Start
Select id,pro,num,price, price*num as s
from ta
--Result:
/*
id pro num price s
----------- ---- ----------- ----- ----------------
1 AA 5 6.00 30.00
2 BB 10 2.00 20.00
3 CC 1 3.00 3.00(所影响的行数为 3 行)
*/
--End
select PRO,NUM=sum(num),PRICE=avg(price)
FROM TA
group by pro
求总数,平均单价
2 BB 10 2.00
3 CC 1 3.00
同时得到
NUM的汇总 16在一个语句能否实现?
2 BB 10 2.00
3 CC 1 3.00
同时得到
NUM的汇总 16在一个语句能否实现?
-- Author: happyflystone
-- Version:V1.001
-- Date:2008-12-06 17:33:36
-------------------------------------- Test Data: ta
If object_id('ta') is not null
Drop table ta
Go
Create table ta(ID int,PRO nvarchar(2),NUM int,PRICE numeric(3,2))
Go
Insert into ta
select 1,'AA',5,6.00 union all
select 2,'BB',10,2.00 union all
select 3,'CC',1,3.00
Go--Start
Select id,pro,num,price, (select sum(num) from ta) as s
from ta--Result:
/*id pro num price s
----------- ---- ----------- ----- -----------
1 AA 5 6.00 16
2 BB 10 2.00 16
3 CC 1 3.00 16(所影响的行数为 3 行)*/
--End
-- Author: happyflystone
-- Version:V1.001
-- Date:2008-12-06 17:33:36
-------------------------------------- Test Data: ta
If object_id('ta') is not null
Drop table ta
Go
Create table ta(ID int,PRO nvarchar(2),NUM int,PRICE numeric(3,2))
Go
Insert into ta
select 1,'AA',5,6.00 union all
select 2,'BB',10,2.00 union all
select 3,'CC',1,3.00
Go--Start
Select
case when grouping(id) = 0 then ltrim(id) else 'sum' end as id,
case when grouping(id) = 0 then max(pro) else '' end as pro,
sum(num) as num,
case when grouping(id) = 0 then ltrim(max(price)) else '' end as price
from ta
group by id
with rollup
--Result:
/*
id pro num price
------------ ---- ----------- ----------------------------------------
1 AA 5 6.00
2 BB 10 2.00
3 CC 1 3.00
sum 16 (所影响的行数为 4 行)*/
--End