SQL算出合计的问题

Question

IF OBJECT_ID('[JsCostGX]')IS NOT NULL DROP TABLE  [JsCostGX]
GO
CREATE TABLE [JsCostGX] (
    [COMMU] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL CONSTRAINT [DF_JsCostGX_COMMU] DEFAULT (0),
    [CODE] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
    [GXTYPE] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
    [GXITEM] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
    [GXPRI] [money] NULL CONSTRAINT [DF_JsCostGX_GXPRI] DEFAULT (0),
    CONSTRAINT [PK_JsCostGX] PRIMARY KEY  CLUSTERED 
    (
        [CODE],
        [GXTYPE],
        [GXITEM]
    )  ON [PRIMARY] 
) ON [PRIMARY]
GOINSERT INTO JsCostGX(CODE, GXTYPE, GXITEM, GXPRI)
VALUES (N'123456', N'gx1', N'01', 3)
INSERT INTO JsCostGX(CODE, GXTYPE, GXITEM, GXPRI)
VALUES (N'123456', N'gx1', N'02', 6)
INSERT INTO JsCostGX(CODE, GXTYPE, GXITEM, GXPRI)
VALUES (N'123456', N'gx2', N'01', 3)
INSERT INTO JsCostGX(CODE, GXTYPE, GXITEM, GXPRI)
VALUES (N'123456', N'gx2', N'02', 4)
SELECT code, gxtype, gxitem ,gxpri  FROM (SELECT code, gxtype, gxitem ,gxpri ,1 ID FROM JsCostGX 
                                UNION ALL
                                SELECT code ,gxtype, ' ',SUM(gxpri ),2 ID FROM  JsCostGX  GROUP BY  code ,gxtype
)T ORDER BY CODE,GXTYPE,ID
/*code                 gxtype     gxitem     gxpri                 
-------------------- ---------- ---------- --------------------- 
123456               gx1        01         3.0000
123456               gx1        02         6.0000
123456               gx1                   9.0000
123456               gx2        01         3.0000
123456               gx2        02         4.0000
123456               gx2                   7.0000*/

Answer 1

CREATE TABLE [JsCostGX] (
    [COMMU] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL CONSTRAINT [DF_JsCostGX_COMMU] DEFAULT (0),
    [CODE] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
    [GXTYPE] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
    [GXITEM] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
    [GXPRI] [money] NULL CONSTRAINT [DF_JsCostGX_GXPRI] DEFAULT (0),
    CONSTRAINT [PK_JsCostGX] PRIMARY KEY  CLUSTERED 
    (
        [CODE],
        [GXTYPE],
        [GXITEM]
    )  ON [PRIMARY] 
) ON [PRIMARY]
GOINSERT INTO JsCostGX(CODE, GXTYPE, GXITEM, GXPRI)
VALUES (N'123456', N'gx1', N'01', 3)
INSERT INTO JsCostGX(CODE, GXTYPE, GXITEM, GXPRI)
VALUES (N'123456', N'gx1', N'02', 6)
INSERT INTO JsCostGX(CODE, GXTYPE, GXITEM, GXPRI)
VALUES (N'123456', N'gx2', N'01', 3)
INSERT INTO JsCostGX(CODE, GXTYPE, GXITEM, GXPRI)
VALUES (N'123456', N'gx2', N'02', 4)SELECT CODE,GXTYPE,ISNULL(GXITEM,'') AS GXITEM,SUM(GXPRI) AS GXPRI
FROM JsCostGX
GROUP BY CODE,GXTYPE,GXITEM WITH ROLLUP
HAVING GXTYPE IS NOT NULL OR GXITEM IS NOT NULLDROP TABLE JsCostGX
/*
CODE                 GXTYPE     GXITEM     GXPRI                 
-------------------- ---------- ---------- --------------------- 
123456               gx1        01         3.0000
123456               gx1        02         6.0000
123456               gx1                   9.0000
123456               gx2        01         3.0000
123456               gx2        02         4.0000
123456               gx2                   7.0000
*/

