請問大們, 我要作兩個欄位的排序, 要如何作

edited 十月 2013 in 資料庫
就是要將同一資料表中的兩個欄位合併進行排序,

不是一般

SELECT * FROM A ORDER BY a DESC, b DESC;

的寫法,

這樣是先排a, 再排b,

而我要是同時排a 和 b

請問要如何寫才正確

謝謝

原始討論: http://twpug.net/x/modules/newbb/viewtopic.php?topic_id=3605

評論

  • edited 九月 2008
    你不妨把問題說清楚些
  • edited 九月 2008
    不好意思,
    寫的太簡略,
    假設有表A,
    內部有許多欄位,
    其中a,b兩欄是日期,
    a,b 兩欄時間互有領先, 並無規則,
    我希望能同時考量a,b兩欄的資料,
    就是將所有表A資料依照a,b欄時間排序,
    而不是先排序a, 再排序b,
    原來ORDER中的語法要先排序a, 再排序b,
    無法同時考量a,b兩欄,
    不知道要如何進行
  • edited 九月 2008
    在 MySQL 可以這樣寫
    SELECT IF(`created` > `modified`, `created`, `modified`) AS bigDate FROM `files` ORDER BY bigDate DESC
    
  • edited 九月 2008
    用大大的方式會產生Error

    You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF(`inputdate` > `last_reply `, `inputdate`, `last_reply`) AS b

    我的程式如下

    "SELECT * IF(`inputdate` > `last_reply `, `inputdate`, `last_reply`) AS bigDate FROM discuss_subject ORDER BY bigDate DESC";


    其實我是要先比較 inputdate, last_reply兩欄,

    再用AS去形成一個虛擬欄位bigDate,

    最後再用bigDate作排序

    不知道哪裡出問題,

    請大大再幫我一下
  • edited 九月 2008
    做法沒錯啊,只是 Select 跟 IF 中間多了星號…
  • edited 九月 2008
    這次語法沒有問題, 但是卻抓不出任何資料

    之前的程式是
    "SELECT * FROM discuss_subject ORDER BY inputdate DESC"

    執行沒有問題,

    改成

    "SELECT IF(`inputdate` > `last_reply `, `inputdate`, `last_reply`) AS bigDate FROM discuss_subject ORDER BY bigDate DESC"

    完全抓不到資料

    可以請大大再幫幫我好嗎?

    不知道哪裡出現問題?
  • edited 九月 2008
    實測過是ok的,會不會是因為你前方欄位名加了 ` 這個符號,但是後方的資料表名,與order的欄位名都沒加??
    你看要全加,或是全不加 測測看。
  • edited 九月 2008
    都測試過, 加不加都一樣抓不到資料,
    是不是日期不能直接比,
    或者其它的地方有誤,
    整個筆數有抓到,
    但是無法個別呈現出來
  • edited 九月 2008
    日期型態還是可以比較的,你確定你兩個欄位的型態是相符的嗎?
  • edited 九月 2008
    兩欄資料型態是一致的,

    而且也能夠抓到總筆數,

    是不是大大的做法只能抓出這兩欄日期資料,

    因此才無法顯現所有欄位資料
  • edited 九月 2008
    我…不是大大
    另、抓全部欄位當然可以,所以判斷不出你的問題點
    方便的話,可以把sql資料dump出來,還有你的sql指令,存成txt檔,上傳來測測嗎?
  • edited 九月 2008
    大大完整程式如下,
    因為無工具可以標示,
    煩請再幫我一下

    <?php
    function buildNavigation($pageNum_Recordset1,$totalPages_Recordset1,$prev_Recordset1,$next_Recordset1,$separator=" | ",$max_links=10, $show_page=true)
    {
    GLOBAL $maxRows_discuss_index,$totalRows_discuss_index;
    $pagesArray = ""; $firstArray = ""; $lastArray = "";
    if($max_links<2)$max_links=2;
    if($pageNum_Recordset1<=$totalPages_Recordset1 && $pageNum_Recordset1>=0)
    {
    if ($pageNum_Recordset1 > ceil($max_links/2))
    {
    $fgp = $pageNum_Recordset1 - ceil($max_links/2) > 0 ? $pageNum_Recordset1 - ceil($max_links/2) : 1;
    $egp = $pageNum_Recordset1 + ceil($max_links/2);
    if ($egp >= $totalPages_Recordset1)
    {
    $egp = $totalPages_Recordset1+1;
    $fgp = $totalPages_Recordset1 - ($max_links-1) > 0 ? $totalPages_Recordset1 - ($max_links-1) : 1;
    }
    }
    else {
    $fgp = 0;
    $egp = $totalPages_Recordset1 >= $max_links ? $max_links : $totalPages_Recordset1+1;
    }
    if($totalPages_Recordset1 >= 1) {
    #
    # Searching for $_GET vars
    #
    $_get_vars = '';
    if(!empty($_GET) || !empty($HTTP_GET_VARS)){
    $_GET = empty($_GET) ? $HTTP_GET_VARS : $_GET;
    foreach ($_GET as $_get_name => $_get_value) {
    if ($_get_name != "pageNum_discuss_index") {
    $_get_vars .= "&$_get_name=$_get_value";
    }
    }
    }
    $successivo = $pageNum_Recordset1+1;
    $precedente = $pageNum_Recordset1-1;
    $firstArray = ($pageNum_Recordset1 > 0) ? "<a href=\"$_SERVER[PHP_SELF]?pageNum_discuss_index=$precedente$_get_vars\">$prev_Recordset1</a>" : "$prev_Recordset1";
    #
    # page numbers
    #
    for($a = $fgp+1; $a <= $egp; $a++){
    $theNext = $a-1;
    if($show_page)
    {
    $textLink = $a;
    } else {
    $min_l = (($a-1)*$maxRows_discuss_index) + 1;
    $max_l = ($a*$maxRows_discuss_index >= $totalRows_discuss_index) ? $totalRows_discuss_index : ($a*$maxRows_discuss_index);
    $textLink = "$min_l - $max_l";
    }
    $_ss_k = floor($theNext/26);
    if ($theNext != $pageNum_Recordset1)
    {
    $pagesArray .= "<a href=\"$_SERVER[PHP_SELF]?pageNum_discuss_index=$theNext$_get_vars\">";
    $pagesArray .= "$textLink</a>" . ($theNext < $egp-1 ? $separator : "");
    } else {
    $pagesArray .= "$textLink" . ($theNext < $egp-1 ? $separator : "");
    }
    }
    $theNext = $pageNum_Recordset1+1;
    $offset_end = $totalPages_Recordset1;
    $lastArray = ($pageNum_Recordset1 < $totalPages_Recordset1) ? "<a href=\"$_SERVER[PHP_SELF]?pageNum_discuss_index=$successivo$_get_vars\">$next_Recordset1</a>" : "$next_Recordset1";
    }
    }
    return array($firstArray,$pagesArray,$lastArray);
    }

    require_once('../Connections/workerwall.php');


    $maxRows_discuss_index = 20;
    $pageNum_discuss_index = 0;
    if (isset($_GET)) {
    $pageNum_discuss_index = $_GET;
    }
    $startRow_discuss_index = $pageNum_discuss_index * $maxRows_discuss_index;

    mysql_select_db($database_workerwall, $workerwall);

    $query_discuss_index = "SELECT IF(`inputdate` > `last_reply `, `inputdate`, `last_reply`) AS bigDate FROM discuss_subject ORDER BY bigDate DESC";


    $query_limit_discuss_index = sprintf("%s LIMIT %d, %d", $query_discuss_index, $startRow_discuss_index, $maxRows_discuss_index);
    $discuss_index = mysql_query($query_limit_discuss_index, $workerwall) or die(mysql_error());
    $row_discuss_index = mysql_fetch_assoc($discuss_index);
    $row_duscuss_index_num = mysql_query($query_discuss_index);
    $row_discuss_num = mysql_num_rows($row_duscuss_index_num);


    if (isset($_GET)) {
    $totalRows_discuss_index = $_GET;
    } else {
    $all_discuss_index = mysql_query($query_discuss_index);
    $totalRows_discuss_index = mysql_num_rows($all_discuss_index);
    }
    $totalPages_discuss_index = ceil($totalRows_discuss_index/$maxRows_discuss_index)-1;
    ?>

    <div>
    <h1 align="center" class="style34">討 論 區 目 錄</h1>

    <table width="670" border="0" align="center" cellpadding="1" cellspacing="2" bordercolor="#333333">
    <tr valign="middle" height="30">
    <td width="30%" valign="middle" align="left">
    <form id="form1" method="post" action="discuss_search_index.php">
    <span class="style37 style36" valign="middle">討論區關鍵字查詢</span><br>
    <label><input name="key_word" type="text" size="16" maxlength="16" /></label>
    <label><input type="submit" name="Submit3" value="查詢" /></label>
    </form>
    </td>
    <td width="20%" align="center">
    <div class="style37"><a href="../discuss/discuss_post.php"><img src='../images/discuss_post.jpg'></a></div> </td>
    <td width="20%" align="center">
    <div class="style37"><a href="javascript:history.go(-1)"><img src='../images/backward.jpg'></a></div> </td>
    <td width="30%" align="right">
    <div><span class="style36">目前共有</span><span class="style29"><? echo ($row_discuss_num); ?></span><span class="style36">篇討論</span></div> </td>
    </tr>
    <tr height="30">
    <td colspan="4">
    <div align="center">
    <?php
    # variable declaration
    $prev_discuss_index = "上一頁 ";
    $next_discuss_index = " 下一頁";
    $separator = " | ";
    $max_links = 20;
    $pages_navigation_discuss_index = buildNavigation($pageNum_discuss_index,$totalPages_discuss_index,$prev_discuss_index,$next_discuss_index,$separator,$max_links,true);

    print $pages_navigation_discuss_index[0];
    print $pages_navigation_discuss_index[1];
    print $pages_navigation_discuss_index[2];
    ?>
    </div>
    </td>
    </tr>
    </table>

    <table width="670" border="2" align="center" cellpadding="1" cellspacing="2" bordercolor="#333333">
    <tr bgcolor="#0066CC">
    <td width="44%" align="center" bordercolor="#333333" bgcolor="#FFFF99"><div align="center" class="style29">討 論 主 題</div></td>
    <td width="15%" align="center" bordercolor="#333333" bgcolor="#FFFF99"><div align="center" class="style29">首次發表</div></td>
    <td width="12%" align="center" bordercolor="#333333" bgcolor="#FFFF99"><div align="center" class="style29">發表人</div></td>
    <td width="7%" align="center" bordercolor="#333333" bgcolor="#FFFF99"><div align="center" class="style29">人氣</div></td>
    <td width="7%" align="center" bordercolor="#333333" bgcolor="#FFFF99"><div align="center" class="style29">回應</div></td>
    <td width="15%" align="center" bordercolor="#333333" bgcolor="#FFFF99"><div align="center" class="style29">最後回應</div></td>
    </tr>
    <?php
    $a =1;
    $sum = 0;
    do {
    $sum = $sum + $a;
    $a = $a++;


    if ($sum%2 == 0)
    {
    echo ("<tr height='35'>");
    }
    else
    {
    echo ("<tr height='35' bgcolor='#DDDDDD'>");
    }
    ?>


    <td width="44%" align="left"><a href='discuss_show.php?discuss_no=<? echo ($row_discuss_index);?>'><?php

    if (strlen ($row_discuss_index) <= 39 )
    {
    echo ($row_discuss_index .'...');
    }
    else
    {
    echo (substr($row_discuss_index,0,39));
    }
    ?></a></td>
    <td width="15%" align="center"><?php echo (substr($row_discuss_index,0,10)); ?></td>
    <td width="12%" align="center"><?php echo $row_discuss_index; ?></td>
    <td width="7%" align="center"><?php echo $row_discuss_index; ?></td>
    <td width="7%" align="center" ><?php echo $row_discuss_index; ?></td>
    <td width="15%" align="center"><?php echo (substr($row_discuss_index,0,10)); ?></td>
    <?php } while ($row_discuss_index = mysql_fetch_assoc($discuss_index)); ?></tr>
    </table>
    <?php mysql_free_result($discuss_index);?>
    </div>
  • edited 九月 2008
    我只針對你的sql部份去看…至於php程式的部份,判其他高手幫你看囉…

    $query_discuss_index = "SELECT IF(`inputdate` > `last_reply `, `inputdate`, `last_reply`) AS bigDate FROM discuss_subject ORDER BY bigDate DESC";

    你用`號標示欄位名,但是在'last_reply跟後方的`符號間有一個空白字元,這也會造成這個sql字串的錯誤。

    你有試過這樣嗎?
    $query_discuss_index = "SELECT IF(inputdate>last_reply,inputdate,last_reply)AS bigDate FROM discuss_subject ORDER BY bigDate DESC";

    你有在mySql query工具裡試run過這串sql語法嗎?
  • edited 九月 2008
    大大, 已經試過,
    沒有用, 還是無法順利顯示資料,
    是mysql版本問題嗎?
  • edited 九月 2008
    1.我不確定是否為版本問題,請問你現在用的mysql版本為何?
    2.你在mysql query工具中驗證過這個sql嗎? 例如phpMyAdmin這一類的工具,都有query的輔助功能。先確認是否sql有誤。
  • edited 九月 2008
    如果在mysql query單純執行下列語法

    SELECT IF(inputdate>last_reply,inputdate,last_reply)AS bigDate FROM discuss_subject ORDER BY bigDate DESC

    所顯示出的只有 bigDate 一欄資料 , 依DESC排列,
    所以之前的才會無法順利顯示所有欄位資料,
    大大要如何改, 才能達到顯示所有欄位資料,
    謝謝
  • edited 九月 2008
    這…我有點搞不懂你的意思了…
    聽起來,你是希望用*號,把其他欄位也秀出
    通常*號需排列在欄位的第一個位置,像這樣:
    SELECT *,IF(inputdate>last_reply,inputdate,last_reply)AS bigDate FROM discuss_subject ORDER BY bigDate DESC
    如果不行的話,你就乖乖的下欄位名吧:
    SELECT IF(inputdate>last_reply,inputdate,last_reply)AS bigDate,欄位名一,欄位名二,欄位名三 FROM discuss_subject ORDER BY bigDate DESC
  • edited 九月 2008
    謝謝,
    已經可以Work,
    應該是我表達不清楚,
    浪費大大許多時間,
    再次謝謝
Sign In or Register to comment.