Início / dba / Perguntas / 64818
Accepted
user2732180
Asked: 2014-05-09 23:42:45 +0800 CST

Minha consulta falha ao segmentar os resultados conforme pretendido. O que estou fazendo errado?

  • 772

Atualização: reformulando a pergunta.

Abaixo estão os resultados e os resultados desejados. O que preciso fazer para obter os resultados desejados? Consulte http://sqlfiddle.com/#!2/d0e13c/69

obrigada

SELECT CONCAT('Greater than or equal to ',t.val2,':', count(*)) FROM
(
SELECT 
  CASE 
    WHEN val>=1000 THEN 1000
    WHEN val>=500 THEN 500
    WHEN val>=400 THEN 400
    WHEN val>=300 THEN 300
    WHEN val>=200 THEN 200
    WHEN val>=100 THEN 100 
    WHEN val>=10 THEN 10
    WHEN val>=5 THEN 5
    WHEN val>=2 THEN 2
    WHEN val>=1 THEN 1
  END AS 'val2'
FROM e
) as t
GROUP BY t.val2
ORDER BY t.val2 DESC

Resultados:

Greater than or equal to 1000:1
Greater than or equal to 500:1
Greater than or equal to 400:2
Greater than or equal to 300:1
Greater than or equal to 200:2
Greater than or equal to 100:3
Greater than or equal to 10:1
Greater than or equal to 5:1
Greater than or equal to 1:2

Resultados desejados:

Greater than or equal to 1000:1
Greater than or equal to 500:2
Greater than or equal to 400:4
Greater than or equal to 300:5
Greater than or equal to 200:7
Greater than or equal to 100:10
Greater than or equal to 10:11
Greater than or equal to 5:12
Greater than or equal to 2:12
Greater than or equal to 1:14
mysql

2 respostas

  1. Best Answer

    É possível converter as CASEopções em linhas de uma tabela temporária, uma JOINcom condição menor ou igual à tabela de dados obterá as linhas.

    SELECT CONCAT('Greater than or equal to ',a.val,':', count(e.val))
FROM   (Select 1000 val UNION ALL 
        Select 500      UNION ALL
        Select 400      UNION ALL
        Select 300      UNION ALL
        Select 200      UNION ALL
        Select 100      UNION ALL
        Select 10       UNION ALL
        Select 5        UNION ALL
        Select 2        UNION ALL
        Select 1       ) a
       LEFT JOIN e ON e.val >= a.val
GROUP BY a.val
ORDER BY a.val  DESC

    Demonstração do SQLFiddle

    Outra maneira nem precisa da tabela temporária, mas obterá os resultados como colunas em vez de linhas, se você se importar com os resultados e não com o formato

    SELECT CONCAT('Greater than or equal to 1000: '
             , SUM(Case When val >= 1000 Then 1 Else 0 End)) _1000
     , CONCAT('Greater than or equal to  500: '
            , SUM(Case When val >= 500 Then 1 Else 0 End)) _500
     , CONCAT('Greater than or equal to  400: '
            , SUM(Case When val >= 400 Then 1 Else 0 End)) _400
     , CONCAT('Greater than or equal to  300: '
            , SUM(Case When val >= 300 Then 1 Else 0 End)) _300
     , CONCAT('Greater than or equal to  200: '
            , SUM(Case When val >= 200 Then 1 Else 0 End)) _200
     , CONCAT('Greater than or equal to  100: '
            , SUM(Case When val >= 100 Then 1 Else 0 End)) _100
     , CONCAT('Greater than or equal to   10: '
            , SUM(Case When val >= 10 Then 1 Else 0 End)) _10
     , CONCAT('Greater than or equal to    5: '
            , SUM(Case When val >= 5 Then 1 Else 0 End)) _5
     , CONCAT('Greater than or equal to    2: '
            , SUM(Case When val >= 2 Then 1 Else 0 End)) _2
     , CONCAT('Greater than or equal to    1: '
            , SUM(Case When val >= 1 Then 1 Else 0 End)) _1
FROM   e

    Demonstração do SQLFiddle

    • 2

  2. PERGUNTA PROPOSTA Nº 1

    SELECT * FROM
(SELECT 'Count 1000' hdr,SUM(val >= 1000) GroupCount FROM e UNION
SELECT 'Count  500',SUM(val >=  500) GroupCount FROM e UNION
SELECT 'Count  400',SUM(val >=  400) GroupCount FROM e UNION
SELECT 'Count  300',SUM(val >=  300) GroupCount FROM e UNION
SELECT 'Count  200',SUM(val >=  200) GroupCount FROM e UNION
SELECT 'Count  100',SUM(val >=  100) GroupCount FROM e UNION
SELECT 'Count   10',SUM(val >=   10) GroupCount FROM e UNION
SELECT 'Count    5',SUM(val >=    5) GroupCount FROM e UNION
SELECT 'Count    2',SUM(val >=    2) GroupCount FROM e UNION
SELECT 'Count    1',SUM(val >=    1) GroupCount FROM e) t
ORDER BY GroupCount DESC,hdr;

    CARREGUE SEUS DADOS DE AMOSTRA

    mysql> USE test
