22,210
社区成员
发帖
与我相关
我的任务
分享
SELECT DISTINCT
t_BOSqlgxhb.FID
,t_BOSqlgxhb.FClassTypeID
,t_BOSqlgxhbEntry.FEntryID
,t_BOSqlgxhbEntry.FIndex
,t_User.FName
FROM t_BOSqlgxhb
INNER JOIN t_BOSqlgxhbEntry ON t_BOSqlgxhb.FID=t_BOSqlgxhbEntry.FID
LEFT JOIN t_Item_3013 ON t_BOSqlgxhb.FGX=t_Item_3013.FItemID AND t_Item_3013.FItemID<>0
LEFT JOIN t_User ON t_BOSqlgxhb.FBiller=t_User.FUserID AND t_User.FUserID<>0
LEFT JOIN t_User t_User2 ON t_BOSqlgxhb.FModifier=t_User2.FUserID AND t_User2.FUserID<>0
LEFT JOIN t_ICItem t_ICItem1 ON t_BOSqlgxhbEntry.FBase9=t_ICItem1.FItemID AND t_ICItem1.FItemID<>0
LEFT JOIN t_ICItem ON t_BOSqlgxhbEntry.FitemID=t_ICItem.FItemID AND t_ICItem.FItemID<>0
LEFT JOIN t_Item t_ICItem_FBaseProperty1 ON t_ICItem.F_121=t_ICItem_FBaseProperty1.FItemID AND t_ICItem_FBaseProperty1.FItemID<>0
LEFT JOIN t_Item t_ICItem_FBaseProperty2 ON t_ICItem.F_150=t_ICItem_FBaseProperty2.FItemID AND t_ICItem_FBaseProperty2.FItemID<>0
LEFT JOIN t_Item t_ICItem_FBaseProperty10 ON t_ICItem.F_141=t_ICItem_FBaseProperty10.FItemID AND t_ICItem_FBaseProperty10.FItemID<>0
LEFT JOIN t_Item t_ICItem_FBaseProperty11 ON t_ICItem.F_131=t_ICItem_FBaseProperty11.FItemID AND t_ICItem_FBaseProperty11.FItemID<>0
LEFT JOIN t_Measureunit ON t_BOSqlgxhbEntry.Funit=t_Measureunit.FItemID AND t_Measureunit.FItemID<>0
LEFT JOIN t_Emp ON t_BOSqlgxhbEntry.FEmp=t_Emp.FItemID AND t_Emp.FItemID<>0
LEFT JOIN t_Emp t_Emp2 ON t_BOSqlgxhbEntry.FEmp2=t_Emp2.FItemID AND t_Emp2.FItemID<>0
LEFT JOIN t_Emp t_Emp1 ON t_BOSqlgxhbEntry.Fbanz=t_Emp1.FItemID AND t_Emp1.FItemID<>0
LEFT JOIN t_Emp t_Emp4 ON t_BOSqlgxhbEntry.FBase5=t_Emp4.FItemID AND t_Emp4.FItemID<>0
LEFT JOIN t_Emp t_Emp3 ON t_BOSqlgxhbEntry.Fzhuren=t_Emp3.FItemID AND t_Emp3.FItemID<>0
WHERE
(
charindex('卢若卡',t_User.FName)>0
and charindex('铂大铸造-重力浇铸',t_Item_3013.FName)>0
and charindex('A6091',t_ICItem.FName)>0
and t_BOSqlgxhb.FHBDate BETWEEN '2017-08-01' AND '2017-08-31 23:59:59'
)
AND t_BOSqlgxhb.FClassTypeID=200000013
Order By t_BOSqlgxhb.FID,t_BOSqlgxhbEntry.FIndex
--先筛选了再去连接, 还慢你来找我
IF OBJECT_ID('tempdb..#t_Item_3013') IS NOT NULL
DROP TABLE #t_Item_3013
IF OBJECT_ID('tempdb..#t_User') IS NOT NULL
DROP TABLE #t_User
IF OBJECT_ID('tempdb..#t_BOSqlgxhb') IS NOT NULL
DROP TABLE #t_BOSqlgxhb
SELECT FItemID FROM t_Item_3013
INTO #t_Item_3013
WHERE t_Item_3013.FItemID <> 0
AND t_Item_3013.FName LIKE '%铂大铸造-重力浇铸%'
SELECT * FROM t_User
INTO #t_User
WHERE t_User.FName = '卢若卡'
SELECT * FROM t_BOSqlgxhb
INTO #t_BOSqlgxhb
where t_BOSqlgxhb.FHBDate BETWEEN '2017-08-01' AND '2017-08-31 23:59:59'
AND t_BOSqlgxhb.FClassTypeID = 200000013
--还有的条件你也可以这么改,太花时间略过吧
SELECT DISTINCT t_BOSqlgxhb.FID,
t_BOSqlgxhb.FClassTypeID,
t_BOSqlgxhbEntry.FEntryID,
t_BOSqlgxhbEntry.FIndex,
t_User.FName
FROM t_BOSqlgxhb
INNER JOIN #t_BOSqlgxhb AS t_BOSqlgxhbEntry
ON t_BOSqlgxhb.FID = t_BOSqlgxhbEntry.FID
LEFT JOIN #t_Item_3013 AS t_Item_3013
ON t_BOSqlgxhb.FGX = t_Item_3013.FItemID
AND t_Item_3013.FItemID <> 0
LEFT JOIN #t_User AS t_User
ON t_BOSqlgxhb.FBiller = t_User.FUserID
AND t_User.FUserID <> 0
LEFT JOIN t_User t_User2
ON t_BOSqlgxhb.FModifier = t_User2.FUserID
AND t_User2.FUserID <> 0
LEFT JOIN t_ICItem t_ICItem1
ON t_BOSqlgxhbEntry.FBase9 = t_ICItem1.FItemID
AND t_ICItem1.FItemID <> 0
LEFT JOIN t_ICItem
ON t_BOSqlgxhbEntry.FitemID = t_ICItem.FItemID
AND t_ICItem.FItemID <> 0
LEFT JOIN t_Item t_ICItem_FBaseProperty1
ON t_ICItem.F_121 = t_ICItem_FBaseProperty1.FItemID
AND t_ICItem_FBaseProperty1.FItemID <> 0
LEFT JOIN t_Item t_ICItem_FBaseProperty2
ON t_ICItem.F_150 = t_ICItem_FBaseProperty2.FItemID
AND t_ICItem_FBaseProperty2.FItemID <> 0
LEFT JOIN t_Item t_ICItem_FBaseProperty10
ON t_ICItem.F_141 = t_ICItem_FBaseProperty10.FItemID
AND t_ICItem_FBaseProperty10.FItemID <> 0
LEFT JOIN t_Item t_ICItem_FBaseProperty11
ON t_ICItem.F_131 = t_ICItem_FBaseProperty11.FItemID
AND t_ICItem_FBaseProperty11.FItemID <> 0
LEFT JOIN t_Measureunit
ON t_BOSqlgxhbEntry.Funit = t_Measureunit.FItemID
AND t_Measureunit.FItemID <> 0
LEFT JOIN t_Emp
ON t_BOSqlgxhbEntry.FEmp = t_Emp.FItemID
AND t_Emp.FItemID <> 0
LEFT JOIN t_Emp t_Emp2
ON t_BOSqlgxhbEntry.FEmp2 = t_Emp2.FItemID
AND t_Emp2.FItemID <> 0
LEFT JOIN t_Emp t_Emp1
ON t_BOSqlgxhbEntry.Fbanz = t_Emp1.FItemID
AND t_Emp1.FItemID <> 0
LEFT JOIN t_Emp t_Emp4
ON t_BOSqlgxhbEntry.FBase5 = t_Emp4.FItemID
AND t_Emp4.FItemID <> 0
LEFT JOIN t_Emp t_Emp3
ON t_BOSqlgxhbEntry.Fzhuren = t_Emp3.FItemID
AND t_Emp3.FItemID <> 0
WHERE t_ICItem.FName LIKE '%A6091%'
ORDER BY
t_BOSqlgxhb.FID,
t_BOSqlgxhbEntry.FIndex
SELECT DISTINCT
t_BOSqlgxhb.FID ,
t_BOSqlgxhb.FClassTypeID ,
t_BOSqlgxhbEntry.FEntryID ,
t_BOSqlgxhbEntry.FIndex ,
t_User.FName
INTO #temp
FROM t_BOSqlgxhb
INNER JOIN t_BOSqlgxhbEntry ON t_BOSqlgxhb.FID = t_BOSqlgxhbEntry.FID
LEFT JOIN t_Item_3013 ON t_BOSqlgxhb.FGX = t_Item_3013.FItemID
AND t_Item_3013.FItemID <> 0
LEFT JOIN t_User ON t_BOSqlgxhb.FBiller = t_User.FUserID
AND t_User.FUserID <> 0
LEFT JOIN t_User t_User2 ON t_BOSqlgxhb.FModifier = t_User2.FUserID
AND t_User2.FUserID <> 0
LEFT JOIN t_ICItem t_ICItem1 ON t_BOSqlgxhbEntry.FBase9 = t_ICItem1.FItemID
AND t_ICItem1.FItemID <> 0
LEFT JOIN t_ICItem ON t_BOSqlgxhbEntry.FitemID = t_ICItem.FItemID
AND t_ICItem.FItemID <> 0
LEFT JOIN t_Item t_ICItem_FBaseProperty1 ON t_ICItem.F_121 = t_ICItem_FBaseProperty1.FItemID
AND t_ICItem_FBaseProperty1.FItemID <> 0
LEFT JOIN t_Item t_ICItem_FBaseProperty2 ON t_ICItem.F_150 = t_ICItem_FBaseProperty2.FItemID
AND t_ICItem_FBaseProperty2.FItemID <> 0
LEFT JOIN t_Item t_ICItem_FBaseProperty10 ON t_ICItem.F_141 = t_ICItem_FBaseProperty10.FItemID
AND t_ICItem_FBaseProperty10.FItemID <> 0
LEFT JOIN t_Item t_ICItem_FBaseProperty11 ON t_ICItem.F_131 = t_ICItem_FBaseProperty11.FItemID
AND t_ICItem_FBaseProperty11.FItemID <> 0
LEFT JOIN t_Measureunit ON t_BOSqlgxhbEntry.Funit = t_Measureunit.FItemID
AND t_Measureunit.FItemID <> 0
LEFT JOIN t_Emp ON t_BOSqlgxhbEntry.FEmp = t_Emp.FItemID
AND t_Emp.FItemID <> 0
LEFT JOIN t_Emp t_Emp2 ON t_BOSqlgxhbEntry.FEmp2 = t_Emp2.FItemID
AND t_Emp2.FItemID <> 0
LEFT JOIN t_Emp t_Emp1 ON t_BOSqlgxhbEntry.Fbanz = t_Emp1.FItemID
AND t_Emp1.FItemID <> 0
LEFT JOIN t_Emp t_Emp4 ON t_BOSqlgxhbEntry.FBase5 = t_Emp4.FItemID
AND t_Emp4.FItemID <> 0
LEFT JOIN t_Emp t_Emp3 ON t_BOSqlgxhbEntry.Fzhuren = t_Emp3.FItemID
AND t_Emp3.FItemID <> 0
WHERE ( t_Item_3013.FName LIKE '%铂大铸造-重力浇铸%'
AND t_ICItem.FName LIKE '%A6091%'
AND t_BOSqlgxhb.FHBDate BETWEEN '2017-08-01'
AND '2017-08-31 23:59:59'
)
AND t_BOSqlgxhb.FClassTypeID = 200000013
ORDER BY t_BOSqlgxhb.FID ,
t_BOSqlgxhbEntry.FIndex;
SELECT *
FROM #temp
WHERE FName = '卢若卡';
LEFT JOIN t_User ON t_BOSqlgxhb.FBiller=t_User.FUserID AND t_User.FUserID<>0
LEFT JOIN t_User t_User2 ON t_BOSqlgxhb.FModifier=t_User2.FUserID AND t_User2.FUserID<>0
-- 换成
LEFT JOIN t_User ON (t_BOSqlgxhb.FBiller=t_User.FUserID or t_BOSqlgxhb.FModifier=t_User.FUserID) AND t_User.FUserID<>0
create index Idx_t_user on t_User(FName)