2 ming key Ming_key 于 2016.03.31 19:10 提问

SQL语句统计问题,不想写union all

建表测试语句:

CREATE TABLE stbuu_cause109_20160331 (
intenbid BIGINT,
intrsrp00 BIGINT,
intrsrp01 BIGINT,
intrsrp02 BIGINT,
intrsrp03 BIGINT,
intrsrp04 BIGINT,
intrsrp05 BIGINT,
intrsrp06 BIGINT
);
插入测试数据:

INSERT INTO stbuu_cause109_20160331(intenbid,intrsrp00,intrsrp01,intrsrp02,intrsrp03,intrsrp04,intrsrp05,intrsrp06) values(1110,1,0,0,0,0,0,0);
INSERT INTO stbuu_cause109_20160331(intenbid,intrsrp00,intrsrp01,intrsrp02,intrsrp03,intrsrp04,intrsrp05,intrsrp06) values(1110,0,1,0,0,0,0,0);
INSERT INTO stbuu_cause109_20160331(intenbid,intrsrp00,intrsrp01,intrsrp02,intrsrp03,intrsrp04,intrsrp05,intrsrp06) values(1110,0,0,1,0,0,0,0);
INSERT INTO stbuu_cause109_20160331(intenbid,intrsrp00,intrsrp01,intrsrp02,intrsrp03,intrsrp04,intrsrp05,intrsrp06) values(1110,0,0,0,1,0,0,0);
INSERT INTO stbuu_cause109_20160331(intenbid,intrsrp00,intrsrp01,intrsrp02,intrsrp03,intrsrp04,intrsrp05,intrsrp06) values(1110,0,0,0,0,1,0,0);
INSERT INTO stbuu_cause109_20160331(intenbid,intrsrp00,intrsrp01,intrsrp02,intrsrp03,intrsrp04,intrsrp05,intrsrp06) values(1110,0,0,0,0,0,1,0);
INSERT INTO stbuu_cause109_20160331(intenbid,intrsrp00,intrsrp01,intrsrp02,intrsrp03,intrsrp04,intrsrp05,intrsrp06) values(1110,0,0,0,0,0,0,1);

select * from stbuu_cause109_20160331;

图片说明

需要实现的效果

图片说明

结果语句是:

select intenbid,-141 as rsrp,sum(intrsrp00) countt from stbuu_cause109_20160331 group by intenbid UNION all
select intenbid,-140 as rsrp,sum(intrsrp01) countt from stbuu_cause109_20160331 group by intenbid UNION all
select intenbid,-139 as rsrp,sum(intrsrp02) countt from stbuu_cause109_20160331 group by intenbid UNION all
select intenbid,-138 as rsrp,sum(intrsrp03) countt from stbuu_cause109_20160331 group by intenbid UNION all
select intenbid,-137 as rsrp,sum(intrsrp04) countt from stbuu_cause109_20160331 group by intenbid UNION all
select intenbid,-136 as rsrp,sum(intrsrp05) countt from stbuu_cause109_20160331 group by intenbid UNION all
select intenbid,-135 as rsrp,sum(intrsrp06) countt from stbuu_cause109_20160331 group by intenbid ;

目前这种实现方式不靠谱,intrsrp00-99个字段,sql写99个union all 语句会内存溢出,求大神解答,谢谢

2个回答

Ming_key
Ming_key   2016.03.31 19:11

数据库用的Postgresql

CSDNXIAON
CSDNXIAON   2016.03.31 19:22

SQL语句之Union和Union All
sql语句中的union和union all
SQL语句 UNION 和 UNION ALL 使用(一)
----------------------同志你好,我是CSDN问答机器人小N,奉组织之命为你提供参考答案,编程尚未成功,同志仍需努力!

Csdn user default icon
上传中...
上传图片
插入图片
准确详细的回答,更有利于被提问者采纳,从而获得C币。复制、灌水、广告等回答会被删除,是时候展现真正的技术了!