Select * from (
Select
ID,
CONVERT(Datetime,[OPS updated Date],111) as [OPS updated Date],
CONVERT(Datetime,[MLoading Date],111) as [MLoading Date],
[T/O#], Trucker, [Customs Broker],Userid,Client, [Client PO No],
[Factory Code], [Carrier SO], [MLOG SO], [Container No],Size, Etd,
[Delivery Place],Incoterm, [Shipper Code], [Service Type], [Custom Type],
Location,Team,[Charge Name],[Charge Code (FACT)],
[Charge Cost]= T3Cost,
Currency, Payer,[OPS Re], MLOG_SO, Income, _Currency,
CUS_UID,[FCR No],REFN,[MSO No],[mItem No],[PO No], [pItem No],OPS_UID,
Re,CM1,OComments,opsStatus,cusStatus,otcstatus,xStatus,MR,exID
from (
Select t1.*,t3.[Charge Cost] as T3Cost From (
Select * From TRKTPO WHERE [T/O#] In ( SELECT t2.[T/O#] from TRKTPO t2 ) ) AS t1 JOIN (
Select [T/O#],[Charge Name],sum([Charge Cost]) [Charge Cost] FROM TRKTPO group by [T/O#],[Charge Name]) t3
ON t1.[T/O#] = t3.[T/O#] and t1.[Charge Name]=t3.[Charge Name]) as TRKTPO
WHERE
not exists (select * from TRKTPO t4 where t4.[T/O#]=[T/O#] and t4.[Charge Name]=[Charge Name] and t4.id<id)
AND xStatus=0
ORDER BY [MLoading Date] DESC,[T/O#],exID,
charindex([Charge Code (FACT)],'100177,100248,100408,100178,100468,100357,100195')
如何修改以上语句 让它所花时间更少 ( 这条语句 现在在我机器上运行 超时 对于11410条数据)
肯请大家帮忙修改一下(说明一下:其中
Select [T/O#],[Charge Name],sum([Charge Cost]) [Charge Cost] FROM TRKTPO group by [T/O#],[Charge Name]
是想实现对于相同[T/O#]下面的相同[Charge Name](费用)加总 ,实际上只要求对Team为'TGT'的情况实现加总,但这条语句中没有体现)顶者有分,在线恭候
Select
ID,
CONVERT(Datetime,[OPS updated Date],111) as [OPS updated Date],
CONVERT(Datetime,[MLoading Date],111) as [MLoading Date],
[T/O#], Trucker, [Customs Broker],Userid,Client, [Client PO No],
[Factory Code], [Carrier SO], [MLOG SO], [Container No],Size, Etd,
[Delivery Place],Incoterm, [Shipper Code], [Service Type], [Custom Type],
Location,Team,[Charge Name],[Charge Code (FACT)],
[Charge Cost]= T3Cost,
Currency, Payer,[OPS Re], MLOG_SO, Income, _Currency,
CUS_UID,[FCR No],REFN,[MSO No],[mItem No],[PO No], [pItem No],OPS_UID,
Re,CM1,OComments,opsStatus,cusStatus,otcstatus,xStatus,MR,exID
from (
Select t1.*,t3.[Charge Cost] as T3Cost From (
Select * From TRKTPO WHERE [T/O#] In ( SELECT t2.[T/O#] from TRKTPO t2 ) ) AS t1 JOIN (
Select [T/O#],[Charge Name],sum([Charge Cost]) [Charge Cost] FROM TRKTPO group by [T/O#],[Charge Name]) t3
ON t1.[T/O#] = t3.[T/O#] and t1.[Charge Name]=t3.[Charge Name]) as TRKTPO
WHERE
not exists (select * from TRKTPO t4 where t4.[T/O#]=[T/O#] and t4.[Charge Name]=[Charge Name] and t4.id<id)
AND xStatus=0
ORDER BY [MLoading Date] DESC,[T/O#],exID,
charindex([Charge Code (FACT)],'100177,100248,100408,100178,100468,100357,100195')
如何修改以上语句 让它所花时间更少 ( 这条语句 现在在我机器上运行 超时 对于11410条数据)
肯请大家帮忙修改一下(说明一下:其中
Select [T/O#],[Charge Name],sum([Charge Cost]) [Charge Cost] FROM TRKTPO group by [T/O#],[Charge Name]
是想实现对于相同[T/O#]下面的相同[Charge Name](费用)加总 ,实际上只要求对Team为'TGT'的情况实现加总,但这条语句中没有体现)顶者有分,在线恭候
你的SQL 嵌套SQL太多,而且不知道字段和表的关系,看起来太累了
Select * from (
Select
ID,
CONVERT(Datetime,[OPS updated Date],111) as [OPS updated Date],
CONVERT(Datetime,[MLoading Date],111) as [MLoading Date],
[T/O#], Trucker, [Customs Broker],Userid,Client, [Client PO No],
[Factory Code], [Carrier SO], [MLOG SO], [Container No],Size, Etd,
[Delivery Place],Incoterm, [Shipper Code], [Service Type], [Custom Type],
Location,Team,[Charge Name],[Charge Code (FACT)],
[Charge Cost]= T3Cost,
Currency, Payer,[OPS Re], MLOG_SO, Income, _Currency,
CUS_UID,[FCR No],REFN,[MSO No],[mItem No],[PO No], [pItem No],OPS_UID,
Re,CM1,OComments,opsStatus,cusStatus,otcstatus,xStatus,MR,exID
from (
Select t1.*,t3.[Charge Cost] as T3Cost From (
Select * From TRKTPO WHERE [T/O#] In ( SELECT t2.[T/O#] from TRKTPO t2 ) ) AS t1 JOIN (
Select [T/O#],[Charge Name],sum([Charge Cost]) [Charge Cost] FROM TRKTPO group by [T/O#],[Charge Name]) t3
ON t1.[T/O#] = t3.[T/O#] and t1.[Charge Name]=t3.[Charge Name]) as TRKTPO
WHERE
not exists (select * from TRKTPO t4 where t4.[T/O#]=[T/O#] and t4.[Charge Name]=[Charge Name] and t4.id <id)
AND xStatus=0
ORDER BY [MLoading Date] DESC,[T/O#],exID,
charindex([Charge Code (FACT)],'100177,100248,100408,100178,100468,100357,100195')
CONVERT(Datetime,[OPS updated Date],111) as [OPS updated Date],
CONVERT(Datetime,[MLoading Date],111) as [MLoading Date],
[T/O#], Trucker, [Customs Broker],Userid,Client, [Client PO No],
[Factory Code], [Carrier SO], [MLOG SO], [Container No],Size, Etd,
[Delivery Place],Incoterm, [Shipper Code], [Service Type], [Custom Type],
Location,Team,[Charge Name],[Charge Code (FACT)],
[Charge Cost]= T3Cost,
Currency, Payer,[OPS Re], MLOG_SO, Income, _Currency,
CUS_UID,[FCR No],REFN,[MSO No],[mItem No],[PO No], [pItem No],OPS_UID,
Re,CM1,OComments,opsStatus,cusStatus,otcstatus,xStatus,MR,exID
from (
Select t1.*,t3.[Charge Cost] as T3Cost
From TRKTPO AS t1
LEFT JOIN
(
Select [T/O#],[Charge Name],sum([Charge Cost]) [Charge Cost]
FROM TRKTPO
group by [T/O#],[Charge Name]
) t3
ON t1.[T/O#] = t3.[T/O#] and t1.[Charge Name]=t3.[Charge Name]
) as TRKTPO
WHERE not exists (select 1 from TRKTPO t4 where t4.[T/O#]=[T/O#] and t4.[Charge Name]=[Charge Name] and t4.id <id)
AND xStatus=0
ORDER BY [MLoading Date] DESC,[T/O#],exID,charindex([Charge Code (FACT)],'100177,100248,100408,100178,100468,100357,100195')
not exists (select 1 from TRKTPO t4 where t4.[T/O#]=[T/O#] and t4.[Charge Name]=[Charge Name] and t4.id <id)
是个问题,看能不能加个字段先处理好。
SELECT
ID,
CONVERT(Datetime,[OPS updated Date],111) as [OPS updated Date],
CONVERT(Datetime,[MLoading Date],111) as [MLoading Date],
[T/O#], Trucker, [Customs Broker],Userid,Client, [Client PO No],
[Factory Code], [Carrier SO], [MLOG SO], [Container No],Size, Etd,
[Delivery Place],Incoterm, [Shipper Code], [Service Type], [Custom Type],
Location,Team,[Charge Name],[Charge Code (FACT)],
[Charge Cost]= T3Cost,
Currency, Payer,[OPS Re], MLOG_SO, Income, _Currency,
CUS_UID,[FCR No],REFN,[MSO No],[mItem No],[PO No], [pItem No],OPS_UID,
Re,CM1,OComments,opsStatus,cusStatus,otcstatus,xStatus,MR,exID
FROM (
Select t1.*,t3.[Charge Cost] as T3Cost From (
Select * From TRKTPO WHERE EXISTS ( SELECT t2.[T/O#] from TRKTPO t2 WHERE [T/O#]=t2.[T/O#]) ) AS t1 JOIN (
Select [T/O#],[Charge Name],sum([Charge Cost]) [Charge Cost] FROM TRKTPO group by [T/O#],[Charge Name]) t3
ON t1.[T/O#] = t3.[T/O#] and t1.[Charge Name]=t3.[Charge Name]) as TRKTPO
WHERE
not exists (select * from TRKTPO t4 where t4.[T/O#]=[T/O#] and t4.[Charge Name]=[Charge Name] and t4.id <id)
AND xStatus=0
ORDER BY [MLoading Date] DESC,[T/O#],exID,
charindex([Charge Code (FACT)],'100177,100248,100408,100178,100468,100357,100195')
前面多写了 "Select * from (" 以下语句是语法正确
各位对不起了Select
ID,
CONVERT(Datetime,[OPS updated Date],111) as [OPS updated Date],
CONVERT(Datetime,[MLoading Date],111) as [MLoading Date],
[T/O#], Trucker, [Customs Broker],Userid,Client, [Client PO No],
[Factory Code], [Carrier SO], [MLOG SO], [Container No],Size, Etd,
[Delivery Place],Incoterm, [Shipper Code], [Service Type], [Custom Type],
Location, Team, [Charge Name], [Charge Code (FACT)],
[Charge Cost]= T3Cost,
Currency,Payer,[OPS Re], MLOG_SO, Income, _Currency,
CUS_UID,[FCR No],REFN,[MSO No],[mItem No],[PO No], [pItem No],OPS_UID,
Re,CM1,OComments,opsStatus,cusStatus,otcstatus,xStatus,MR,exID
from (
Select t1.*,t3.[Charge Cost] as T3Cost From (
Select * From TRKTPO WHERE [T/O#] In ( SELECT t2.[T/O#] from TRKTPO t2 ) ) AS t1 JOIN (
Select [T/O#],[Charge Name],sum([Charge Cost]) [Charge Cost] FROM TRKTPO group by [T/O#],[Charge Name]) t3
ON t1.[T/O#]=t3.[T/O#] and t1.[Charge Name]=t3.[Charge Name]) as TRKTPO
WHERE
not exists (select * from TRKTPO t4 where t4.[T/O#]=[T/O#] and t4.[Charge Name]=[Charge Name] and t4.id<id)
AND xStatus=0 ORDER BY [MLoading Date] DESC,[T/O#],exID,
charindex([Charge Code (FACT)],'100177,100248,100408,100178,100468,100357,100195')
TRKTPO表结构如下:
ID BigInt 8
[OPS updated Date] VarChar 30
[MLoading Date] VarChar 10
[T/O#] VarChar 50
Trucker VarChar 10
[Customs Broker] VarChar 50
Userid VarChar 10
Client VarChar 50
[Client PO No] VarChar 50
[Factory Code] VarChar 255
[Carrier SO] VarChar 200
[MLOG SO] VarChar 255
[Container No] VarChar 50
Size VarChar 20
Etd VarChar 10
[Delivery Place] VarChar 50
Incoterm VarChar 20
[Shipper Code] VarChar 50
[Service Type] VarChar 20
[Custom Type] VarChar 50
Location VarChar 20
Team VarChar 50
[Charge Name] VarChar 120
[Charge Code (FACT)]VarChar 30
[Charge Cost] Real 4
Currency VarChar 10
Payer VarChar 50
[OPS Re] VarChar 200
MLOG_SO VarChar 255
Income Real 4
_Currency VarChar 10
CUS_UID VarChar 10
[FCR No] VarChar 255
REFN VarChar 255
[MSO No] VarChar 255
[mItem No] VarChar 255
[PO No] VarChar 255
[pItem No] VarChar 255
OPS_UID VarChar 10
Re VarChar 200
CM1 VarChar 10
OComments VarChar 200
opsStatus Bit 1
cusStatus Bit 1
otcstatus Bit 1
xStatus Bit 1
MR Bit 1
exID VarChar 200
从下面的语句看,你这个查询可能最多只会取出一条记录:
not exists (select 1 from TRKTPO t4 where t4.[T/O#]=[T/O#] and t4.[Charge Name]=[Charge Name] and t4.id <id)
-----------------------------------
如果 相同[T/O#]的 [Charge Name]出现多次只会取出一条记录To: Novelty
order by中的charindex([Charge Code (FACT)],'100177,100248,100408,100178,100468,100357,100195')
是个问题,看能不能加个字段先处理好。
--------------------------------
其实这里是 对于同一个[T/O#] 下的七种费用 按照费用代码'100177,100248,100408,100178,100468,100357,100195'的顺序排列
感觉加一个字段来处理也不好处理To: zzxap , fzcheng
你们的语句试过了,不行
真的哟,有些排序的字段没索引呢。 good question
在我的team中谁写这样的SQL,我就开他
--------------------------------TMD,先解决问题好吗 ,你要开谁也与我们没关系对吗
0808010341 Trucking 100177 903.00 VDR TGT 111111111
0808010341 Customs Declaration Administration Fee 100248 0.00 VDR TGT 111111111
0808010341 Trucking Overnight Surcharge 100408 10.00 VDR TGT 111111111
0808010341 Trucking-Empty (预提) 100178 6.00 VDR TGT 111111111
0808010341 Port Entry 100468 0.00 VDR TGT 111111111
0808010341 Port Additional 100357 3.00 VDR TGT 111111111
0808010341 Additional Item Charge 100195 1.00 VDR TGT 111111111
0808010341 Trucking 100177 0.00 TGT TGT 100000001
0808010341 Customs Declaration Administration Fee 100248 8.00 TGT TGT 100000001
0808010341 Trucking Overnight Surcharge 100408 0.00 TGT TGT 100000001
0808010341 Trucking-Empty (预提) 100178 0.00 TGT TGT 100000001
0808010341 Port Entry 100468 9.00 TGT TGT 100000001
0808010341 Port Additional 100357 0.00 TGT TGT 100000001
0808010341 Additional Item Charge 100195 2.00 TGT TGT 100000001
0808011340 Trucking 100177 0.00 HAF
0808011340 Customs Declaration Administration Fee 100248 8.00 HAF
0808011340 Trucking Overnight Surcharge 100408 0.00 HAF
0808011340 Trucking-Empty (预提) 100178 0.00 HAF
0808011340 Port Entry 100468 9.00 HAF
0808011340 Port Additional 100357 0.00 HAF
0808011340 Additional Item Charge 100195 2.00 HAF 每一个[T/O#] 对应七种费用 : [T/O#]--定单号,VDR ---发货人,TGT---收件人,Payer--付款人
对于TGT team 的[T/O#] 描述:
-----------------------------------
由于付款人不一样 所以同一个[T/O#]七种费用中一部分费用由VDR付 另一部分费用由TGT 付,
对于相同[T/O#]的相同种费用如:
[T/O#] 0808010341有两个相同的 Trucking 费
Trucking 如果VDR付了钱,我们就不能再找TGT收钱 所以 Payer--TGT 所对应的 Charge Cost 应为0
有一点不一样的是对于 Additional Item Charge 我们有可能 同时向 VDR 和 TGT收钱,也就是说相同[T/O#]下面的相同
Additional Item Charge 可能向 VDR收1 块钱,也向TGT收了2块. 注意: exID 只是当 Team ='TGT' 时 才会有,且相同[T/O#] 因payer 不样 exID也不相同 ,exID是我为了方便排序加入的字段
对于非TGT team 的[T/O#]描述 :
如 HAF team的[T/O#] 因为七种费用的付款人都是一样的,所以相同[T/O#]的同一种费用只会出现一次
意图 :
对于某一特定的用户组, 无论是哪个Team的[T/O#],对于一个相同[T/O#]只希望看到每种费的收费总数 (不关心是由谁付)因此我要将上面 TGT team 的数据显示为
[T/O#] [Charge Name] [Charge Code (FACT)] [Charge Cost] Payer Team exID<- 字段名
0808010341 Trucking 100177 903.00 VDR TGT 111111111
0808010341 Customs Declaration Administration Fee 100248 8.00 VDR TGT 111111111
0808010341 Trucking Overnight Surcharge 100408 10.00 VDR TGT 111111111
0808010341 Trucking-Empty (预提) 100178 6.00 VDR TGT 111111111
0808010341 Port Entry 100468 9.00 VDR TGT 111111111
0808010341 Port Additional 100357 3.00 VDR TGT 111111111
0808010341 Additional Item Charge 100195 3.00 VDR TGT 111111111或者
[T/O#] [Charge Name] [Charge Code (FACT)] [Charge Cost] Payer Team exID<- 字段名
0808010341 Trucking 100177 903.00 TGT TGT 100000001
0808010341 Customs Declaration Administration Fee 100248 8.00 TGT TGT 100000001
0808010341 Trucking Overnight Surcharge 100408 10.00 TGT TGT 100000001
0808010341 Trucking-Empty (预提) 100178 6.00 TGT TGT 100000001
0808010341 Port Entry 100468 9.00 TGT TGT 100000001
0808010341 Port Additional 100357 3.00 TGT TGT 100000001
0808010341 Additional Item Charge 100195 3.00 TGT TGT 100000001
而对于非 TGT Team的数据 原样显示就可以了如果有任何疑问请回帖
from TRKTPO t1
left join
(select [T/O#],[Charge Name],[Charge Code (FACT)],sum([Charge Cost]) [Charge Cost]
from TRKTPO
where Payer='TGT'
Group by [T/O#],[Charge Name],[Charge Code (FACT)]
) t2
on t1.[T/O#]=t2.[T/O#] and t1.[Charge Name]=t2.[Charge Name] and t1.[Charge Code (FACT)]=t2.[Charge Code (FACT)]
where t1.Payer<>'VDR'如果你想取VDR对应该的结果,只要把上面的两个引号中的对调一下!
测试过你最后的语句, 发现有两点漏掉没有考虑:
第一: 对相同[T/O#] 下Additional Item Charge只显示一条且[Charge Cost]为和 如上面的
0808010341 Additional Item Charge 100195 3.00 VDR TGT 111111111
3.00 是 2.00 + 1.00 第二: 参照我的语句中有一条:
..... Select * From TRKTPO WHERE [T/O#] In ( SELECT t2.[T/O#] from TRKTPO t2 ......
其实是实现,无论用户怎么查询 ,都将一个[T/O#]下的七种费用完整显示 。
打个比如 : 当用户查询 [Charge Cost]=10.00 时 我希望 080010341 不仅只显示Trucking Overnight Surcharge,而且其他的六种费用
也显示在一起,这种用户始终能看到一个[T/O#]整体的情况.
select t1.[T/O#],t1.[Charge Name],t1.[Charge Code (FACT)],
Isnull(t2.[Charge Cost],t1.[Charge Cost]) [Charge Cost], '1 --->改为t2.[Charge Code]
t1.Payer,t1.Team,t1.exID
from TRKTPO t1
left join
(select [T/O#],[Charge Name],[Charge Code (FACT)],sum([Charge Cost]) [Charge Cost]
from TRKTPO
where Payer='TGT' '2--> 改为 Team='TGT'
Group by [T/O#],[Charge Name],[Charge Code (FACT)]
) t2
on t1.[T/O#]=t2.[T/O#] and t1.[Charge Name]=t2.[Charge Name] and t1.[Charge Code (FACT)]=t2.[Charge Code (FACT)]
where t1.Payer <>'VDR'
修改上面两处可以 第一点漏掉的问题如何进一步修改解决第二点漏掉的问题
其他不说,首先问一下,在where用到的条件,是否都加索引了?
如有更好的写法,可以追加上
Select * from (
select
t1.ID,
CONVERT(Datetime,t1.[OPS updated Date],111) as [OPS updated Date],
CONVERT(Datetime,t1.[MLoading Date],111) as [MLoading Date],
t1.[T/O#], t1.Trucker, t1.[Customs Broker],t1.Userid,t1.Client, t1.[Client PO No],
t1.[Factory Code], t1.[Carrier SO],t1.[MLOG SO],
t1.[Container No],t1.Size, t1.Etd, t1.[Delivery Place],t1.Incoterm,
t1.[Shipper Code], t1.[Service Type], t1.[Custom Type],
t1.Location, t1.Team, t1.[Charge Name], t1.[Charge Code (FACT)],
[Charge Cost]= case Team
when 'TGT' then t2.[Charge Cost]
else t1.[Charge Cost]
end ,
t1.Currency, t1.Payer,t1.[OPS Re], t1.MLOG_SO, t1.Income, t1._Currency, t1.CUS_UID,
t1.[FCR No],t1.REFN,t1.[MSO No],t1.[mItem No],t1.[PO No],
t1.[pItem No],t1.OPS_UID,t1.Re,t1.CM1,t1.OComments,t1.opsStatus,
t1.cusStatus,t1.otcstatus,t1.xStatus,t1.MR,t1.exID
from TRKTPO t1
left join
(select [T/O#],[Charge Name],[Charge Code (FACT)],sum([Charge Cost]) [Charge Cost] from TRKTPO where Team='TGT'
Group by [T/O#],[Charge Name],[Charge Code (FACT)]) t2
on t1.[T/O#]=t2.[T/O#] and t1.[Charge Name]=t2.[Charge Name] and
t1.[Charge Code (FACT)]=t2.[Charge Code (FACT)]
where t1.Payer <>'VDR' )
as TRKTPO
WHERE [T/O#] In ( SELECT t3.[T/O#] from TRKTPO t3 ) and [T/O#]='0808270263'
ORDER BY [MLoading Date] DESC,[T/O#],exID,charindex([Charge Code (FACT)],'100177,100248,100408,100178,100468,100357,100195') 谢谢各位的大力顶帖,鄙视Tigersong 牛B轰轰的假小子