一个客服答疑的模块，主贴（qna_ask）、回复帖（qna_replay ）分别是两个表，需要获取每个主贴最新的回复贴。
这里不考虑效率，只是说一条语句的实现问题。
先看MySql：
select * from
(
SELECT
qna_ask.qa_id,
...
qna_replay.qr_time,
...
FROM
qna_ask
INNER JOIN qna_replay ON qna_replay.qa_id = qna_ask.qa_id
ORDER BY qa_id, qr_time desc) as v
group by v.qa_id
Mysql 在Group By存在的情况下，SELECT 的内容，不仅仅是Group By的字段和聚合函数，可以是任何内容，只是有些返回是没有意义的。
SqlServer 在Group
By存在的情况下，SELECT 的内容，只能是Group
By的字段和聚合函数。Sqlserver聚合函数只有那可怜的几个，于是，决定写一个返回第一个字段内容的函数，FirstVal，据说SqlServer
2012会有个这个函数。
select sex,dbo.FirstVal(name) as n, dbo.FirstVal(weight) as w from
(
SELECT top(99.9999999999999) percent * from dbo.[user]
order by sex , weight desc
)v
group by v.sex
需要写一个Sqlserver 的 CLR 自定义聚合函数。很简单的，VS2010 有wizard。
using System;
using System.Data;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
using System.IO;
using System.Text;
[Serializable]
[SqlUserDefinedAggregate(
Format.UserDefined, //use custom serialization to serialize the intermediate result
IsInvariantToNulls = true, //optimizer property
IsInvariantToDuplicates = false, //optimizer property
IsInvariantToOrder = false, //optimizer property
MaxByteSize = 8000) //maximum size in bytes of persisted value
]
public class FirstVal : IBinarySerialize
{
/// <summary>
/// The variable that holds the intermediate result of the concatenation
/// </summary>
private StringBuilder intermediateResult;
private Boolean isAssignment;
/// <summary>
/// Initialize the internal data structures
/// </summary>
public void Init()
{
this.intermediateResult = new StringBuilder();
isAssignment = false;
}
/// <summary>
/// Accumulate the next value, not if the value is null
/// </summary>
/// <param></param>
public void Accumulate(SqlString value)
{
if (!isAssignment)
{
isAssignment = true;
intermediateResult.Append(value.Value);
}
return;
}
/// <summary>
/// Merge the partially computed aggregate with this aggregate.
/// </summary>
/// <param></param>
public void Merge(FirstVal other)
{
this.intermediateResult.Append(other.intermediateResult);
}
/// <summary>
/// Called at the end of aggregation, to return the results of the aggregation.
/// </summary>
/// <returns></returns>
public SqlString Terminate()
{
return new SqlString(intermediateResult.ToString());
}
public void Read(BinaryReader r)
{
intermediateResult = new StringBuilder(r.ReadString());
}
public void Write(BinaryWriter w)
{
w.Write(this.intermediateResult.ToString());
}
}
要调试，修改一下儿Test.sql的内容，比如就是上一段SQL就可以进入调试。
SQL Server 查询子句不支持order by， 必须要加上 SELECT TOP number|percent。
SELECT top(99.9999999999999) percent * from dbo.[user]
order by sex , weight desc
发现问题了，SELECT top(100) percent ，在CLR的 void Accumulate(SqlString value)中接受到的数据是不经过排序的。数字随便写都排序，小于100%也可以，于是诞生了上面SELECT top(99.9999999999999),小数点后面13个，再多，就又不排序了。或者干脆写个大整数比如几千亿也排序。
就到这里吧。。。