主页 / dba / 问题 / 4024
Accepted
Mokus
Asked: 2011-07-22 14:18:08 +0800 CST

复杂的mysql选择

  • 772

我有下表，

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

我怎样才能得到以下数组？或者我可以选择一个吗？

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
)
)

它是按 bet[1] 排序的；

更新：

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

有时我必须按 bet[2] 或 bet[x] 进行排序；

mysql select

1 个回答

  1. Best Answer

    我试图产生相同的顺序并遇到了一些麻烦，但是您可以从以下两个不同的查询开始：

    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
;

    我还创建了样本数据来测试它，这里是：

    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) ::

    您将不得不从这里开始调整整行的顺序。

    试试看 ！！！

    • 2

相关问题