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