Answer 2

CREATE TABLE [JsCostGX] (
    [COMMU] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL CONSTRAINT [DF_JsCostGX_COMMU] DEFAULT (0),
    [CODE] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
    [GXTYPE] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
    [GXITEM] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
    [GXPRI] [money] NULL CONSTRAINT [DF_JsCostGX_GXPRI] DEFAULT (0),
    CONSTRAINT [PK_JsCostGX] PRIMARY KEY  CLUSTERED 
    (
        [CODE],
        [GXTYPE],
        [GXITEM]
    )  ON [PRIMARY] 
) ON [PRIMARY]
GOINSERT INTO JsCostGX(CODE, GXTYPE, GXITEM, GXPRI)
VALUES (N'123456', N'gx1', N'01', 3)
INSERT INTO JsCostGX(CODE, GXTYPE, GXITEM, GXPRI)
VALUES (N'123456', N'gx1', N'02', 6)
INSERT INTO JsCostGX(CODE, GXTYPE, GXITEM, GXPRI)
VALUES (N'123456', N'gx2', N'01', 3)
INSERT INTO JsCostGX(CODE, GXTYPE, GXITEM, GXPRI)
VALUES (N'123456', N'gx2', N'02', 4)SELECT CODE,GXTYPE,ISNULL(GXITEM,'') AS GXITEM,SUM(GXPRI) AS GXPRI
FROM JsCostGX
GROUP BY CODE,GXTYPE,GXITEM WITH ROLLUP
HAVING GXTYPE IS NOT NULL OR GXITEM IS NOT NULLDROP TABLE JsCostGX

Answer 3

CREATE TABLE [tb] (
    [COMMU] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL CONSTRAINT [DF_tb_COMMU] DEFAULT (0),
    [CODE] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
    [GXTYPE] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
    [GXITEM] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
    [GXPRI] [money] NULL CONSTRAINT [DF_tb_GXPRI] DEFAULT (0),
    CONSTRAINT [PK_tb] PRIMARY KEY  CLUSTERED 
    (
        [CODE],
        [GXTYPE],
        [GXITEM]
    )  ON [PRIMARY] 
) ON [PRIMARY]
GO
INSERT INTO tb(CODE, GXTYPE, GXITEM, GXPRI)
VALUES (N'123456', N'gx1', N'01', 3)
INSERT INTO tb(CODE, GXTYPE, GXITEM, GXPRI)
VALUES (N'123456', N'gx1', N'02', 6)
INSERT INTO tb(CODE, GXTYPE, GXITEM, GXPRI)
VALUES (N'123456', N'gx2', N'01', 3)
INSERT INTO tb(CODE, GXTYPE, GXITEM, GXPRI)
VALUES (N'123456', N'gx2', N'02', 4)select * from 
(
select CODE, GXTYPE, GXITEM,GXPRI from tb
union all
select CODE, GXTYPE, GXITEM='',sum(GXPRI ) from tb group by CODE, GXTYPE
) t
order by CODE, GXTYPE , case when GXITEM = '' then 2 else 1 enddrop table tb/*
CODE                 GXTYPE     GXITEM     GXPRI                 
-------------------- ---------- ---------- --------------------- 
123456               gx1        01         3.0000
123456               gx1        02         6.0000
123456               gx1                   9.0000
123456               gx2        01         3.0000
123456               gx2        02         4.0000
123456               gx2                   7.0000（所影响的行数为 6 行）*/

Answer 4

select CODE,GXTYPE,GXITEM,GXPRI=sum(GXPRI)
from JsCostGX
group by CODE,GXTYPE,GXITEM with rollup
having grouping(GXTYPE)=0
/*
CODE                 GXTYPE     GXITEM     GXPRI
-------------------- ---------- ---------- ---------------------
123456               gx1        01         3.00
123456               gx1        02         6.00
123456               gx1        NULL       9.00
123456               gx2        01         3.00
123456               gx2        02         4.00
123456               gx2        NULL       7.00(6 行受影响)
*/

