34,593
社区成员
发帖
与我相关
我的任务
分享
DECLARE @TA TABLE([flbm] VARCHAR(6), [flmc] NVARCHAR(4))
INSERT @TA
SELECT '001', N'机械' UNION ALL
SELECT '001001', N'化工机械' UNION ALL
SELECT '001002', N'工程机械' UNION ALL
SELECT '002', N'电器' UNION ALL
SELECT '002001', N'家用电器' UNION ALL
SELECT '002002', N'企业电器'
DECLARE @TB TABLE([cpbm] VARCHAR(5), [flbm] VARCHAR(6), [sl] INT)
INSERT @TB
SELECT 'p0001', '001001', 50 UNION ALL
SELECT 'p0002', '001002', 45 UNION ALL
SELECT 'p0003', '002001', 13 UNION ALL
SELECT 'p0004', '002002', 12 UNION ALL
SELECT 'p0005', '001001', 17 UNION ALL
SELECT 'p0006', '001002', 11 UNION ALL
SELECT 'p0007', '002001', 19
SELECT ISNULL(a.flmc,N'合计') AS flmc,SUM(sl) AS sl
FROM @TB AS B JOIN @TA AS A
ON B.flbm=A.flbm OR LEFT(B.flbm,3)=A.flbm
GROUP BY A.flmc WITH ROLLUP
/*
flmc sl
---- -----------
工程机械 56
化工机械 67
企业电器 12
机械 123
家用电器 32
电器 44
合计 334
*/
DECLARE @TA TABLE([flbm] VARCHAR(6), [flmc] NVARCHAR(4))
INSERT @TA
SELECT '001', N'机械' UNION ALL
SELECT '001001', N'化工机械' UNION ALL
SELECT '001002', N'工程机械' UNION ALL
SELECT '002', N'电器' UNION ALL
SELECT '002001', N'家用电器' UNION ALL
SELECT '002002', N'企业电器'
DECLARE @TB TABLE([cpbm] VARCHAR(5), [flbm] VARCHAR(6), [sl] INT)
INSERT @TB
SELECT 'p0001', '001001', 50 UNION ALL
SELECT 'p0002', '001002', 45 UNION ALL
SELECT 'p0003', '002001', 13 UNION ALL
SELECT 'p0004', '002002', 12 UNION ALL
SELECT 'p0005', '001001', 17 UNION ALL
SELECT 'p0006', '001002', 11 UNION ALL
SELECT 'p0007', '002001', 19
select
case when grouping([大类])=1 then '总计' else [大类] end
,case when grouping(flmc)=1 then '总计' else flmc end
,sum(sl) as 'sl'
from (
select right(a.flmc,2) as '大类', a.flmc,sl
from @TA a join @TB b on a.flbm=b.flbm
) as T
group by [大类],[flmc] with rollup
--结果
电器 家用电器 32
电器 企业电器 12
电器 总计 44
机械 工程机械 56
机械 化工机械 67
机械 总计 123
总计 总计 167
SELECT ISNULL(a.flmc,N'合计') AS flmc,SUM(sl) AS sl
FROM @TB AS B JOIN @TA AS A
ON B.flbm=A.flbm OR LEFT(B.flbm,3)=A.flbm
GROUP BY A.flbm,A.flmc WITH ROLLUP
HAVING (A.flmc IS NOT NULL) OR (A.flmc IS NULL AND A.flbm IS NULL)
ORDER BY LEFT(ISNULL(A.flbm,'999999'),3), LEN(A.flbm) DESC
/*
flmc sl
---- -----------
化工机械 67
工程机械 56
机械 123
家用电器 32
企业电器 12
电器 44
合计 334
(7 row(s) affected)
*/
declare @table table (flbm varchar(6),flmc varchar(8))
insert into @table
select '001','机械' union all
select '001001','化工机械' union all
select '001002','工程机械' union all
select '002','电器' union all
select '002001','家用电器' union all
select '002002','企业电器'
--select * from @table
declare @spbm table (cpbm varchar(5),flbm varchar(6),sl int)
insert into @spbm
select 'p0001','001001',50 union all
select 'p0002','001002',45 union all
select 'p0003','002001',13 union all
select 'p0004','002002',12 union all
select 'p0005','001001',17 union all
select 'p0006','001002',11 union all
select 'p0007','002001',19
--select * from @spbm
select isnull(a.flmc,'合计'),sum(sl) from @spbm b
left join @table a on a.flbm=b.flbm or left(b.flbm,3)=a.flbm
group by a.flmc with rollup
/*
电器 44
工程机械 56
化工机械 67
机械 123
家用电器 32
企业电器 12
合计 334
*/