是这样的,客户有的员工做小动作,把一车登记两次进行搞鬼,所以客户领导要一个功能检查同一台车在10分钟内有多次登记入库过磅!表结构(简化)
tbRKB(
ZBID varchar(40) , --主键
BillCode varchar(40),--入库号
BillDate DateTime,--入库时间
ClientID Varchar(40),--供应商主键
MID varchar(40),--材料主键
CarCode varchar(40),--车号
MZ decimal(18,4),--毛重
PZ decimal(18,4),--皮重
JZ decimal(18,4)--净重
)如何写检索语句呢?
tbRKB(
ZBID varchar(40) , --主键
BillCode varchar(40),--入库号
BillDate DateTime,--入库时间
ClientID Varchar(40),--供应商主键
MID varchar(40),--材料主键
CarCode varchar(40),--车号
MZ decimal(18,4),--毛重
PZ decimal(18,4),--皮重
JZ decimal(18,4)--净重
)如何写检索语句呢?
where exists(select 1 from tbRKB where CarCode=a.Carcode and billdate<>a.billdate and abs(datediff(m,billdate,a.billdate))<10)
*
from
tbRKB t
where
exists(select 1 from tbRKB where CarCode=t.Carcode and billdate<>t.billdate and abs(datediff(mi,billdate,t.billdate))<=10)
*,
(SELECT COUNT(1) FROM tbRKB WHERE CarCode=a.CarCode AND DATEDIFF(n,BillDate,a.BillDate)<10) AS 次
FROM tbRKB AS a
NOT EXISTS(SELECT 1 FROM tbRKB WHERE CarCode=a.CarCode AND DATEDIFF(n,BillDate,a.BillDate)<10 AND BillDate>a.BillDate)
where exists(select * from tbRKB where CarCode=a.CarCode
and abs(datediff(mi,BillDate,a.BillDate))<10)