Question

SQL查询的时候有多个满足的条件我只想返回第一个结果就行，该怎么改？？？

Select u2.fnumber as 仓库代码,u2.FName as 仓库名称,ti.fnumber as 物料代码 ,ti.FName as 物料名称 ,ti.FModel as 型号,u1.FBatchNo as 批号,
tm.FName as 基本单位,cast(u1.FQty as numeric(18,0)) as 基本单位数量,convert(nvarchar,t1.FDate,111) as 制单日期,
convert(nvarchar,t1.FcheckDate,111) as 入库日期,convert(varchar(10),getdate(),120)-convert(nvarchar,t1.FcheckDate,111))as 账龄
from icinventory u1,t_ICItem ti,t_MeasureUnit tm,t_Stock u2,ICStockBill t1,ICStockBillentry t2
where tm.FItemID=ti.funitid and t1.FInterID=t2.FInterID and u1.FStockID=u2.FItemID and u1.FItemID=ti.FItemID
and u1.FQty>0
and u1.FBatchNo is not null
and u1.FBatchNo<>''and u1.FBatchNo<> 'KB' and u1.FBatchNo<> '*'
and t1.FTranType=2
and t2.FBatchNo=u1.FBatchNo
and getdate()-convert(nvarchar,t1.FcheckDate,111)>15
convert(varchar(10),getdate(),120)-convert(nvarchar,t1.FcheckDate,111))as 账龄

里面这个语句帮忙修正一下，提示错误。。
我要的是u1.FBatchNo这个到t2表里面查找返回第一条记录

Answer 1

用 top 1来返回一行，
用
datediff(day,t1.FcheckDate, getdate())as 账龄 来进行日期相减

Select top 1 u2.fnumber as 仓库代码,u2.FName as 仓库名称,ti.fnumber as 物料代码 ,ti.FName as 物料名称 ,ti.FModel as 型号,u1.FBatchNo as 批号,
tm.FName as 基本单位,cast(u1.FQty as numeric(18,0)) as 基本单位数量,convert(nvarchar,t1.FDate,111) as 制单日期,
convert(nvarchar,t1.FcheckDate,111) as 入库日期,
datediff(day,t1.FcheckDate, getdate())as 账龄
from icinventory u1,t_ICItem ti,t_MeasureUnit tm,t_Stock u2,ICStockBill t1,ICStockBillentry t2
where tm.FItemID=ti.funitid and t1.FInterID=t2.FInterID and u1.FStockID=u2.FItemID and u1.FItemID=ti.FItemID
and u1.FQty>0
and u1.FBatchNo is not null
and u1.FBatchNo<>''and u1.FBatchNo<> 'KB' and u1.FBatchNo<> '*'
and t1.FTranType=2
and t2.FBatchNo=u1.FBatchNo
and getdate()-convert(nvarchar,t1.FcheckDate,111)>15

追问

不行，提示在关键字 'top' 附近有语法错误。

Answer 2

你是用的什么数据库.如果是ms sql server的话就是top 1，如果是mysql milit 0,1

追问

用的是ms sql ，
Select u2.fnumber as 仓库代码,u2.FName as 仓库名称,ti.fnumber as 物料代码 ,ti.FName as 物料名称 ,ti.FModel as 型号,top 1 u1.FBatchNo as 批号,
就是提示TOP旁边又错误

追答

select top 1, u2...........

Answer 3

在后面加上 and rownum=1 就可以了

追问

不行 提示这个列明无效

追答

oracle肯定是好使的 你用哪个数据库？

追问

SQL2000，我要查找的是u1.FBatchNo这个到t2表里面返回第一条记录

追答

SELECT TOP number|percent column_name(s)
FROM table_name

追问

这个怎么弄啊，我很笨，直接放进去，提示错误了

Answer 4

select top 1 u2.fn..........

追问

把这一句放在什么位置呢？

追答

就把top 1放在 u2.fnumber as 仓库代码,u2.FName前面就好了

追问

还是不行，我要查找的是FBatchNo这个，返回的结果只要第一条记录