SQL Server常用语法及常见问题
1、row_number() over(partition by .. order by .. desc)示例
select t.* from
(select tradecode,thedate,commissionRate,saleuser_eid,commisstionRatio,createlogid,ctime
,row_number() over(partition by tradecode,saleuser_eid order by thedate desc) row
from CommissionRule)t
select t.* from
(select tradecode,thedate,commissionRate,saleuser_eid,commisstionRatio,createlogid,ctime
,row_number() over(partition by tradecode,saleuser_eid order by thedate desc) row
from CommissionRule)t where row=1
结果如下示:
2、SQLServer 2005 附加数据库
GO
CREATE DATABASE [ERP_JXC_ZH] ON
( FILENAME = N'C:\dddd\ERP_JXC.mdf' ),
( FILENAME = N'C:\dddd\ERP_JXC_Log.ldf' )
FOR ATTACH
GO
3、SQLServer2005 用户、组或角色 在当前数据库中已存在 无法删除数据库所有用户
4、Sql Server 操作xml
declare @divXml xml
set @divXml='<root>
<user>
<userEid>52</userEid>
<commisstionRatio>60</commisstionRatio>
</user>
<user>
<userEid>53</userEid>
<commisstionRatio>40</commisstionRatio>
</user>
</root>'
SELECT doc.col.value('userEid[1]', 'nvarchar(10)') UserEid,
doc.col.value('commisstionRatio[1]', 'numeric(18,2)') commisstionRatio
FROM @divXml.nodes('/root/user') doc(col)