主页 / 问题 / 645138
Accepted
Пупкин_Василий
Asked:2020-03-28 17:17:24 +0000 UTC2020-03-28 17:17:24 +0000 UTC

MYSQL 一张表输出两条select

  • 772

请帮助，我无法将两个请求合并为一个。他们每个人都显示一行，其中包含有关总数的必要数据。在两行的一个标签中是可取的。联合不工作：

首先：

select s_n_name, count(duration) as 'Количество', SEC_TO_TIME(sum(TIME_TO_SEC(duration))) as 'Время'
from table where s_n='001111';

第二：

select s_n_name, count(duration) as 'Количество', SEC_TO_TIME(sum(TIME_TO_SEC(duration))) as 'Время'
from table where s_n='002222';
mysql

2 个回答

  1. Best Answer

    您可以使用 IN WHERE 子句按键对结果进行分组：

    select s_n_name, 
    count(duration) as cnt,
    SEC_TO_TIME(sum(TIME_TO_SEC(duration))) as `time`
from table where s_n IN ('001111', '002222')
group by s_n, s_n_name;

    或者，如果严格需要 2 个结果： 

    select s_n_name, 
    count(duration) as cnt_dur,
    SEC_TO_TIME(sum(TIME_TO_SEC(duration))) as secs
from table where s_n='001111' OR s_n='002222'
group by s_n, s_n_name;

    需要分组来覆盖聚合函数的常量分组COUNT——不自定义分组，查询的结果永远是一行

    如果您需要将两个具有相同签名的抽象查询（更复杂的条件）的结果组合起来，请使用union all： 

    select s_n_name, 
    count(duration) as cnt,
    SEC_TO_TIME(sum(TIME_TO_SEC(duration))) as secs
from table where s_n='001111'
UNION ALL
select s_n_name, 
    count(duration) as cnt,
    SEC_TO_TIME(sum(TIME_TO_SEC(duration))) as secs
from table where s_n='002222';
    • 1

  2. 分组将为您节省：

    SELECT s_n_name, COUNT(*) n, SEC_TO_TIME(SUM(TIME_TO_SEC(duration))) as t
FROM _table
WHERE s_n IN('001111', '002222')
GROUP BY s_n;

    为了调试，我使用了以下临时表：

    CREATE TEMPORARY TABLE _table(s_n char(6), s_n_name char(14), duration TIME)
  SELECT '001111' s_n, 'name is 001111' s_n_name, '00:11:11' duration UNION ALL
  SELECT '001111' s_n, 'name is 001111' s_n_name, '00:11:11' duration UNION ALL
  SELECT '001111' s_n, 'name is 001111' s_n_name, '00:11:11' duration UNION ALL

  SELECT '002222' s_n, 'name is 002222' s_n_name, '00:22:22' duration UNION ALL
  SELECT '002222' s_n, 'name is 002222' s_n_name, '00:22:22' duration UNION ALL
  SELECT '002222' s_n, 'name is 002222' s_n_name, '00:22:22' duration UNION ALL
  SELECT '002222' s_n, 'name is 002222' s_n_name, '00:22:22' duration UNION ALL

  SELECT '003333' s_n, 'name is 003333' s_n_name, '00:33:33' duration UNION ALL
  SELECT '003333' s_n, 'name is 003333' s_n_name, '00:33:33' duration;

    你可以使用联合（我不明白什么对你不起作用），但这是地狱！

    SELECT s_n_name, COUNT(*) n, SEC_TO_TIME(SUM(TIME_TO_SEC(duration))) as t
FROM _table
WHERE s_n = '001111'

UNION

SELECT s_n_name, COUNT(*) n, SEC_TO_TIME(SUM(TIME_TO_SEC(duration))) as t
FROM _table
WHERE s_n = '002222';
    • 1

相关问题