列不同条件的总和 [英] sum of column diffrent conditon

问题描述

嗨frd''z .............................

i有一张像这样的桌子。 ......................

hi frd''z.............................
i have a table like.......................

c.id	gas	water	wire	dranage	roadl	roadtype
1	100	100	0	0	100	a
1	25	0	51	55	250	b
1	0	0	12	0	150	c
1	60	80	0	70	50	d
2	50	0	20	50	10	a
2	50	70	0	0	50	b
2	0	80	0	20	20	c
2	0	0	10	50	30	d
.	.	.	.	.	.	.
.	.	.	.	.	.	.
n	n	n	n	n	n	n

----------------- -------------------------------------------------- -----------------------------

i wnat结果如........



选择当气体= 0.00然后总和（roadl）为b，当水= 0.00然后sum（roadl）为c，当wire = 0.00然后sum（roadl）为d ，当dranage = 0.00然后总和（roadl）作为来自表格

------------------------------------------------------------------------------------------------
i wnat result like........

select when gas=0.00 then sum(roadl)as b,when water=0.00 then sum(roadl)as c,when wire=0.00 then sum(roadl)as d,when dranage=0.00 then sum(roadl)as a from table

cid	b	c 	d	a	
1	150	400	150	250	
2	50	40	70	50

[edit]已添加代码块 - OriginalGriff [/ edit]

[edit]Code block added - OriginalGriff[/edit]

推荐答案

你需要使用CASE WHEN子句。

试试这个：

You need to use a CASE WHEN clause.
Try this:

SELECT cid,
       SUM(CASE WHEN gas=0 THEN roadl ELSE 0 END) AS b,
       SUM(CASE WHEN water=0 THEN roadl ELSE 0 END) AS c,
       SUM(CASE WHEN wire=0 THEN roadl ELSE 0 END) AS d,
       SUM(CASE WHEN dranage=0 THEN roadl ELSE 0 END) AS a
FROM MyTable GROUP BY cid

这篇关于列不同条件的总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！