--tryselect F1, Amount=sum(Amount), price=sum(price), Count=sum(Count) from A group by F1 union all select 'ALL', NULL, NULL, sum(Count) from A
if object_id('pubs..tb') is not null drop table tb gocreate table tb ( F1 varchar(10), Amount decimal(18,2), price decimal(18,2), Count decimal(18,2) )insert into tb(F1,Amount,price,Count) values('A', 1 , 0.5, 0.5) insert into tb(F1,Amount,price,Count) values('A', 2 , 0.5, 1) insert into tb(F1,Amount,price,Count) values('B', 10, 1 , 10) select isnull(F1,'ALL') as F1,sum(Amount) as Amount,sum(price) as price,sum(Count) as count from tb group by f1 WITH ROLLUPdrop table tb
F1 Amount price count ---------- ------ ----- ----- A 3.00 1.00 1.50 B 10.00 1.00 10.00 ALL 13.00 2.00 11.50(所影响的行数为 3 行)
如果还有一列F1 Amount price Count Type A 1 0.5 0.5 C A 2 0.5 1 C B 10 1 10 N我要得到 F1 Amount price count TYPE A 3 0.5 1.5 C B 10 1 10 N All 11.5 那怎么写呢
if object_id('pubs..tb') is not null drop table tb gocreate table tb ( F1 varchar(10), Amount decimal(8,2), price decimal(8,2), Count decimal(8,2), type varchar(1) )insert into tb(F1,Amount,price,Count,type) values('A', 1 , 0.5, 0.5,'C') insert into tb(F1,Amount,price,Count,type) values('A', 2 , 0.5, 1 ,'C') insert into tb(F1,Amount,price,Count,type) values('B', 10, 1 , 10,'N')select m.* , n.type from ( select isnull(F1,'ALL') as F1,sum(Amount) as Amount,sum(price) as price,sum(Count) as count from tb group by f1 WITH ROLLUP ) m left join ( select f1,max(type) as type from tb group by f1 ) n on m.f1 = n.f1drop table tb
if object_id('pubs..tb') is not null drop table tb gocreate table tb ( F1 varchar(10), Amount decimal(8,2), price decimal(8,2), Count decimal(8,2), type varchar(1) )insert into tb(F1,Amount,price,Count,type) values('A', 1 , 0.5, 0.5,'C') insert into tb(F1,Amount,price,Count,type) values('A', 2 , 0.5, 1 ,'C') insert into tb(F1,Amount,price,Count,type) values('B', 10, 1 , 10,'N')select m.* , isnull(n.type,'') as type from ( select isnull(F1,'ALL') as F1,sum(Amount) as Amount,sum(price) as price,sum(Count) as count from tb group by f1 WITH ROLLUP ) m left join ( select f1,max(type) as type from tb group by f1 ) n on m.f1 = n.f1drop table tb
F1 Amount price count type ---------- ------ ----- ----- ---- A 3.00 1.00 1.50 C B 10.00 1.00 10.00 N ALL 13.00 2.00 11.50 (所影响的行数为 3 行)
F1 Amount price count type ---------- ------ ----- ----- ---- A 3.00 1.00 1.50 C B 10.00 1.00 10.00 N ALL 11.50 Top dawugui(潇洒老乌龟) ALL结果不要 Amount, price 怎么写
if object_id('pubs..tb') is not null drop table tb gocreate table tb ( F1 varchar(10), Amount decimal(18,2), price decimal(18,2), Count decimal(18,2), type varchar(1) )insert into tb(F1,Amount,price,Count,type) values('A', 1 , 0.5, 0.5,'C') insert into tb(F1,Amount,price,Count,type) values('A', 2 , 0.5, 1 ,'C') insert into tb(F1,Amount,price,Count,type) values('B', 10, 1 , 10,'N')select m.* , isnull(n.type,'') as type from ( select F1,sum(Amount) as Amount,sum(price) as price,sum(Count) as count from tb group by f1 union all select F1='ALL',Amount=null,price = null,sum(Count) as count from tb ) m left join ( select f1,max(type) as type from tb group by f1 ) n on m.f1 = n.f1drop table tb--result F1 Amount price count type ---------- ------ ----- ----- ---- A 3.00 1.00 1.50 C B 10.00 1.00 10.00 N ALL null null 11.50 (所影响的行数为 3 行)
if object_id('pubs..tb') is not null drop table tb gocreate table tb ( F1 varchar(10), Amount decimal(18,2), price decimal(18,2), Count decimal(18,2), type varchar(1) )insert into tb(F1,Amount,price,Count,type) values('A', 1 , 0.5, 0.5,'C') insert into tb(F1,Amount,price,Count,type) values('A', 2 , 0.5, 1 ,'C') insert into tb(F1,Amount,price,Count,type) values('B', 10, 1 , 10,'N')select m.* , isnull(n.type,'') as type from ( select F1,cast(sum(Amount) as varchar(10)) as Amount,cast(sum(price) as varchar(10)) as price,cast(sum(Count) as varchar(10)) as count from tb group by f1 union all select F1='ALL',Amount='',price = '',cast(sum(Count) as varchar(10)) as count from tb ) m left join ( select f1,max(type) as type from tb group by f1 ) n on m.f1 = n.f1drop table tb--result F1 Amount price count type ---------- ---------- ---------- ---------- ---- A 3.00 1.00 1.50 C B 10.00 1.00 10.00 N ALL 11.50 (所影响的行数为 3 行)
直接這樣就可以Select F1, Rtrim(Amount) As Amount, Rtrim(price) As price, [count], type From tb Union All Select 'ALL', '', '', SUM([count]), '' From tb
看錯了,改下Select F1, Rtrim(SUM(Amount)) As Amount, Rtrim(price) As price, SUM([count]) As [count], type From tb Group By F1, price, type Union All Select 'ALL', '', '', SUM([count]), '' From tb
if object_id('pubs..tb') is not null drop table tb gocreate table tb ( F1 varchar(10), Amount decimal(18,2), price decimal(18,2), Count decimal(18,2), type varchar(1) )insert into tb(F1,Amount,price,Count,type) values('A', 1 , 0.5, 0.5,'C') insert into tb(F1,Amount,price,Count,type) values('A', 2 , 0.5, 1 ,'C') insert into tb(F1,Amount,price,Count,type) values('B', 10, 1 , 10,'N')Select F1, Rtrim(SUM(Amount)) As Amount, Rtrim(price) As price, SUM([count]) As [count], type From tb Group By F1, price, type Union All Select 'ALL', '', '', SUM([count]), '' From tbDrop Table tb--Result /* F1 Amount price count type A 3.00 0.50 1.50 C B 10.00 1.00 10.00 N ALL 11.50 */
if object_id('pubs..tb') is not null drop table tb gocreate table tb ( F1 varchar(10), Amount decimal(18,2), price decimal(18,2), Count decimal(18,2), type varchar(1) )insert into tb(F1,Amount,price,Count,type) values('A', 1 , 0.5, 0.5,'C') insert into tb(F1,Amount,price,Count,type) values('A', 2 , 0.5, 1 ,'C') insert into tb(F1,Amount,price,Count,type) values('B', 10, 1 , 10,'N') select F1,cast(sum(Amount) as varchar(10)) as Amount,cast(sum(price) as varchar(10)) as price,cast(sum(Count) as varchar(10)) as count , max(type) as type from tb group by f1 union all select F1='ALL',Amount='',price = '',cast(sum(Count) as varchar(10)) as count , type = '' from tb drop table tbF1 Amount price count type ---------- ---------- ---------- ---------- ---- A 3.00 1.00 1.50 C B 10.00 1.00 10.00 N ALL 11.50 (所影响的行数为 3 行)
A 3.00 1.00 1.50 C-------------這一行應該有問題,price應該是不需要匯總的。
--希望对你有作用,不知道理解的是不是简单了SELECT F1 , CAST(SUM(Amount) as nvarchar) AS Amount , CAST (AVG(price) as nvarchar) AS price , sum([count]) AS [count], TYPE -- 或者min,max(单价) FROM (select 'A' as F1 , 1 as Amount, 0.5 as price , 0.5 as [count] , 'C' as TYPE union select 'A', 2 , 0.5 , 1 , 'C' union select 'B', 10 , 1 , 10, 'N') AS TABLE_ONE group by F1,TYPEUNION SELECT 'ALL' , '' , '' , sum([count]), '' FROM (select 'A' as F1 , 1 as Amount, 0.5 as price , 0.5 as [count] , 'C' as TYPE union select 'A', 2 , 0.5 , 1 , 'C' union select 'B', 10 , 1 , 10, 'N') AS TABLE_ONE ORDER BY [count]
from A
group by F1
union all
select 'ALL', NULL, NULL, sum(Count) from A
drop table tb
gocreate table tb
(
F1 varchar(10),
Amount decimal(18,2),
price decimal(18,2),
Count decimal(18,2)
)insert into tb(F1,Amount,price,Count) values('A', 1 , 0.5, 0.5)
insert into tb(F1,Amount,price,Count) values('A', 2 , 0.5, 1)
insert into tb(F1,Amount,price,Count) values('B', 10, 1 , 10)
select isnull(F1,'ALL') as F1,sum(Amount) as Amount,sum(price) as price,sum(Count) as count
from tb
group by f1
WITH ROLLUPdrop table tb
---------- ------ ----- -----
A 3.00 1.00 1.50
B 10.00 1.00 10.00
ALL 13.00 2.00 11.50(所影响的行数为 3 行)
A 1 0.5 0.5 C
A 2 0.5 1 C
B 10 1 10 N我要得到
F1 Amount price count TYPE
A 3 0.5 1.5 C
B 10 1 10 N
All 11.5
那怎么写呢
drop table tb
gocreate table tb
(
F1 varchar(10),
Amount decimal(8,2),
price decimal(8,2),
Count decimal(8,2),
type varchar(1)
)insert into tb(F1,Amount,price,Count,type) values('A', 1 , 0.5, 0.5,'C')
insert into tb(F1,Amount,price,Count,type) values('A', 2 , 0.5, 1 ,'C')
insert into tb(F1,Amount,price,Count,type) values('B', 10, 1 , 10,'N')select m.* , n.type from
(
select isnull(F1,'ALL') as F1,sum(Amount) as Amount,sum(price) as price,sum(Count) as count
from tb
group by f1
WITH ROLLUP
) m
left join
(
select f1,max(type) as type from tb group by f1
) n
on m.f1 = n.f1drop table tb
drop table tb
gocreate table tb
(
F1 varchar(10),
Amount decimal(8,2),
price decimal(8,2),
Count decimal(8,2),
type varchar(1)
)insert into tb(F1,Amount,price,Count,type) values('A', 1 , 0.5, 0.5,'C')
insert into tb(F1,Amount,price,Count,type) values('A', 2 , 0.5, 1 ,'C')
insert into tb(F1,Amount,price,Count,type) values('B', 10, 1 , 10,'N')select m.* , isnull(n.type,'') as type from
(
select isnull(F1,'ALL') as F1,sum(Amount) as Amount,sum(price) as price,sum(Count) as count
from tb
group by f1
WITH ROLLUP
) m
left join
(
select f1,max(type) as type from tb group by f1
) n
on m.f1 = n.f1drop table tb
---------- ------ ----- ----- ----
A 3.00 1.00 1.50 C
B 10.00 1.00 10.00 N
ALL 13.00 2.00 11.50 (所影响的行数为 3 行)
---------- ------ ----- ----- ----
A 3.00 1.00 1.50 C
B 10.00 1.00 10.00 N
ALL 11.50 Top
dawugui(潇洒老乌龟)
ALL结果不要 Amount, price 怎么写
drop table tb
gocreate table tb
(
F1 varchar(10),
Amount decimal(18,2),
price decimal(18,2),
Count decimal(18,2),
type varchar(1)
)insert into tb(F1,Amount,price,Count,type) values('A', 1 , 0.5, 0.5,'C')
insert into tb(F1,Amount,price,Count,type) values('A', 2 , 0.5, 1 ,'C')
insert into tb(F1,Amount,price,Count,type) values('B', 10, 1 , 10,'N')select m.* , isnull(n.type,'') as type from
(
select F1,sum(Amount) as Amount,sum(price) as price,sum(Count) as count
from tb
group by f1
union all
select F1='ALL',Amount=null,price = null,sum(Count) as count from tb
) m
left join
(
select f1,max(type) as type from tb group by f1
) n
on m.f1 = n.f1drop table tb--result
F1 Amount price count type
---------- ------ ----- ----- ----
A 3.00 1.00 1.50 C
B 10.00 1.00 10.00 N
ALL null null 11.50 (所影响的行数为 3 行)
drop table tb
gocreate table tb
(
F1 varchar(10),
Amount decimal(18,2),
price decimal(18,2),
Count decimal(18,2),
type varchar(1)
)insert into tb(F1,Amount,price,Count,type) values('A', 1 , 0.5, 0.5,'C')
insert into tb(F1,Amount,price,Count,type) values('A', 2 , 0.5, 1 ,'C')
insert into tb(F1,Amount,price,Count,type) values('B', 10, 1 , 10,'N')select m.* , isnull(n.type,'') as type from
(
select F1,cast(sum(Amount) as varchar(10)) as Amount,cast(sum(price) as varchar(10)) as price,cast(sum(Count) as varchar(10)) as count
from tb
group by f1
union all
select F1='ALL',Amount='',price = '',cast(sum(Count) as varchar(10)) as count from tb
) m
left join
(
select f1,max(type) as type from tb group by f1
) n
on m.f1 = n.f1drop table tb--result
F1 Amount price count type
---------- ---------- ---------- ---------- ----
A 3.00 1.00 1.50 C
B 10.00 1.00 10.00 N
ALL 11.50 (所影响的行数为 3 行)
F1,
Rtrim(Amount) As Amount,
Rtrim(price) As price,
[count],
type
From tb
Union All
Select
'ALL',
'',
'',
SUM([count]),
''
From tb
F1,
Rtrim(SUM(Amount)) As Amount,
Rtrim(price) As price,
SUM([count]) As [count],
type
From tb
Group By F1, price, type
Union All
Select
'ALL',
'',
'',
SUM([count]),
''
From tb
drop table tb
gocreate table tb
(
F1 varchar(10),
Amount decimal(18,2),
price decimal(18,2),
Count decimal(18,2),
type varchar(1)
)insert into tb(F1,Amount,price,Count,type) values('A', 1 , 0.5, 0.5,'C')
insert into tb(F1,Amount,price,Count,type) values('A', 2 , 0.5, 1 ,'C')
insert into tb(F1,Amount,price,Count,type) values('B', 10, 1 , 10,'N')Select
F1,
Rtrim(SUM(Amount)) As Amount,
Rtrim(price) As price,
SUM([count]) As [count],
type
From tb
Group By F1, price, type
Union All
Select
'ALL',
'',
'',
SUM([count]),
''
From tbDrop Table tb--Result
/*
F1 Amount price count type
A 3.00 0.50 1.50 C
B 10.00 1.00 10.00 N
ALL 11.50
*/
drop table tb
gocreate table tb
(
F1 varchar(10),
Amount decimal(18,2),
price decimal(18,2),
Count decimal(18,2),
type varchar(1)
)insert into tb(F1,Amount,price,Count,type) values('A', 1 , 0.5, 0.5,'C')
insert into tb(F1,Amount,price,Count,type) values('A', 2 , 0.5, 1 ,'C')
insert into tb(F1,Amount,price,Count,type) values('B', 10, 1 , 10,'N') select F1,cast(sum(Amount) as varchar(10)) as Amount,cast(sum(price) as varchar(10)) as price,cast(sum(Count) as varchar(10)) as count , max(type) as type
from tb
group by f1
union all
select F1='ALL',Amount='',price = '',cast(sum(Count) as varchar(10)) as count , type = '' from tb drop table tbF1 Amount price count type
---------- ---------- ---------- ---------- ----
A 3.00 1.00 1.50 C
B 10.00 1.00 10.00 N
ALL 11.50 (所影响的行数为 3 行)
--希望对你有作用,不知道理解的是不是简单了SELECT F1 , CAST(SUM(Amount) as nvarchar) AS Amount , CAST (AVG(price) as nvarchar) AS price , sum([count]) AS [count], TYPE -- 或者min,max(单价)
FROM
(select 'A' as F1 , 1 as Amount, 0.5 as price , 0.5 as [count] , 'C' as TYPE
union
select 'A', 2 , 0.5 , 1 , 'C'
union
select 'B', 10 , 1 , 10, 'N') AS TABLE_ONE
group by F1,TYPEUNION
SELECT 'ALL' , '' , '' , sum([count]), ''
FROM
(select 'A' as F1 , 1 as Amount, 0.5 as price , 0.5 as [count] , 'C' as TYPE
union
select 'A', 2 , 0.5 , 1 , 'C'
union
select 'B', 10 , 1 , 10, 'N') AS TABLE_ONE
ORDER BY [count]