Sql_從查詢的結果集中分組後取最後有效的資料成新的結果集小記(待優化)

Dim sql As String = " SELECT xp.*, "
            sql = sql + "        xf_owner.ownername, "
            sql = sql + "        xf_receiver.receivename, "
            sql = sql + "        lastprice = "
            sql = sql + "        ( "
            sql = sql + "            SELECT TOP 1 "
            sql = sql + "                   lp.tonprice "
            sql = sql + "            FROM "
            sql = sql + "            ( "
            sql = sql + "                SELECT * "
            sql = sql + "                FROM xf_price "
            sql = sql + "                WHERE pk_price IN ( "
            sql = sql + "                                      SELECT TOP 2 "
            sql = sql + "                                             pk_price "
            sql = sql + "                                      FROM xf_price "
            sql = sql + "                                      WHERE pk_pricegroup = xp.pk_pricegroup "
            sql = sql + "                                            AND pk_owner = xp.pk_owner "
            sql = sql + "                                            AND pk_receiver = xp.pk_receiver "
            sql = sql + "                                      ORDER BY enabletime DESC "
            sql = sql + "                                  ) "
            sql = sql + "            ) lp "
            sql = sql + "            ORDER BY lp.enabletime ASC "
            sql = sql + "        ), "
            sql = sql + "        transname = CASE "
            sql = sql + "                        WHEN transtype = 0 THEN "
            sql = sql + "                            `外部運輸` "
            sql = sql + "                        WHEN transtype = 1 THEN "
            sql = sql + "                            `廠內轉運` "
            sql = sql + "                        ELSE "
            sql = sql + "                            `廠外轉運` "
            sql = sql + "                    END "
            sql = sql + " FROM xf_price xp "
            sql = sql + "     LEFT JOIN xf_owner "
            sql = sql + "         ON xf_owner.pk_owner = xp.pk_owner "
            sql = sql + "     LEFT JOIN xf_receiver "
            sql = sql + "         ON xf_receiver.pk_receiver = xp.pk_receiver "
            sql = sql + " WHERE xp.dr = 0 "
            sql = sql + "       AND xp.pk_pricegroup = @pk_pricegroup "
            sql = sql + "       AND pk_price IN ( "
            sql = sql + "                           SELECT TOP 1 "
            sql = sql + "                                  pk_price "
            sql = sql + "                           FROM xf_price "
            sql = sql + "                           WHERE pk_pricegroup = xp.pk_pricegroup "
            sql = sql + "                                 AND pk_owner = xp.pk_owner "
            sql = sql + "                                 AND pk_receiver = xp.pk_receiver "
            sql = sql + "                           ORDER BY enabletime DESC "
            sql = sql + "                       ) "
            sql = sql + " ORDER BY xp.enabletime DESC; "
            Dim com As New SqlClient.SqlCommand(sql, strCon)
            com.Parameters.Add(New SqlClient.SqlParameter("@pk_pricegroup", pk_pricegroup))
            Dim ds As New DataSet
            Dim adapter As New SqlClient.SqlDataAdapter(com)
            strCon.Open()
            adapter.Fill(ds, "xf_price")
            strCon.Close()

主要思路：先取整體結果集，然後條件取按時間倒序排第一條記錄的主鍵，然後有個欄位需要取上一次修改的結果值（別名lastprice，即上次修改的值tonprice），則採用先TOP 2 取兩條資料，然後按時間倒序排，再TOP 1取第一條便是上一次修改的結果值。
注意xf_price xp 的妙用，可以解決當主鍵條件取第一條時只顯示一條返回記錄的問題。