GROUP BY

-暖暖- 发布于 2014/10/30 11:16
阅读 59
收藏 0
 /* Formatted on 05-2月-2009 (QP5 v5.228) */
CREATE TABLE group_test
(
    id        NUMBER,
    name      VARCHAR2 (10),
    VALUE     NUMBER,
    remarks   VARCHAR2 (255)
);
/

COMMENT ON TABLE group_test IS
    '为了测试CUBE,ROLLUP,GROUPING,GROUPING_ID的区别';
COMMENT ON COLUMN group_test.id IS '序号';
COMMENT ON COLUMN group_test.name IS '名字';
COMMENT ON COLUMN group_test.VALUE IS '消费金额';
COMMENT ON COLUMN group_test.remarks IS '备注';
/

CREATE SEQUENCE seq_group_test
    INCREMENT BY 1
    START WITH 1
    MAXVALUE 999999999;
/

INSERT INTO group_test
     VALUES (seq_group_test.NEXTVAL,
             '&NAME',
             &value,
             '&REMARKS');
/

SELECT * FROM group_test;

ID    NAME   VALUE   remarks

INSERT INTO group_test
     VALUES (1,
             'tom',
             8,
             ' 生活用品')
INSERT INTO GROUP_TEST VALUES(2,     'marry',   9,   '生活用品')
/

INSERT INTO group_test
     VALUES (3,
             'lee',
             8,
             '生活用品')
/

INSERT INTO group_test
     VALUES (4,
             'tom',
             9,
             '学习用品')
/

INSERT INTO group_test
     VALUES (5,
             'marry',
             10,
             ' 学习用品')
/

INSERT INTO group_test
     VALUES (6,
             'lee',
             8,
             '学习用品')
/

INSERT INTO group_test
     VALUES (7,
             'tom',
             15,
             '生活用品')
/

INSERT INTO group_test
     VALUES (8,
             'marry',
             16,
             '生活用品')
/

INSERT INTO group_test
     VALUES (9,
             'lee',
             16,
             '生活用品')
/

INSERT INTO group_test
     VALUES (10,
             'lee',
             15,
             '学习用品')
/

INSERT INTO group_test
     VALUES (11,
             'marry',
             15,
             '学习用品')
/



  SELECT name, remarks, SUM (VALUE)
    FROM group_test
GROUP BY name, remarks;
/
加载中
返回顶部
顶部