一,游标的用法
USE pubs
DECLARE tnames_cursor CURSOR
FOR
SELECTstate ,au_id
FROM authors
open tnames_cursor
declare @state varchar(50)
declare@au_id varchar(50)
Fetchtnames_cursor into @state,@au_id
while(@@fetch_status=0)
begin
if(@state='CA')
begin
print @au_id
end
FETCHNEXT FROM tnames_cursor INTO @state,@au_id
end
CLOSE tnames_cursor
DEALLOCATE tnames_cursor
二，取时间段
(1),Convert(varchar(11),storeExport.inTime,21)>= '2007-03-13' and Convert(varchar(11),storeExport.inTime,21)<='2007-03-15')
(2),两个日期做减法
DateDiff(interval, date1, date2)
interval 参数的设定值如下：
yyyy 年
q 季
m 月
y 一年的日数
d 日
w一周的日数
ww 周
h 时
n 分钟
s 秒
例:selectDateDiff(d, '2005-5-01','2007-05-01')
三,常用语句
1,select * from saleOrder order byrsoid asc --正排
2,select * fromsaleOrder order by rsoid desc --倒排
3,selecttop 5* from saleOrder --取前五行
4,selecttop 5* from saleOrder order by rsoId desc --取后五行
5,select distinct saleOrderCode fromsaleOrder --去掉编号重复的数据
6,selectsaleOrderCode from table1
union all
select saleOrderCode from table2
--将两个 SQL 语句的结果合并起来
比如希望返回前三名的比分，但是第三名有并列的，使用：
select top 3 * fromtable order by score
的话就只能返回三条记录，在这样的情况下，就可以使用：
selecttop 3 with ties * from table order by score
还有，有的时候我不希望出现重复的记录，那么可以使用：
select distinct top 3 * from table order by score
四,连接
交叉联合
SELECT *
FROM <table1 CROSS JOIN table2>
[WHERE <serach condition>]
自然联合
SELECT *
FROM <table1 NATURAL JOIN table2>
[WHERE <serach condition>]
内联合
SELECT *
FROM <table1 INNER JOIN table2>
[WHERE <serach condition>]
[ON <serach condition>]
[USING <column name,...last column name>]
外联合
SELECT *
FROM <table1 FULL OUTER JOIN table2>
[WHERE <serach condition>]
[ON <serach condition>]
[USING <column name,...last column name>]
左外联合
SELECT *
FROM <table1 LEFT OUTER JOIN table2>
[WHERE <serach condition>]
[ON <serach condition>]
[USING <column name,...last column name>]
右外联合
SELECT *
FROM <table1 RIGHT OUTER JOIN table2>
[WHERE <serach condition>]
[ON <serach condition>]
[USING <column name,...last column name>]
如何在存储过程里插完主表返回主表ID:
1,在存储过程里插主表:
ALTER PROCEDURE [dbo].[UP_saleOrder_ADD]
@rsoIdint output,
.......
as
INSERTINTO saleOrder(
[rsoCcrid],[rsoSusid],[rsoCode],[rsoTime],[rsoDelivertime],[rsoDeliveraddr],[rsoDiscount],
[rsoStatus],[rsoArole],[rsoMoney],[rsoDownpay],[PayID],[rsoFinaldiscount],[rsoSdtid],[rsoMemo],
[rsoType],[rsoSourcecode],[rsoIsinvoice],[rsoFinalPayID],[clientName],[clientTel],[clientCard]
)VALUES(
@rsoCcrid,@rsoSusid,dbo.GetContractCode(getdate()),@rsoTime,@rsoDelivertime,@rsoDeliveraddr,@rsoDiscount,
@rsoStatus,@rsoArole,@rsoMoney,@rsoDownpay,@PayID,@rsoFinaldiscount,@rsoSdtid,@rsoMemo,
@rsoType,@rsoSourcecode,@rsoIsinvoice,@rsoFinalPayID,@clientName,@clientTel,@clientCard
)
SET@rsoId = @@IDENTITY
2,在程序里接收存储过程返回值:
public int Add(QuMeiXJ.Model.saleOrdermodel)
{
SqlParameter[] parameters = {
newSqlParameter("@rsoId", SqlDbType.Int,4),
new SqlParameter("@rsoCcrid", SqlDbType.Int,4),
....................................................................略
};
parameters[0].Direction =ParameterDirection.Output;
parameters[1].Value= model.rsoCcrid;
....................................................................略
DbHelperSQL.RunProcedure("UP_saleOrder_ADD",parameters);
return (int)parameters[0].Value;
}
3,case的使用:
例1
SELECTShipVia, CASE ShipVia WHEN 1 THEN 'A.Datum' WHEN 2 THEN 'Contoso' WHEN 3 THEN 'Consolidated Messenger' ELSE 'Unknown' ENDFROM Orders
例2
selectsaleOrderProductPack.IsStock,
casesaleOrderProductPack.IsStock when 0 then saleOrderProduct.IsStock
else saleOrderProductPack.IsStock end fromsaleOrderProductpack
inner joinsaleOrderProduct onsaleOrderProductPack.saleOrderProductID=saleOrderProduct.ID
4,SubString 截取字符串
例1:截取BJPX0711260155-1单号"-"以后的部分
selectsubstring(BJPX0711260155-1,CHARINDEX('-',BJPX0711260155-1)+1,len(BJPX0711260155-1)-CHARINDEX('-',BJPX0711260155-1))
例2:字段RetailContractNumber=1,2,3,4,查询显示时去掉最后一个逗号:
SELECT substring(RetailContractNumber,1,len(RetailContractNumber)-1from table
5,存储过程里拼串变量赋值
declare @str nvarchar(4000) --变量类型,大小必须如此定义
declare @Cou int
declare @SaleOrderCode varchar(30)
select @str=N'select @Cou=count(*) fromOperEncasement where EncasementCode like ''%'+cast(@SaleOrderCode asvarchar(30))+'%'''
exec sp_executesql@str, N'@Cou int output',@Cou output
print@Cou
6,存储过程里执行存储过程取它的返回值:
declare @ECount int
set @ECount = 0
EXECUTEUP_OperStockApplySplit3 @stockApplyDetailID , @Count = @ECountOUTPUT //@Count是另一个存储过程的返回值
if(@ECount> 0 )
begin
SET @result = 3
GOTO ERROR
end
7,替换查询结果部分内容(把<br>替换为---):
select replace(cast(productMaterial asvarchar(8000)),'<br>','---') from buyOrderproduct
联系客服
