很抱歉内容有点长。我有一个包含以下列的表格:
候选人姓名,选区,政党,结果
这些与it技术无关。
CREATE TABLE `parlia` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`CANDIDATE_NAME` varchar(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT '',
`REGION` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT '',
`CONSTITUENCY` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT '',
`PARTY` varchar(10) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT '',
`RESULT` int(11) NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE,
) ENGINE = InnoDB AUTO_INCREMENT = 295 CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Dynamic;
INSERT INTO `parlia`(`id`, `CANDIDATE_NAME`, `REGION`, `CONSTITUENCY`, `PARTY`, `RESULT`) VALUES (31, 'OPPONG SAMUEL', 'ASHANTI', 'AKROFUOM', 'NDC', 7484);
INSERT INTO `parlia`(`id`, `CANDIDATE_NAME`, `REGION`, `CONSTITUENCY`, `PARTY`, `RESULT`) VALUES (32, 'KWABENA APPIAH-AKROFUOM', 'ASHANTI', 'AKROFUOM', 'NPP', 10249);
INSERT INTO `parlia`(`id`, `CANDIDATE_NAME`, `REGION`, `CONSTITUENCY`, `PARTY`, `RESULT`) VALUES (34, 'NATHONY KWAKYE-AMEYAW', 'ASHANTI', 'AKROFUOM', 'PPP', 129);
INSERT INTO `parlia`(`id`, `CANDIDATE_NAME`, `REGION`, `CONSTITUENCY`, `PARTY`, `RESULT`) VALUES (49, 'HON ALHAJI MOHAMMED MUNTAKA MUBARAK', 'ASHANTI', 'ASAWASE', 'NDC', 43917);
INSERT INTO `parlia`(`id`, `CANDIDATE_NAME`, `REGION`, `CONSTITUENCY`, `PARTY`, `RESULT`) VALUES (50, 'NANA OKKERE TAWIAH ANTWI', 'ASHANTI', 'ASAWASE', 'NPP', 31013);
INSERT INTO `parlia`(`id`, `CANDIDATE_NAME`, `REGION`, `CONSTITUENCY`, `PARTY`, `RESULT`) VALUES (51, 'JERRY JOSEPH QUAYSON', 'ASHANTI', 'ASAWASE', 'PPP', 458);
INSERT INTO `parlia`(`id`, `CANDIDATE_NAME`, `REGION`, `CONSTITUENCY`, `PARTY`, `RESULT`) VALUES (90, 'DACOSTA KOFI YEBOAH', 'ASHANTI', 'BEKWAI', 'NDP', 92);
INSERT INTO `parlia`(`id`, `CANDIDATE_NAME`, `REGION`, `CONSTITUENCY`, `PARTY`, `RESULT`) VALUES (91, 'AKWASI GYAMFI', 'ASHANTI', 'BEKWAI', 'INDP', 656);
INSERT INTO `parlia`(`id`, `CANDIDATE_NAME`, `REGION`, `CONSTITUENCY`, `PARTY`, `RESULT`) VALUES (92, 'DR.JOE OTENG ADJEI', 'ASHANTI', 'BOSOMTWE', 'NDC', 17235);
INSERT INTO `parlia`(`id`, `CANDIDATE_NAME`, `REGION`, `CONSTITUENCY`, `PARTY`, `RESULT`) VALUES (93, 'HON. SIMON OSEI MENSAH', 'ASHANTI', 'BOSOMTWE', 'NPP', 34790);
INSERT INTO `parlia`(`id`, `CANDIDATE_NAME`, `REGION`, `CONSTITUENCY`, `PARTY`, `RESULT`) VALUES (95, 'YAW WIREDU', 'ASHANTI', 'BOSOMTWE', 'PNC', 58);
INSERT INTO `parlia`(`id`, `CANDIDATE_NAME`, `REGION`, `CONSTITUENCY`, `PARTY`, `RESULT`) VALUES (96, 'HON KWAME ADARKWAH', 'ASHANTI', 'BOSOME FREHO', 'NDC', 5467);
INSERT INTO `parlia`(`id`, `CANDIDATE_NAME`, `REGION`, `CONSTITUENCY`, `PARTY`, `RESULT`) VALUES (97, 'KWADWO KYEI FRIMPONG', 'ASHANTI', 'BOSOME FREHO', 'NPP', 14293);
INSERT INTO `parlia`(`id`, `CANDIDATE_NAME`, `REGION`, `CONSTITUENCY`, `PARTY`, `RESULT`) VALUES (98, 'OWUSU AUGUSTINE', 'ASHANTI', 'BOSOME FREHO', 'CPP', 143);
我想在另一列名为SHARE的地方计算每个候选人在各个选区结果的总和基础上的百分比份额。以下是没有SHARE列的查询。请参见链接中的图像。
SELECT CANDIDATE_NAME, CONSTITUENCY, PARTY, RESULT
FROM parlia
WHERE REGION = 'ASHANTI'
我使用ROUND函数完成了这个操作,但是我得到的百分比份额是使用RESULTS列中所有值的总和进行计算的。我的目标是根据每个选区的SUM(RESULTS)获取每个政党的份额。
SELECT CANDIDATE_NAME, CONSTITUENCY, PARTY, RESULT,
ROUND((RESULT/(SELECT SUM(RESULT) FROM parlia)) * 100,2) AS `SHARE`
FROM parlia
WHERE REGION = 'ASHANTI'
ORDER BY SHARE_PER_CONSTITUENCY DESC
添加到上面的查询中应该可以解决问题。 - Tim Biegeleisen