Início / dba / Perguntas / 4024
Accepted
Mokus
Asked: 2011-07-22 14:18:08 +0800 CST

Seleção mysql complexa

  • 772

Eu tenho a seguinte tabela,

id | booki_id | bet_id   |      bet  | bettype | line | odds 
1  |   123    |   321    |        1  |    3way |  0,0 |  2.3
2  |   123    |   321    |        2  |    3way |  0,0 |  3.4
3  |   123    |   322    |        1  |    3way |  0,0 |  1.1
4  |   123    |   322    |        2  |    3way |  0,0 |  7.4
5  |   123    |   323    |        1  |    3way |  0,0 |  1.3
6  |   123    |   323    |        2  |    3way |  0,0 |  9.4

Como posso obter o seguinte array? Ou posso ficar com uma seleção?

array
(
[0]=>array
(
   "booki_id"=>123,
   "betid"=>321,
   "bet"=>array([1]=>2.3
                [2]=>3.4
                ),
    "bettype"=>"3way",
    "line"=>0
),
[1]=>array
(
   "booki_id"=>123,
   "betid"=>323,
   "bet"=>array([1]=>1.3
                [2]=>9.4
                ),
    "bettype"=>"3way",
    "line"=>0
)
[2]=>array
(
   "booki_id"=>123,
   "betid"=>322,
   "bet"=>array([1]=>1.1
                [2]=>7.4
                ),
    "bettype"=>"3way",
    "line"=>0
)
)

E é ordenado por bet[1];

ATUALIZADA:

id | booki_id | bet_id   |      bet  | bettype | line | odds 
1  |   123    |   321    |        1  |    3way |  0,0 |  2.3
2  |   123    |   321    |        2  |    3way |  0,0 |  3.4
3  |   123    |   321    |        x  |    3way |  0,0 |  2.4
4  |   123    |   322    |        1  |    3way |  0,0 |  1.1
5  |   123    |   322    |        2  |    3way |  0,0 |  7.4
6  |   123    |   322    |        x  |    3way |  0,0 |  3.4
7  |   123    |   323    |        1  |    3way |  0,0 |  1.3
8  |   123    |   323    |        2  |    3way |  0,0 |  9.4
9  |   123    |   323    |        x  |    3way |  0,0 |  6.4

E às vezes tenho que pedir por bet[2] ou bet[x];

mysql select

1 respostas

  1. Best Answer

    Tentei produzir o mesmo pedido e tive alguns problemas, mas aqui estão duas consultas diferentes com as quais você pode começar:

    SELECT
    booki_id,bet_id,bettype,
    group_concat(odds order by bet) betodds,line
FROM
    bettable
GROUP BY
    booki_id,bet_id,bettype,line
;

SELECT * FROM
(
    SELECT
        booki_id,bet_id,bettype,
        group_concat(odds order by bet) betodds,line
    FROM
        bettable
    GROUP BY
        booki_id,bet_id,bettype,line
) AA
ORDER BY betodds
;

    Também criei dados de amostra para testá-los e aqui estão:

    Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.5.12 MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

MySQL (Current test) :: use test
Database changed
MySQL (Current test) :: DROP TABLE IF EXISTS bettable;
Query OK, 0 rows affected (0.03 sec)

MySQL (Current test) :: CREATE TABLE bettable
    -> (
    ->     id       INT NOT NULL AUTO_INCREMENT,
    ->     booki_id INT,
    ->     bet_id   INT,
    ->     bet      INT,
    ->     bettype  VARCHAR(10),
    ->     line     FLOAT,
    ->     odds     FLOAT,
    ->     PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.05 sec)

MySQL (Current test) :: INSERT INTO bettable (booki_id,bet_id,bet,bettype,line,odds) VALUES
    -> (123,321,1,'3way',0.0,2.3),
    -> (123,321,2,'3way',0.0,3.4),
    -> (123,322,1,'3way',0.0,1.1),
    -> (123,322,2,'3way',0.0,7.4),
    -> (123,323,1,'3way',0.0,1.3),
    -> (123,323,2,'3way',0.0,9.4);
Query OK, 6 rows affected (0.02 sec)
Records: 6  Duplicates: 0  Warnings: 0

MySQL (Current test) :: SELECT
    ->     booki_id,bet_id,bettype,
    ->     group_concat(odds order by bet) betodds,line
    -> FROM
    ->     bettable
    -> GROUP BY
    ->     booki_id,bet_id,bettype,line
    -> ;
+----------+--------+---------+---------+------+
| booki_id | bet_id | bettype | betodds | line |
+----------+--------+---------+---------+------+
|      123 |    321 | 3way    | 2.3,3.4 |    0 |
|      123 |    322 | 3way    | 1.1,7.4 |    0 |
|      123 |    323 | 3way    | 1.3,9.4 |    0 |
+----------+--------+---------+---------+------+
3 rows in set (0.01 sec)

MySQL (Current test) :: SELECT * FROM
    -> (
    ->     SELECT
    ->         booki_id,bet_id,bettype,
    ->         group_concat(odds order by bet) betodds,line
    ->     FROM
    ->         bettable
    ->     GROUP BY
    ->         booki_id,bet_id,bettype,line
    -> ) AA
    -> ORDER BY betodds
    -> ;
+----------+--------+---------+---------+------+
| booki_id | bet_id | bettype | betodds | line |
+----------+--------+---------+---------+------+
|      123 |    322 | 3way    | 1.1,7.4 |    0 |
|      123 |    323 | 3way    | 1.3,9.4 |    0 |
|      123 |    321 | 3way    | 2.3,3.4 |    0 |
+----------+--------+---------+---------+------+
3 rows in set (0.02 sec)

MySQL (Current test) ::

    Você terá que brincar com a ordem de todas as linhas a partir daqui.

    De uma chance !!!

    • 2

relate perguntas