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 的妙用，可以解决当主键条件取第一条时只显示一条返回记录的问题。