Answer 5

CREATE TABLE [JsCostGX] (
    [COMMU] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL CONSTRAINT [DF_JsCostGX_COMMU] DEFAULT (0),
    [CODE] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
    [GXTYPE] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
    [GXITEM] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
    [GXPRI] [money] NULL CONSTRAINT [DF_JsCostGX_GXPRI] DEFAULT (0),
    CONSTRAINT [PK_JsCostGX] PRIMARY KEY  CLUSTERED 
    (
        [CODE],
        [GXTYPE],
        [GXITEM]
    )  ON [PRIMARY] 
) ON [PRIMARY]
GOINSERT INTO JsCostGX(CODE, GXTYPE, GXITEM, GXPRI)
VALUES (N'123456', N'gx1', N'01', 3)
INSERT INTO JsCostGX(CODE, GXTYPE, GXITEM, GXPRI)
VALUES (N'123456', N'gx1', N'02', 6)
INSERT INTO JsCostGX(CODE, GXTYPE, GXITEM, GXPRI)
VALUES (N'123456', N'gx2', N'01', 3)
INSERT INTO JsCostGX(CODE, GXTYPE, GXITEM, GXPRI)
VALUES (N'123456', N'gx2', N'02', 4)select * from jscostgx
union all
select 0 as commu,code,gxtype,'' as gxitem , sum(GXPRI) as GXPRI from jscostgx
group by code,gxtype
order by code,gxtype,gxpri
drop table jscostgx
/*COMMU       CODE                 GXTYPE     GXITEM     GXPRI
----------- -------------------- ---------- ---------- ---------------------
0           123456               gx1        01         3.00
0           123456               gx1        02         6.00
0           123456               gx1                   9.00
0           123456               gx2        01         3.00
0           123456               gx2        02         4.00
0           123456               gx2                   7.00
*/

Answer 6

INSERT INTO JsCostGX(CODE, GXTYPE, GXITEM, GXPRI)
VALUES (N'123456', N'1', N'0661', 3)
INSERT INTO JsCostGX(CODE, GXTYPE, GXITEM, GXPRI)
VALUES (N'123456', N'2', N'6', 6)
INSERT INTO JsCostGX(CODE, GXTYPE, GXITEM, GXPRI)
VALUES (N'123456', N'3', N'4', 3)
INSERT INTO JsCostGX(CODE, GXTYPE, GXITEM, GXPRI)
VALUES (N'123456', N'4', N'444', 4)如果数据变成这样
能不能得到-------------------------------------------
0 123456 1 0661 3.0000
0 123456 2 6 6.0000
0 123456 3 4 3.0000
0 123456 4 444 4.0000
合计                               16.0000 

Answer 7

INSERT INTO JsCostGX(CODE, GXTYPE, GXITEM, GXPRI)
VALUES (N'123456', N'1', N'AA', 3)
INSERT INTO JsCostGX(CODE, GXTYPE, GXITEM, GXPRI)
VALUES (N'123456', N'2', N'SS', 6)
INSERT INTO JsCostGX(CODE, GXTYPE, GXITEM, GXPRI)
VALUES (N'123456', N'3', N'DD', 3)
INSERT INTO JsCostGX(CODE, GXTYPE, GXITEM, GXPRI)
VALUES (N'123456', N'4', N'FF', 4)code gxtype gxitem gxpri 
-------------------------- 
123456  1  AA  3 
123456  2  SS  6 
123456  3  DD  3 
123456  4  FF  4 能不能达到下列效果 123456  1  AA  3 
123456  2  SS  6 
123456  3  DD  3 
123456  4  FF  4 
123456         16