各位大侠,我刚接触数据库没多久,只是菜鸟级别的,最近在做数据库的时候遇到了问题,希望各位大侠帮我解决一下。具体问题如下:现有这样的数据表
CLIENT_NAME ITEM_CODE QTY AMT
AAAA S 1 10
BBBB C 2 30
CCCC M 1 10
S 1 40
C 1 50要把上表变成下面的这种形式。CLIENT_NAME ITEM_CODE QTY AMT
AAAA S 1 10
总计 1 10
BBBB C 2 30
总计 2 30
CCCC M 1 10
S 1 40
C 1 50
总计 3 100对每个client_name 下的,qty, amt 求和,然后在每一个client_name最后一行显示出来。希望各位大侠 多指教啊!
CLIENT_NAME ITEM_CODE QTY AMT
AAAA S 1 10
BBBB C 2 30
CCCC M 1 10
S 1 40
C 1 50要把上表变成下面的这种形式。CLIENT_NAME ITEM_CODE QTY AMT
AAAA S 1 10
总计 1 10
BBBB C 2 30
总计 2 30
CCCC M 1 10
S 1 40
C 1 50
总计 3 100对每个client_name 下的,qty, amt 求和,然后在每一个client_name最后一行显示出来。希望各位大侠 多指教啊!
UNION 操作符用于合并两个或多个 SELECT 语句的结果集,
但UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时,每条 SELECT 语句中的列的顺序必须相同。
(
client_name varchar(10),
item_code varchar(10),
qty int,
amt int
);INSERT INTO @tb
SELECT 'AAAA','S',1,10
UNION
SELECT 'BBBB','C',2,30
UNION
SELECT 'CCCC','M',1,10
UNION
SELECT 'CCCC','S',1,40
UNION
SELECT 'CCCC','C',1,50
;WITH c1 AS
(
SELECT t.client_name,
ISNULL(t.item_code,'总计') item_code,
SUM(t.qty) qty,
SUM(t.amt) amt
FROM @tb t
GROUP BY t.item_code,t.client_name WITH CUBE
)
SELECT *
FROM c1
WHERE client_name IS NOT NULL--执行结果:
--AAAA S 1 10
--AAAA 总计 1 10
--BBBB C 2 30
--BBBB 总计 2 30
--CCCC C 1 50
--CCCC M 1 10
--CCCC S 1 40
--CCCC 总计 3 100
好像跟最终结果稍微有点出入……麻烦!
go
--CLIENT_NAME ITEM_CODE QTY AMT
create table [tb]([CLIENT_NAME ] varchar(3),[ITEM_CODE] varchar(3),[QTY] int,[AMT] int)
insert [tb]
select 'AAA','S',1,10
union
select 'BBB','C',2,30
union
select 'CCC','M',1,10
union
select 'CCC','S',1,40
union
select 'CCC','C',1,50
goselect CLIENT_NAME,ITEM_CODE,sum(QTY) QTY,sum(AMT) AMT
from tb
group by CLIENT_NAME, ITEM_CODE with rollup
having CLIENT_NAME is not null
DECLARE @tb table
(
client_name varchar(10),
item_code varchar(10),
qty int,
amt int
);
INSERT INTO @tb
SELECT 'AAAA','S',1,10
UNION
SELECT 'BBBB','C',2,30
UNION
SELECT 'CCCC','M',1,10
UNION
SELECT 'CCCC','S',1,40
UNION
SELECT 'CCCC','C',1,50
SELECT * FROM
(
SELECT * FROM @tb
UNION ALL
SELECT client_name,'总计',SUM(qty) qty,SUM(amt) amt
FROM @tb
GROUP BY client_name
) a
ORDER BY a.client_name,a.item_code
/*
(5 行受影响)
client_name item_code qty amt
----------- ---------- ----------- -----------
AAAA S 1 10
AAAA 总计 1 10
BBBB C 2 30
BBBB 总计 2 30
CCCC C 1 50
CCCC M 1 10
CCCC S 1 40
CCCC 总计 3 100
(8 行受影响)
*/
DECLARE @tb table
(
client_name varchar(10),
item_code varchar(10),
qty int,
amt int
);
INSERT INTO @tb
SELECT 'AAAA','S',1,10
UNION
SELECT 'BBBB','C',2,30
UNION
SELECT 'CCCC','M',1,10
UNION
SELECT 'CCCC','S',1,40
UNION
SELECT 'CCCC','C',1,50
;
SELECT * into @tb2 FROM
(
SELECT * FROM @tb
UNION ALL
SELECT client_name,'总计',SUM(qty) qty,SUM(amt) amt
FROM @tb
GROUP BY client_name
) a
ORDER BY a.client_name,a.item_codeUpdate @tb2 Set client_name="总计",item_code="" Where item_code="总计";Select * from @tb2;
/*
client_name item_code qty amt
----------- ---------- ----------- -----------
AAAA S 1 10
总计 1 10
BBBB C 2 30
总计 2 30
CCCC C 1 50
CCCC M 1 10
CCCC S 1 40
总计 3 100
*/
四楼的您好,这个应该不行吧! with cube 就会把 ITEM_CODE 分组的也相加,例子中的第二第五行都是C,
会把他们同组相加的.没别的意思,就是我开始也是想到这种方法!共同学习
select case ITEM_CODE when '' then '总计' else CLIENT_NAME end CLIENT_NAME, ITEM_CODE,QTY,AMT FROM (select CLIENT_NAME ,isnull(ITEM_CODE,'') ITEM_CODE,sum(QTY) QTY,sum(AMT) AMT
from tb
group by CLIENT_NAME, ITEM_CODE with rollup
having CLIENT_NAME is not null ) BAAA S 1 10
总计 1 10
BBB C 2 30
总计 2 30
CCC C 1 50
CCC M 1 10
CCC S 1 40
总计 3 100
就求1分