在MySql中使用UNION的SUM,COUNT

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了在MySql中使用UNION的SUM,COUNT，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含1594字，纯文字阅读大概需要3分钟。

内容图文

我有一个具有以下架构的表：

DATA | CAUSE_1 | TIME_1 | CAUSE_2 | TIME_2 | CAUSE_3 | TIME_3

CAUSE.*字段(VarChar)不能包含任何字符串,如果是,则字段TIME.*为0.

我正在尝试创建一个查询,但遗憾的是没有成功,我将以这种形式显示结果：

CAUSE | TOT_TIME | N_RIPET_CAUSE,

哪里：

>在CAUSE中,我有一个CAUSE_1 … CAUSE_3中包含的内容列表,
>在TOT_TIME中,TIME_1 … TIME_3中的值之和,
>在N_RIPET_CAUSE中,每个原因的重复次数.

我希望我解释一下.

解决方法:

试试这个

 SELECT DATA ,CAUSE , TOT_TIME , N_RIPET_CAUSE
 FROM ( select DATA, CONCAT(`CAUSE_1`,' ',`CAUSE_2`, ' ', `CAUSE_3`) as CAUSE ,
 sum(`TIME_1` + `TIME_2` +`TIME_3`) as TOT_TIME ,
 (count(`CAUSE_1`)+count(`CAUSE_2`)+count(`CAUSE_3`)) as N_RIPET_CAUSE
 FROM your_table
 group by DATA
 ) t

SEE SQLFIDDLE DEMO

编辑.

试试这个

     ( select DATA , `CAUSE_1` as CAUSE ,
     sum(`TIME_1` + `TIME_2` +`TIME_3`) as TOT_TIME ,
     (count(`CAUSE_1`)+count(`CAUSE_2`)+count(`CAUSE_3`)) as N_RIPET_CAUSE
     FROM Table1 
    group by DATA)
  union all
    (select DATA , `CAUSE_2` as CAUSE ,
    sum(`TIME_1` + `TIME_2` +`TIME_3`) as TOT_TIME ,
    (count(`CAUSE_1`)+count(`CAUSE_2`)+count(`CAUSE_3`)) as N_RIPET_CAUSE
    FROM Table1
    group by DATA   )
  union all

    (select DATA , `CAUSE_3` as CAUSE ,
    sum(`TIME_1` + `TIME_2` +`TIME_3`) as TOT_TIME ,
    (count(`CAUSE_1`)+count(`CAUSE_2`)+count(`CAUSE_3`)) as N_RIPET_CAUSE
    FROM Table1
    group by DATA   )

SQL DEMO HERE

编辑：

根据您的需要尝试这个

 select cause, sum(time) Tot_time, count(cause) N_Ripet_Cause
 from(
    select  cause_1 as cause, time_1 as time
    from Table1
    union all
    select  cause_2 as cause, time_2 as time
   from Table1
   union all
   select  cause_3 as cause, time_3 as time
   from Table1
 ) t
 group by cause

DEMO SQL FIDDLE

内容总结

以上是互联网集市为您收集整理的在MySql中使用UNION的SUM,COUNT全部内容，希望文章能够帮你解决在MySql中使用UNION的SUM,COUNT所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端