Database changed
mysql> DROP TABLE IF EXISTS e;
Query OK, 0 rows affected (0.14 sec)

mysql> CREATE TABLE e (val INTEGER NOT NULL);
Query OK, 0 rows affected (0.36 sec)

mysql> INSERT INTO e(val) VALUES
    -> (1),(1),(5),(10),(100),(150),(199),(200),
    -> (250),(301),(401),(402),(500),(1000);
Query OK, 14 rows affected (0.17 sec)
Records: 14  Duplicates: 0  Warnings: 0

mysql>

    CONSULTA PROPOSTA Nº 1 EXECUTADA

    mysql> SELECT * FROM
    -> (SELECT 'Count 1000' hdr,SUM(val >= 1000) GroupCount FROM e UNION
    -> SELECT 'Count  500',SUM(val >=  500) GroupCount FROM e UNION
    -> SELECT 'Count  400',SUM(val >=  400) GroupCount FROM e UNION
    -> SELECT 'Count  300',SUM(val >=  300) GroupCount FROM e UNION
    -> SELECT 'Count  200',SUM(val >=  200) GroupCount FROM e UNION
    -> SELECT 'Count  100',SUM(val >=  100) GroupCount FROM e UNION
    -> SELECT 'Count   10',SUM(val >=   10) GroupCount FROM e UNION
    -> SELECT 'Count    5',SUM(val >=    5) GroupCount FROM e UNION
    -> SELECT 'Count    2',SUM(val >=    2) GroupCount FROM e UNION
    -> SELECT 'Count    1',SUM(val >=    1) GroupCount FROM e) t
    -> ORDER BY GroupCount DESC,hdr;
+------------+------------+
| hdr        | GroupCount |
+------------+------------+
| Count    1 |         14 |
| Count    2 |         12 |
| Count    5 |         12 |
| Count   10 |         11 |
| Count  100 |         10 |
| Count  200 |          7 |
| Count  300 |          5 |
| Count  400 |          4 |
| Count  500 |          2 |
| Count 1000 |          1 |
+------------+------------+
10 rows in set (0.01 sec)

mysql>

    PERGUNTA PROPOSTA Nº 2

    SELECT CONCAT('Greater than or equal to ',valrange,':',GroupCount) DesiredResults FROM
(SELECT * FROM (SELECT 1000 valrange,SUM(val >= 1000) GroupCount FROM e UNION
SELECT 500,SUM(val >=  500) GroupCount FROM e UNION
SELECT 400,SUM(val >=  400) GroupCount FROM e UNION
SELECT 300,SUM(val >=  300) GroupCount FROM e UNION
SELECT 200,SUM(val >=  200) GroupCount FROM e UNION
SELECT 100,SUM(val >=  100) GroupCount FROM e UNION
SELECT 10,SUM(val >=   10) GroupCount FROM e UNION
SELECT 5,SUM(val >=    5) GroupCount FROM e UNION
SELECT 2,SUM(val >=    2) GroupCount FROM e UNION
SELECT 1,SUM(val >=    1) GroupCount FROM e) t) q;

    CONSULTA PROPOSTA Nº 2 EXECUTADA

    mysql> SELECT CONCAT('Greater than or equal to ',valrange,':',GroupCount) DesiredResult FROM
    -> (SELECT * FROM (SELECT 1000 valrange,SUM(val >= 1000) GroupCount FROM e UNION
    -> SELECT 500,SUM(val >=  500) GroupCount FROM e UNION
    -> SELECT 400,SUM(val >=  400) GroupCount FROM e UNION
    -> SELECT 300,SUM(val >=  300) GroupCount FROM e UNION
    -> SELECT 200,SUM(val >=  200) GroupCount FROM e UNION
    -> SELECT 100,SUM(val >=  100) GroupCount FROM e UNION
    -> SELECT 10,SUM(val >=   10) GroupCount FROM e UNION
    -> SELECT 5,SUM(val >=    5) GroupCount FROM e UNION
    -> SELECT 2,SUM(val >=    2) GroupCount FROM e UNION
    -> SELECT 1,SUM(val >=    1) GroupCount FROM e) t) q;
+---------------------------------+
| DesiredResult                   |
+---------------------------------+
| Greater than or equal to 1000:1 |
| Greater than or equal to 500:2  |
| Greater than or equal to 400:4  |
| Greater than or equal to 300:5  |
| Greater than or equal to 200:7  |
| Greater than or equal to 100:10 |
| Greater than or equal to 10:11  |
| Greater than or equal to 5:12   |
| Greater than or equal to 2:12   |
| Greater than or equal to 1:14   |
+---------------------------------+
10 rows in set (0.00 sec)

mysql>

    Observação: a consulta proposta nº 2 tem a saída desejada

    DE UMA CHANCE !!!

    • 1

relate perguntas