mysql - 如何在连接查询后将 "FIRST"表的一列移动到结果中的最后一个？

Question

我使用的是zend，但是问题的答案纯粹可以在mysql中给出。

protected function _getView() {
    $gatewayTable = new Emailer_Model_DbTable_Gateway();
    $select = $this->_getDb()->select()
        ->from(
            array(
                'gatewayTable'  =>  $gatewayTable->getTableName()
            ),
            array(
                'id',
                'name',
                'requireAuth',
                'smtphost',
                'smtpPort',
                'sendLimit',
                'inError',
                'lastError',
                'errorCount'
            )
        );
    return $select;
}



$authTable = new Emailer_Model_DbTable_Auth();
    $select = $this->_getView();
    $select
        ->join(
            array(
                'authTable' =>  $authTable->getTableName()
            ),
            'gatewayTable.id = authTable.idEmailerGateway',
            array(
                "authId"    =>  "id",
                'username',
                'fromAddress',
                'fromName',
                "password"  =>  "encryptedPwd",
                'sentCount',
                'lastUsed'
            )
        );

当我对结果 $select 执行提取时，我想要errorCount $res 中最后出现的第一个表的列.

$res = $this->_getDb()->query($select)->fetch();

Answer 1

您的意思是您想要更改结果中列的顺序？

在 SQL 中，您只需按照所需的顺序选择列即可。喜欢

SELECT t1.x, t1.y, t2.a, t2.b, t1.z 
FROM t1
JOIN t2

对于 Zend_Db，有 columns() 方法。

https://framework.zend.com/manual/1.10/en/zend.db.select.html

段落向现有 FROM 或 JOIN 表添加列

There may be cases where you wish to add columns to an existing FROM or JOIN table after those methods have been called. The columns() method allows you to add specific columns at any point before the query is executed. You can supply the columns as either a string or Zend_Db_Expr or as an array of these elements. The second argument to this method can be omitted, implying that the columns are to be added to the FROM table, otherwise an existing correlation name must be used.

使用示例 #11 使用 columns() 方法添加列的示例

// Build this query:
//   SELECT p."product_id", p."product_name"
//   FROM "products" AS p

$select = $db->select()
             ->from(array('p' => 'products'), 'product_id')
             ->columns('product_name');

// Build the same query, specifying correlation names:
//   SELECT p."product_id", p."product_name"
//   FROM "products" AS p

$select = $db->select()
             ->from(array('p' => 'products'), 'p.product_id')
             ->columns('product_name', 'p');
             // Alternatively use columns('p.product_name')