34,594
社区成员
发帖
与我相关
我的任务
分享
--表格数据如下:
CREATE TABLE [dbo].[Table_AB](
[ID] [int] IDENTITY(1,1) NOT NULL,
[StrA] [nvarchar](50) NULL,
[NumB] [numeric](18, 2) NOT NULL
) ON [PRIMARY]
GO
INSERT INTO [temp].[dbo].[Table_AB]
([StrA]
,[NumB])
VALUES
('A'
,20.00)
INSERT INTO [temp].[dbo].[Table_AB]
([StrA]
,[NumB])
VALUES
('B'
,30.00)
INSERT INTO [temp].[dbo].[Table_AB]
([StrA]
,[NumB])
VALUES
('C'
,40.00)
INSERT INTO [temp].[dbo].[Table_AB]
([StrA]
,[NumB])
VALUES
('D'
,50.00)
GO
--结果如下:
SELECT * FROM (
SELECT StrA,NumB FROM Table_AB
WHERE StrA IN ('A','B')
UNION ALL
SELECT StrA+'合计',SUM(NumB) as NumB FROM Table_AB
WHERE StrA IN ('A','B')
GROUP BY StrA
UNION ALL
SELECT 'Z总计' AS StrA,SUM(NumB) as NumB FROM Table_AB
WHERE StrA IN ('A','B')
) AS A ORDER BY StrA
---条件是活的,不定的。 请高手们用 视图,表值函数,只要不用【存储过程】就行。
create view view_Table_AB
as
select case when (GROUPING(StrA)=1) then 'ALL'
else ISNULL (StrA,'NULL')
end as StrA,
SUM(NumB) as SUMB
from Table_AB
group by StrA with cube
select * from view_Table_AB
select *
from view_Table_AB
where StrA not in ('ALL')
union all
select StrA+N'总计' ,SUM(SUMB)
from view_Table_AB
where StrA not in ('ALL')
group by StrA
union all
select StrA as 总计,SUMB
from view_Table_AB
where StrA ='ALL'
StrA SUMB
---------------------------------------------------- ---------------------------------------
A 20.00
B 30.00
C 40.00
D 50.00
A总计 20.00
B总计 30.00
C总计 40.00
D总计 50.00
ALL 140.00
(9 行受影响)
SELECT 'StrA' = ISNULL(( CASE WHEN GROUPING(ID) = 0 THEN StrA
ELSE StrA + '小计'
END ), '总计') ,
--'StrA2' = ( CASE WHEN ID IS NOT NULL THEN StrA
-- ELSE StrA + '小计'
-- END ) ,
SUM(t.numb) AS 'TotalNum' ,
ID
FROM dbo.Table_AB t
GROUP BY t.StrA ,
t.ID
WITH ROLLUP
create function dbo.fn_tb(@strA Nvarchar(100))
returns @t table(a [nvarchar](50),b [numeric](18, 2))
as
begin
insert into @t(a,b)
SELECT *
FROM
(
SELECT StrA,NumB FROM Table_AB
WHERE charindex(','+StrA+',',','+@stra+',')>0
UNION ALL
SELECT StrA+'合计',SUM(NumB) as NumB FROM Table_AB
WHERE charindex(','+StrA+',',','+@stra+',')>0
GROUP BY StrA
UNION ALL
SELECT 'Z总计' AS StrA,SUM(NumB) as NumB FROM Table_AB
WHERE charindex(','+StrA+',',','+@stra+',')>0
) AS A
return
end
go
查询:
select *
from dbo.fn_tb('A,B')
/*
a b
A 20.00
B 30.00
A合计 20.00
B合计 30.00
Z总计 50.00
*/