| 以文本方式查看主题 - Foxtable(狐表) (http://foxtable.net/bbs/index.asp) -- 专家坐堂 (http://foxtable.net/bbs/list.asp?boardid=2) ---- [求助]drawcell中效率太低了 有没有别的办法啊 (http://foxtable.net/bbs/dispbbs.asp?boardid=2&id=50915) |
| -- 作者：jiskin -- 发布时间：2014/5/15 12:06:00 -- [求助]drawcell中效率太低了 有没有别的办法啊 Dim cmd As new SQLCommand cmd.ConnectionName -- "" If e.Row("Status") = "采购中" Then cmd.CommandText = "SELECT DISTINCT OrderNumber from {Purchase_Orders} where OrderNumber = \'" & e.Row("OrderNumber") & "\'and (select sum(Quantity) from {IQC_Details} where Identify = {Purchase_Orders}.[_Identify]) - (select sum(StorageQty) from {IQC_Details} where Identify = {Purchase_Orders}.[_Identify]) > 0 And DATEDIFF(day,GETDATE(),{Purchase_Orders}.DeliveryDate) <= 3 And DATEDIFF(day,GETDATE(),{Purchase_Orders}.DeliveryDate) >= 0" If cmd.ExecuteScalar IsNot Nothing Then e.Style = "timequick" End If cmd.CommandText = "SELECT DISTINCT OrderNumber from {Purchase_Orders} where OrderNumber = \'" & e.Row("OrderNumber") & "\'and (select sum(Quantity) from {IQC_Details} where Identify = {Purchase_Orders}.[_Identify]) - (select sum(StorageQty) from {IQC_Details} where Identify = {Purchase_Orders}.[_Identify]) > 0 And DATEDIFF(day,GETDATE(),{Purchase_Orders}.DeliveryDate) < 0 " If cmd.ExecuteScalar IsNot Nothing Then e.Style = "timeout" End If End If 这些写在drawcell事件里 效率太低了 有没有别的方法弄颜色上去啊
|
| -- 作者：Bin -- 发布时间：2014/5/15 12:14:00 -- 你居然在DrAWCELl做这样的操作,不卡才怪了 你的需求是什么,说一下.看看有什么好方案没
|
| -- 作者：jspta -- 发布时间：2014/5/15 12:30:00 -- cmd.CommandText = "SELECT DISTINCT OrderNumber from {Purchase_Orders} where OrderNumber = \'" & e.Row("OrderNumber") & "\'and (select sum(Quantity) from {IQC_Details} where Identify = {Purchase_Orders}.[_Identify]) - (select sum(StorageQty) from {IQC_Details} where Identify = {Purchase_Orders}.[_Identify]) > 0 And DATEDIFF(day,GETDATE(),{Purchase_Orders}.DeliveryDate) <= 3 And DATEDIFF(day,GETDATE(),{Purchase_Orders}.DeliveryDate) >= 0" If cmd.ExecuteScalar IsNot Nothing Then 用一个全局表进行代替。把相关数据全部加载，然后引用这个表，这个效率应该还能接受
|
| -- 作者：blackzhu -- 发布时间：2014/5/15 12:50:00 -- 不会吧 老大 一再说这个事件是很耗内存的 你居然这样用! |
| -- 作者：jiskin -- 发布时间：2014/5/15 13:20:00 -- 以下是引用Bin在2014-5-15 12:14:00的发言：
你居然在DrAWCELl做这样的操作,不卡才怪了 你的需求是什么,说一下.看看有什么好方案没
在订单表中状态为“采购中”的订单， 并且对应的订单明细表中排除该笔订单明细中已经到货的，只要有一项的预计到货日期和今天日期差3天以内， 将订单表中标记一个颜色，如果已经超过今天的日期并且未到货，那就标记另外一个颜色
BIN 大哥 看来还得做个辅助列是吧？ 把cmd.CommandText 中的内容做为一个辅助列估计就可以了吧 |
| -- 作者：jiskin -- 发布时间：2014/5/15 13:20:00 -- 以下是引用jspta在2014-5-15 12:30:00的发言：
cmd.CommandText = "SELECT DISTINCT OrderNumber from {Purchase_Orders} where OrderNumber = \'" & e.Row("OrderNumber") & "\'and (select sum(Quantity) from {IQC_Details} where Identify = {Purchase_Orders}.[_Identify]) - (select sum(StorageQty) from {IQC_Details} where Identify = {Purchase_Orders}.[_Identify]) > 0 And DATEDIFF(day,GETDATE(),{Purchase_Orders}.DeliveryDate) <= 3 And DATEDIFF(day,GETDATE(),{Purchase_Orders}.DeliveryDate) >= 0"
If cmd.ExecuteScalar IsNot Nothing Then
用一个全局表进行代替。把相关数据全部加载，然后引用这个表，这个效率应该还能接受
还是做一个辅助列吧 估计就可以了 |
| -- 作者：jspta -- 发布时间：2014/5/15 13:41:00 -- 加个辅助列肯定可以了，基本上也就没感觉 |
| -- 作者：jiskin -- 发布时间：2014/5/15 16:18:00 -- Select _Identify,OrderNumber,OrderDate,Supplier,InvoiceNumber,InvoiceDate,UserName,Status, (Select DISTINCT OrderNumber from Purchase_Orders b where (Select Status from Purchase_List where OrderNumber = b.OrderNumber) = \'采购中\' and b.Quantity - (select sum(Quantity) from IQC_Details where Identify = b._Identify) > 0 And DATEDIFF(day,GETDATE(),b.DeliveryDate) between 0 and 3) as 辅助 from Purchase_List a 这样做辅助列 辅助列里居然没数据，是不是哪里写错了啊 求帮忙看看
|
| -- 作者：有点甜 -- 发布时间：2014/5/15 16:27:00 -- 额，哪有这样写代码的？辅助列的值是不是只有空和非空两种？
Select _Identify,OrderNumber,OrderDate,Supplier,InvoiceNumber,InvoiceDate,UserName,Status,
((Select top 1 OrderNumber from Purchase_Orders b where (Select Status from Purchase_List where OrderNumber = b.OrderNumber) = \'采购中\'
and b.Quantity - (select sum(Quantity) from IQC_Details where Identify = b._Identify) > 0
And DATEDIFF(day,GETDATE(),b.DeliveryDate) between 0 and 3) is null) as 辅助
from Purchase_List a
|
| -- 作者：jiskin -- 发布时间：2014/5/15 16:44:00 -- 以下是引用有点甜在2014-5-15 16:27:00的发言：
额，哪有这样写代码的？辅助列的值是不是只有空和非空两种？
Select _Identify,OrderNumber,OrderDate,Supplier,InvoiceNumber,InvoiceDate,UserName,Status,
((Select top 1 OrderNumber from Purchase_Orders b where (Select Status from Purchase_List where OrderNumber = b.OrderNumber) = \'采购中\'
and b.Quantity - (select sum(Quantity) from IQC_Details where Identify = b._Identify) > 0
And DATEDIFF(day,GETDATE(),b.DeliveryDate) between 0 and 3) is null) as 辅助
from Purchase_List a
甜总， 辅助列到时候就为了判断空和非空 加了 is null 会报错，关键字 is 附近有语法错误，去掉is null 后辅助列还是没有数据
|