SELECT bj, mc , SUM(fenshu)FROM TB GROUP BY bj, mc
SELECT bj,mc,sum([fenshu]) as [fenshu] FROM TB GROUP BY bj,mc
DECLARE @TB TABLE([bj] INT, [mc] NVARCHAR(1), [fenshu] INT) INSERT @TB SELECT 1, N'一', 50 UNION ALL SELECT 1, N'一', 50 UNION ALL SELECT 1, N'一', 50 UNION ALL SELECT 2, N'二', 50 UNION ALL SELECT 2, N'二', 50 UNION ALL SELECT 2, N'二', 50 UNION ALL SELECT 3, N'三', 50 UNION ALL SELECT 3, N'三', 50 UNION ALL SELECT 3, N'三', 50 UNION ALL SELECT 3, N'三', 50SELECT isnull(rtrim(bj),N'TOTAL') AS bj,ISNULL(rtrim(mc),'') AS MC,sum([fenshu]) as [fenshu] FROM @TB GROUP BY bj,mc WITH ROLLUP HAVING (GROUPING(bj)=1 AND GROUPING(mc)=1) OR GROUPING(mc)=0 /* bj MC fenshu ------------ ---- ----------- 1 一 150 2 二 150 3 三 200 TOTAL 500 */
create table TB([bj] int, [mc] nvarchar(1), [fenshu] int) insert tb select 1, '一', 50 union all select 1, '一', 50 union all select 1, '一', 50 union all select 2, '二', 50 union all select 2, '二', 50 union all select 2, '二', 50 union all select 3, '三', 50 union all select 3, '三', 50 union all select 3, '三', 50 union all select 3, '三', 50 SELECT [bj],[mc],[fenshu]=SUM([fenshu]) FROM TB GROUP BY [bj],[mc] ORDER BY [bj]bj mc fenshu ----------- ---- ----------- 1 一 150 2 二 150 3 三 200(3 行受影响)
DROP TABLE TB create table TB([bj] int, [mc] nvarchar(1), [fenshu] int) insert tb select 1, '一', 50 union all select 1, '一', 50 union all select 1, '一', 50 union all select 2, '二', 50 union all select 2, '二', 50 union all select 2, '二', 50 union all select 3, '三', 50 union all select 3, '三', 50 union all select 3, '三', 50 union all select 3, '三', 50SELECT CAST([bj] AS VARCHAR(10))[bj] ,[mc],[fenshu]=SUM([fenshu]) FROM TB GROUP BY [bj],[mc] UNION ALL SELECT N'合計','',SUM([FENSHU]) FROM TB /*bj mc fenshu ---------- ---- ----------- 1 一 150 2 二 150 3 三 200 合計 500(影響 4 個資料列) */
create table TB1([bj] int, [mc] nvarchar(1), [fenshu] int) insert TB1 select 1, '一', 50 union all select 1, '二', 50 union all select 1, '三', 50 union all select 2, '一', 50 union all select 2, '二', 50 union all select 2, '三', 50 union all select 3, '一', 50 union all select 3, '二', 50 union all select 3, '三', 50 union all select 3, '四', 50SELECT * FROM ( SELECT CASE WHEN (GROUPING([bj]) = 1) THEN [bj] ELSE ISNULL([bj], 'UNKNOWN') END AS [bj] ,CASE WHEN (GROUPING([mc]) = 1) THEN '合计' ELSE ISNULL([mc], 'UNKNOWN') END AS [mc] ,[fenshu]=SUM([fenshu]) FROM TB1 GROUP BY [bj],[mc] with rollup HAVING GROUPING([bj]) = 0) A ORDER BY [bj],charindex([mc],'一二三四合计')bj mc fenshu ----------- ---- ----------- 1 一 50 1 二 50 1 三 50 1 合计 150 2 一 50 2 二 50 2 三 50 2 合计 150 3 一 50 3 二 50 3 三 50 3 四 50 3 合计 200(13 行受影响)
group by col1,col2 with cube 不晓得是不是楼主的意思。
1 一 50
1 一 50
1 一 50
2 二 50
2 二 50
2 二 50
3 三 50
3 三 50
3 三 50
3 三 50结果
1 一 150
2 二 150
3 三 200我想用到的语句group by bj,mc
在这个基础上用cube或者ROLLUP
FROM TB
GROUP BY bj,mc
INSERT @TB
SELECT 1, N'一', 50 UNION ALL
SELECT 1, N'一', 50 UNION ALL
SELECT 1, N'一', 50 UNION ALL
SELECT 2, N'二', 50 UNION ALL
SELECT 2, N'二', 50 UNION ALL
SELECT 2, N'二', 50 UNION ALL
SELECT 3, N'三', 50 UNION ALL
SELECT 3, N'三', 50 UNION ALL
SELECT 3, N'三', 50 UNION ALL
SELECT 3, N'三', 50SELECT isnull(rtrim(bj),N'TOTAL') AS bj,ISNULL(rtrim(mc),'') AS MC,sum([fenshu]) as [fenshu]
FROM @TB
GROUP BY bj,mc WITH ROLLUP
HAVING (GROUPING(bj)=1 AND GROUPING(mc)=1) OR GROUPING(mc)=0
/*
bj MC fenshu
------------ ---- -----------
1 一 150
2 二 150
3 三 200
TOTAL 500
*/
insert tb
select 1, '一', 50 union all
select 1, '一', 50 union all
select 1, '一', 50 union all
select 2, '二', 50 union all
select 2, '二', 50 union all
select 2, '二', 50 union all
select 3, '三', 50 union all
select 3, '三', 50 union all
select 3, '三', 50 union all
select 3, '三', 50
SELECT [bj],[mc],[fenshu]=SUM([fenshu])
FROM TB
GROUP BY [bj],[mc]
ORDER BY [bj]bj mc fenshu
----------- ---- -----------
1 一 150
2 二 150
3 三 200(3 行受影响)
create table TB([bj] int, [mc] nvarchar(1), [fenshu] int)
insert tb
select 1, '一', 50 union all
select 1, '一', 50 union all
select 1, '一', 50 union all
select 2, '二', 50 union all
select 2, '二', 50 union all
select 2, '二', 50 union all
select 3, '三', 50 union all
select 3, '三', 50 union all
select 3, '三', 50 union all
select 3, '三', 50SELECT CAST([bj] AS VARCHAR(10))[bj] ,[mc],[fenshu]=SUM([fenshu])
FROM TB
GROUP BY [bj],[mc]
UNION ALL
SELECT N'合計','',SUM([FENSHU]) FROM TB
/*bj mc fenshu
---------- ---- -----------
1 一 150
2 二 150
3 三 200
合計 500(影響 4 個資料列)
*/
insert TB1
select 1, '一', 50 union all
select 1, '二', 50 union all
select 1, '三', 50 union all
select 2, '一', 50 union all
select 2, '二', 50 union all
select 2, '三', 50 union all
select 3, '一', 50 union all
select 3, '二', 50 union all
select 3, '三', 50 union all
select 3, '四', 50SELECT * FROM (
SELECT CASE WHEN (GROUPING([bj]) = 1) THEN [bj]
ELSE ISNULL([bj], 'UNKNOWN') END AS [bj]
,CASE WHEN (GROUPING([mc]) = 1) THEN '合计'
ELSE ISNULL([mc], 'UNKNOWN') END AS [mc]
,[fenshu]=SUM([fenshu])
FROM TB1
GROUP BY [bj],[mc] with rollup
HAVING GROUPING([bj]) = 0) A
ORDER BY [bj],charindex([mc],'一二三四合计')bj mc fenshu
----------- ---- -----------
1 一 50
1 二 50
1 三 50
1 合计 150
2 一 50
2 二 50
2 三 50
2 合计 150
3 一 50
3 二 50
3 三 50
3 四 50
3 合计 200(13 行受影响)