SQL Server的使用笔记
SQL Server的使用笔记
一、比较复杂的update语句：update .. from?
?
update T_BuyMaterialReceiveStoreDetail
set QCID = a.QCID,
QCQty = isnull(a.QCQty,a.Qty),
QCPricePercent = isnull(a.QCPricePercent,1),
QCFailedQty = isnull(a.QCFailedQty,0),
QCWasteQty = isnull(a.QCWasteQty,0),
QCShortQty = isnull(a.QCShortQty,0),
QCPercent = isnull(a.QCPercent,1),
Note = a.Note,
DeterMineResult=a.DeterMineResult
from (select * from T_Temp_BuyMaterialReceiveStoreDetail
where OperateID = @OperateID
and OperateTime = @OperateTime
and QCID = QCID) a
where T_BuyMaterialReceiveStoreDetail.BuyID = a.BuyID
and T_BuyMaterialReceiveStoreDetail.MaterialItemID = a.MaterialItemID
and T_BuyMaterialReceiveStoreDetail.ReceiveID = a.ReceiveID
and T_BuyMaterialReceiveStoreDetail.StoreID = a.StoreID
and T_BuyMaterialReceiveStoreDetail.MaterialColorID = a.MaterialColorID
and T_BuyMaterialReceiveStoreDetail.MaterialSizeID = a.MaterialSizeID
and T_BuyMaterialReceiveStoreDetail.SaleID = a.SaleID
and T_BuyMaterialReceiveStoreDetail.StyleID = a.StyleID
?
二、判断varchar型的数据是否空
len(Note)= 0?
?
三、将数据库的表的表结构的ntext类型的字段改为nvarchar(max)
select 'alter table ' + ?c.name+' alter column '+a.name+' nvarchar(max)'?
from sys.columns a
left join sys.types b on a.system_type_id = b.system_type_id and b.name='ntext'
left join sys.objects c on a.object_id = c.object_id and c.Type='U'
where b.name='ntext' and c.Type='U'
四、巧妙的将返回的所有行的记录以一行字符串的形式输出
?
DECLARE @STR VARCHAR(8000)
SELECT @STR = ISNULL(@STR,'')+salecode+char(13) FROM t_sale?
SELECT @STR?
五、处理bit的类型的数据不能maxmax(cast(a.State as int))?
