Foxtable(狐表) → 用户栏目 → 专家坐堂 → ［求助］一个SQL按钮代码


  共有23893人关注过本帖树形打印复制链接

主题：［求助］一个SQL按钮代码
帅哥哟，离线，有人找我吗？
migold
 小大  21楼 | QQ | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级：一尾狐 帖子：447 积分：4572 威望：0 精华：0 注册：2009/1/11 11:00:00 		  发帖心情 Post By：2009/7/27 17:59:00 [只看该作者]

我的SQL数据库里面已有220个存储过程，有用于查询，有用于录入等，
就是不会使用狐表进行调用
 回到顶部
帅哥哟，离线，有人找我吗？
migold
 小大  22楼 | QQ | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级：一尾狐 帖子：447 积分：4572 威望：0 精华：0 注册：2009/1/11 11:00:00 		  发帖心情 Post By：2009/7/27 18:03:00 [只看该作者]

请各位老大，给个使用存储过程的帮助文件，我学习一下
谢谢

 回到顶部
帅哥哟，离线，有人找我吗？
migold
 小大  23楼 | QQ | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级：一尾狐 帖子：447 积分：4572 威望：0 精华：0 注册：2009/1/11 11:00:00 		  发帖心情 Post By：2009/7/28 15:02:00 [只看该作者]

读取TXT文件内容：




点击按钮执行SQL，如下，
弹出错误对话框:System.Data.OleDb.OleDbException:超时已过期 


直接在SQL查询分析器，执行：（所影响的行数为 6217 行）花费0:00:37时间

以上问题，请求老大解决一下
 回到顶部
帅哥哟，离线，有人找我吗？
migold
 小大  24楼 | QQ | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级：一尾狐 帖子：447 积分：4572 威望：0 精华：0 注册：2009/1/11 11:00:00 		  发帖心情 Post By：2009/7/28 15:04:00 [只看该作者]

TXT内容：
Insert into MiMonthTotal (DataBase_Name,mode,Stor_ID,Stor_Code,Stor_Name,Unit_ID,Unit_Code,Unit_Name,Emp_ID,Emp_Code,Emp_Name,Prod_ProdType,Prod_Back1,BillDate,BillType,Prod_Number1,Prod_Number2,Prod_Number3,DisMoney1,DisMoney2,DisMoney3,CostMoney1,CostMoney2,CostMoney3,ProfitMoney)
select 'DB_Name' AS 'DataBase_Name','1' AS 'mode',
 f.s_ID AS 'Stor_ID',f.u_Code AS 'Stor_Code',f.u_Name AS 'Stor_Name',
 '0' AS 'Unit_ID','0' AS 'Unit_Code','0' AS 'Unit_Name',
 '0' AS 'Emp_ID','0' AS 'Emp_Code','0' AS 'Emp_Name',
 e.ProdType AS 'Prod_ProdType',
 '0' AS 'Prod_Back1',
 left(b.billdate,7) AS 'BillDate',left(d.billname,2) AS 'BillType',
 sum(case when billtype2 in ('4','16') then 0 else isnull(Prod_Number,0) end) AS 'Prod_Number1',
 sum(case when billtype2 in ('4','16') then -isnull(Prod_Number,0) else 0 end) AS 'Prod_Number2',
 sum(case when billtype2 in ('4','16') then -isnull(Prod_Number,0) else isnull(Prod_Number,0) end) AS 'Prod_Number3',
 sum(case when billtype2 in ('4','16') then 0 else isnull(DisMoney,0) end) AS 'DisMoney1',
 sum(case when billtype2 in ('4','16') then -isnull(DisMoney,0) else 0 end) AS 'DisMoney2',
 sum(case when billtype2 in ('4','16') then -isnull(DisMoney,0) else isnull(DisMoney,0) end) AS 'DisMoney3',
 sum(case when billtype2 in ('4','16') then 0 else isnull(CostMoney,0) end) AS 'CostMoney1',
 sum(case when billtype2 in ('4','16') then -isnull(CostMoney,0) else 0 end) AS 'CostMoney2',
 sum(case when billtype2 in ('4','16') then -isnull(CostMoney,0) else isnull(CostMoney,0) end) AS 'CostMoney3',
 sum(case when billtype2 in ('4','16') then -(isnull(dismoney,0)-isnull(costmoney,0)) else (isnull(dismoney,0)-isnull(costmoney,0)) end) AS 'ProfitMoney'
from [DB_Name].[dbo].[ListSale] A
left join (select autoid,unit_id,billtype,billdate,s_Syb,emp_id from [DB_Name].[dbo].[masterbill]) b on a.bill_id=b.autoid
left join (select s_id,u_Code,u_Name from [DB_Name].[dbo].[units]) c on b.unit_id=c.s_id
left join (select billtype billtype2,billname from [DB_Name].[dbo].[billtype]) d on b.billtype=d.billtype2
left join (select s_id,u_Code,u_Name,ProdType,Back1,Back2,Back3 from [DB_Name].[dbo].[product]) e on a.prod_id=e.s_id
left join (select s_ID,u_Code,u_Name from [DB_Name].[dbo].[StorHouse]) f on a.Stor_ID = f.s_ID
left join (select s_ID,u_Name from [DB_Name].[dbo].[commoninfo] where s_class=14)comib on e.Back1 = comib.s_ID
left join (select s_ID,u_Name from [DB_Name].[dbo].[commoninfo] where s_class=15)comic on e.Back2 = comic.s_ID
left join (select s_ID,u_Name from [DB_Name].[dbo].[commoninfo] where s_class=16)comid on e.Back3 = comid.s_ID
left join (select s_id,u_Code,u_name from [DB_Name].[dbo].[employee]) k on b.emp_id = k.s_id
where b.s_Syb = 0 and d.billtype2 in ('15','16','1','4') And left(b.billdate,7) = 'XXXX-XX'
GROUP BY f.s_ID,f.u_Code,f.u_Name,e.ProdType,left(b.billdate,7),left(d.billname,2)
union all
select 'DB_Name' AS 'DataBase_Name','2' AS 'mode',
 f.s_ID AS 'Stor_ID',f.u_Code AS 'Stor_Code',f.u_Name AS 'Stor_Name',
 '0' AS 'Unit_ID','0' AS 'Unit_Code','0' AS 'Unit_Name',
 '0' AS 'Emp_ID','0' AS 'Emp_Code','0' AS 'Emp_Name',
 '0' AS 'Prod_ProdType',
 comib.u_Name AS 'Prod_Back1',
 left(b.billdate,7) AS 'BillDate',left(d.billname,2) AS 'BillType',
 sum(case when billtype2 in ('4','16') then 0 else isnull(Prod_Number,0) end) AS 'Prod_Number1',
 sum(case when billtype2 in ('4','16') then -isnull(Prod_Number,0) else 0 end) AS 'Prod_Number2',
 sum(case when billtype2 in ('4','16') then -isnull(Prod_Number,0) else isnull(Prod_Number,0) end) AS 'Prod_Number3',
 sum(case when billtype2 in ('4','16') then 0 else isnull(DisMoney,0) end) AS 'DisMoney1',
 sum(case when billtype2 in ('4','16') then -isnull(DisMoney,0) else 0 end) AS 'DisMoney2',
 sum(case when billtype2 in ('4','16') then -isnull(DisMoney,0) else isnull(DisMoney,0) end) AS 'DisMoney3',
 sum(case when billtype2 in ('4','16') then 0 else isnull(CostMoney,0) end) AS 'CostMoney1',
 sum(case when billtype2 in ('4','16') then -isnull(CostMoney,0) else 0 end) AS 'CostMoney2',
 sum(case when billtype2 in ('4','16') then -isnull(CostMoney,0) else isnull(CostMoney,0) end) AS 'CostMoney3',
 sum(case when billtype2 in ('4','16') then -(isnull(dismoney,0)-isnull(costmoney,0)) else (isnull(dismoney,0)-isnull(costmoney,0)) end) AS 'ProfitMoney'
from [DB_Name].[dbo].[ListSale] A
left join (select autoid,unit_id,billtype,billdate,s_Syb,emp_id from [DB_Name].[dbo].[masterbill]) b on a.bill_id=b.autoid
left join (select s_id,u_Code,u_Name from [DB_Name].[dbo].[units]) c on b.unit_id=c.s_id
left join (select billtype billtype2,billname from [DB_Name].[dbo].[billtype]) d on b.billtype=d.billtype2
left join (select s_id,u_Code,u_Name,ProdType,Back1,Back2,Back3 from [DB_Name].[dbo].[product]) e on a.prod_id=e.s_id
left join (select s_ID,u_Code,u_Name from [DB_Name].[dbo].[StorHouse]) f on a.Stor_ID = f.s_ID
left join (select s_ID,u_Name from [DB_Name].[dbo].[commoninfo] where s_class=14)comib on e.Back1 = comib.s_ID
left join (select s_ID,u_Name from [DB_Name].[dbo].[commoninfo] where s_class=15)comic on e.Back2 = comic.s_ID
left join (select s_ID,u_Name from [DB_Name].[dbo].[commoninfo] where s_class=16)comid on e.Back3 = comid.s_ID
left join (select s_id,u_Code,u_name from [DB_Name].[dbo].[employee]) k on b.emp_id = k.s_id
where b.s_Syb = 0 and d.billtype2 in ('15','16','1','4') And left(b.billdate,7) = 'XXXX-XX'
GROUP BY f.s_ID,f.u_Code,f.u_Name,comib.u_Name,left(b.billdate,7),left(d.billname,2)
union all
select 'DB_Name' AS 'DataBase_Name','3' AS 'mode',
 f.s_ID AS 'Stor_ID',f.u_Code AS 'Stor_Code',f.u_Name AS 'Stor_Name',
 '0' AS 'Unit_ID','0' AS 'Unit_Code','0' AS 'Unit_Name',
 k.s_id AS 'Emp_ID',k.u_Code AS 'Emp_Code',k.u_name AS 'Emp_Name',
 e.ProdType AS 'Prod_ProdType',
 '0' AS 'Prod_Back1',
 left(b.billdate,7) AS 'BillDate',left(d.billname,2) AS 'BillType',
 sum(case when billtype2 in ('4','16') then 0 else isnull(Prod_Number,0) end) AS 'Prod_Number1',
 sum(case when billtype2 in ('4','16') then -isnull(Prod_Number,0) else 0 end) AS 'Prod_Number2',
 sum(case when billtype2 in ('4','16') then -isnull(Prod_Number,0) else isnull(Prod_Number,0) end) AS 'Prod_Number3',
 sum(case when billtype2 in ('4','16') then 0 else isnull(DisMoney,0) end) AS 'DisMoney1',
 sum(case when billtype2 in ('4','16') then -isnull(DisMoney,0) else 0 end) AS 'DisMoney2',
 sum(case when billtype2 in ('4','16') then -isnull(DisMoney,0) else isnull(DisMoney,0) end) AS 'DisMoney3',
 sum(case when billtype2 in ('4','16') then 0 else isnull(CostMoney,0) end) AS 'CostMoney1',
 sum(case when billtype2 in ('4','16') then -isnull(CostMoney,0) else 0 end) AS 'CostMoney2',
 sum(case when billtype2 in ('4','16') then -isnull(CostMoney,0) else isnull(CostMoney,0) end) AS 'CostMoney3',
 sum(case when billtype2 in ('4','16') then -(isnull(dismoney,0)-isnull(costmoney,0)) else (isnull(dismoney,0)-isnull(costmoney,0)) end) AS 'ProfitMoney'
from [DB_Name].[dbo].[ListSale] A
left join (select autoid,unit_id,billtype,billdate,s_Syb,emp_id from [DB_Name].[dbo].[masterbill]) b on a.bill_id=b.autoid
left join (select s_id,u_Code,u_Name from [DB_Name].[dbo].[units]) c on b.unit_id=c.s_id
left join (select billtype billtype2,billname from [DB_Name].[dbo].[billtype]) d on b.billtype=d.billtype2
left join (select s_id,u_Code,u_Name,ProdType,Back1,Back2,Back3 from [DB_Name].[dbo].[product]) e on a.prod_id=e.s_id
left join (select s_ID,u_Code,u_Name from [DB_Name].[dbo].[StorHouse]) f on a.Stor_ID = f.s_ID
left join (select s_ID,u_Name from [DB_Name].[dbo].[commoninfo] where s_class=14)comib on e.Back1 = comib.s_ID
left join (select s_ID,u_Name from [DB_Name].[dbo].[commoninfo] where s_class=15)comic on e.Back2 = comic.s_ID
left join (select s_ID,u_Name from [DB_Name].[dbo].[commoninfo] where s_class=16)comid on e.Back3 = comid.s_ID
left join (select s_id,u_Code,u_name from [DB_Name].[dbo].[employee]) k on b.emp_id = k.s_id
where b.s_Syb = 0 and d.billtype2 in ('15','16','1','4') And left(b.billdate,7) = 'XXXX-XX'
GROUP BY f.s_ID,f.u_Code,f.u_Name,k.s_id,k.u_Code,k.u_name,e.ProdType,left(b.billdate,7),left(d.billname,2)
union all
select 'DB_Name' AS 'DataBase_Name','4' AS 'mode',
 f.s_ID AS 'Stor_ID',f.u_Code AS 'Stor_Code',f.u_Name AS 'Stor_Name',
 '0' AS 'Unit_ID','0' AS 'Unit_Code','0' AS 'Unit_Name',
 k.s_id AS 'Emp_ID',k.u_Code AS 'Emp_Code',k.u_name AS 'Emp_Name',
 '0' AS 'Prod_ProdType',
 comib.u_Name AS 'Prod_Back1',
 left(b.billdate,7) AS 'BillDate',left(d.billname,2) AS 'BillType',
 sum(case when billtype2 in ('4','16') then 0 else isnull(Prod_Number,0) end) AS 'Prod_Number1',
 sum(case when billtype2 in ('4','16') then -isnull(Prod_Number,0) else 0 end) AS 'Prod_Number2',
 sum(case when billtype2 in ('4','16') then -isnull(Prod_Number,0) else isnull(Prod_Number,0) end) AS 'Prod_Number3',
 sum(case when billtype2 in ('4','16') then 0 else isnull(DisMoney,0) end) AS 'DisMoney1',
 sum(case when billtype2 in ('4','16') then -isnull(DisMoney,0) else 0 end) AS 'DisMoney2',
 sum(case when billtype2 in ('4','16') then -isnull(DisMoney,0) else isnull(DisMoney,0) end) AS 'DisMoney3',
 sum(case when billtype2 in ('4','16') then 0 else isnull(CostMoney,0) end) AS 'CostMoney1',
 sum(case when billtype2 in ('4','16') then -isnull(CostMoney,0) else 0 end) AS 'CostMoney2',
 sum(case when billtype2 in ('4','16') then -isnull(CostMoney,0) else isnull(CostMoney,0) end) AS 'CostMoney3',
 sum(case when billtype2 in ('4','16') then -(isnull(dismoney,0)-isnull(costmoney,0)) else (isnull(dismoney,0)-isnull(costmoney,0)) end) AS 'ProfitMoney'
from [DB_Name].[dbo].[ListSale] A
left join (select autoid,unit_id,billtype,billdate,s_Syb,emp_id from [DB_Name].[dbo].[masterbill]) b on a.bill_id=b.autoid
left join (select s_id,u_Code,u_Name from [DB_Name].[dbo].[units]) c on b.unit_id=c.s_id
left join (select billtype billtype2,billname from [DB_Name].[dbo].[billtype]) d on b.billtype=d.billtype2
left join (select s_id,u_Code,u_Name,ProdType,Back1,Back2,Back3 from [DB_Name].[dbo].[product]) e on a.prod_id=e.s_id
left join (select s_ID,u_Code,u_Name from [DB_Name].[dbo].[StorHouse]) f on a.Stor_ID = f.s_ID
left join (select s_ID,u_Name from [DB_Name].[dbo].[commoninfo] where s_class=14)comib on e.Back1 = comib.s_ID
left join (select s_ID,u_Name from [DB_Name].[dbo].[commoninfo] where s_class=15)comic on e.Back2 = comic.s_ID
left join (select s_ID,u_Name from [DB_Name].[dbo].[commoninfo] where s_class=16)comid on e.Back3 = comid.s_ID
left join (select s_id,u_Code,u_name from [DB_Name].[dbo].[employee]) k on b.emp_id = k.s_id
where b.s_Syb = 0 and d.billtype2 in ('15','16','1','4') And left(b.billdate,7) = 'XXXX-XX'
GROUP BY f.s_ID,f.u_Code,f.u_Name,k.s_id,k.u_Code,k.u_name,comib.u_Name,left(b.billdate,7),left(d.billname,2)
union all
select 'DB_Name' AS 'DataBase_Name','5' AS 'mode',
 '0' AS 'Stor_ID','0' AS 'Stor_Code','0' AS 'Stor_Name',
 c.s_id AS 'Unit_ID',c.u_Code AS 'Unit_Code',c.u_Name AS 'Unit_Name',
 '0' AS 'Emp_ID','0' AS 'Emp_Code','0' AS 'Emp_Name',
 e.ProdType AS 'Prod_ProdType',
 '0' AS 'Prod_Back1',
 left(b.billdate,7) AS 'BillDate',left(d.billname,2) AS 'BillType',
 sum(case when billtype2 in ('4','16') then 0 else isnull(Prod_Number,0) end) AS 'Prod_Number1',
 sum(case when billtype2 in ('4','16') then -isnull(Prod_Number,0) else 0 end) AS 'Prod_Number2',
 sum(case when billtype2 in ('4','16') then -isnull(Prod_Number,0) else isnull(Prod_Number,0) end) AS 'Prod_Number3',
 sum(case when billtype2 in ('4','16') then 0 else isnull(DisMoney,0) end) AS 'DisMoney1',
 sum(case when billtype2 in ('4','16') then -isnull(DisMoney,0) else 0 end) AS 'DisMoney2',
 sum(case when billtype2 in ('4','16') then -isnull(DisMoney,0) else isnull(DisMoney,0) end) AS 'DisMoney3',
 sum(case when billtype2 in ('4','16') then 0 else isnull(CostMoney,0) end) AS 'CostMoney1',
 sum(case when billtype2 in ('4','16') then -isnull(CostMoney,0) else 0 end) AS 'CostMoney2',
 sum(case when billtype2 in ('4','16') then -isnull(CostMoney,0) else isnull(CostMoney,0) end) AS 'CostMoney3',
 sum(case when billtype2 in ('4','16') then -(isnull(dismoney,0)-isnull(costmoney,0)) else (isnull(dismoney,0)-isnull(costmoney,0)) end) AS 'ProfitMoney'
from [DB_Name].[dbo].[ListSale] A
left join (select autoid,unit_id,billtype,billdate,s_Syb,emp_id from [DB_Name].[dbo].[masterbill]) b on a.bill_id=b.autoid
left join (select s_id,u_Code,u_Name from [DB_Name].[dbo].[units]) c on b.unit_id=c.s_id
left join (select billtype billtype2,billname from [DB_Name].[dbo].[billtype]) d on b.billtype=d.billtype2
left join (select s_id,u_Code,u_Name,ProdType,Back1,Back2,Back3 from [DB_Name].[dbo].[product]) e on a.prod_id=e.s_id
left join (select s_ID,u_Code,u_Name from [DB_Name].[dbo].[StorHouse]) f on a.Stor_ID = f.s_ID
left join (select s_ID,u_Name from [DB_Name].[dbo].[commoninfo] where s_class=14)comib on e.Back1 = comib.s_ID
left join (select s_ID,u_Name from [DB_Name].[dbo].[commoninfo] where s_class=15)comic on e.Back2 = comic.s_ID
left join (select s_ID,u_Name from [DB_Name].[dbo].[commoninfo] where s_class=16)comid on e.Back3 = comid.s_ID
left join (select s_id,u_Code,u_name from [DB_Name].[dbo].[employee]) k on b.emp_id = k.s_id
where b.s_Syb = 0 and d.billtype2 in ('15','16','1','4') And left(b.billdate,7) = 'XXXX-XX'
GROUP BY c.s_id,c.u_Code,c.u_Name,e.ProdType,left(b.billdate,7),left(d.billname,2)
union all
......
 回到顶部
帅哥哟，离线，有人找我吗？
migold
 小大  25楼 | QQ | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级：一尾狐 帖子：447 积分：4572 威望：0 精华：0 注册：2009/1/11 11:00:00 		  发帖心情 Post By：2009/7/28 15:04:00 [只看该作者]

接上

select 'DB_Name' AS 'DataBase_Name','6' AS 'mode',
 '0' AS 'Stor_ID','0' AS 'Stor_Code','0' AS 'Stor_Name',
 c.s_id AS 'Unit_ID',c.u_Code AS 'Unit_Code',c.u_Name AS 'Unit_Name',
 '0' AS 'Emp_ID','0' AS 'Emp_Code','0' AS 'Emp_Name',
 '0' AS 'Prod_ProdType',
 comib.u_Name AS 'Prod_Back1',
 left(b.billdate,7) AS 'BillDate',left(d.billname,2) AS 'BillType',
 sum(case when billtype2 in ('4','16') then 0 else isnull(Prod_Number,0) end) AS 'Prod_Number1',
 sum(case when billtype2 in ('4','16') then -isnull(Prod_Number,0) else 0 end) AS 'Prod_Number2',
 sum(case when billtype2 in ('4','16') then -isnull(Prod_Number,0) else isnull(Prod_Number,0) end) AS 'Prod_Number3',
 sum(case when billtype2 in ('4','16') then 0 else isnull(DisMoney,0) end) AS 'DisMoney1',
 sum(case when billtype2 in ('4','16') then -isnull(DisMoney,0) else 0 end) AS 'DisMoney2',
 sum(case when billtype2 in ('4','16') then -isnull(DisMoney,0) else isnull(DisMoney,0) end) AS 'DisMoney3',
 sum(case when billtype2 in ('4','16') then 0 else isnull(CostMoney,0) end) AS 'CostMoney1',
 sum(case when billtype2 in ('4','16') then -isnull(CostMoney,0) else 0 end) AS 'CostMoney2',
 sum(case when billtype2 in ('4','16') then -isnull(CostMoney,0) else isnull(CostMoney,0) end) AS 'CostMoney3',
 sum(case when billtype2 in ('4','16') then -(isnull(dismoney,0)-isnull(costmoney,0)) else (isnull(dismoney,0)-isnull(costmoney,0)) end) AS 'ProfitMoney'
from [DB_Name].[dbo].[ListSale] A
left join (select autoid,unit_id,billtype,billdate,s_Syb,emp_id from [DB_Name].[dbo].[masterbill]) b on a.bill_id=b.autoid
left join (select s_id,u_Code,u_Name from [DB_Name].[dbo].[units]) c on b.unit_id=c.s_id
left join (select billtype billtype2,billname from [DB_Name].[dbo].[billtype]) d on b.billtype=d.billtype2
left join (select s_id,u_Code,u_Name,ProdType,Back1,Back2,Back3 from [DB_Name].[dbo].[product]) e on a.prod_id=e.s_id
left join (select s_ID,u_Code,u_Name from [DB_Name].[dbo].[StorHouse]) f on a.Stor_ID = f.s_ID
left join (select s_ID,u_Name from [DB_Name].[dbo].[commoninfo] where s_class=14)comib on e.Back1 = comib.s_ID
left join (select s_ID,u_Name from [DB_Name].[dbo].[commoninfo] where s_class=15)comic on e.Back2 = comic.s_ID
left join (select s_ID,u_Name from [DB_Name].[dbo].[commoninfo] where s_class=16)comid on e.Back3 = comid.s_ID
left join (select s_id,u_Code,u_name from [DB_Name].[dbo].[employee]) k on b.emp_id = k.s_id
where b.s_Syb = 0 and d.billtype2 in ('15','16','1','4') And left(b.billdate,7) = 'XXXX-XX'
GROUP BY c.s_id,c.u_Code,c.u_Name,comib.u_Name,left(b.billdate,7),left(d.billname,2)
union all
select 'DB_Name' AS 'DataBase_Name','7' AS 'mode',
 '0' AS 'Stor_ID','0' AS 'Stor_Code','0' AS 'Stor_Name',
 c.s_id AS 'Unit_ID',c.u_Code AS 'Unit_Code',c.u_Name AS 'Unit_Name',
 k.s_id AS 'Emp_ID',k.u_Code AS 'Emp_Code',k.u_name AS 'Emp_Name',
 '0' AS 'Prod_ProdType',
 '0' AS 'Prod_Back1',
 left(b.billdate,7) AS 'BillDate',left(d.billname,2) AS 'BillType',
 sum(case when billtype2 in ('4','16') then 0 else isnull(Prod_Number,0) end) AS 'Prod_Number1',
 sum(case when billtype2 in ('4','16') then -isnull(Prod_Number,0) else 0 end) AS 'Prod_Number2',
 sum(case when billtype2 in ('4','16') then -isnull(Prod_Number,0) else isnull(Prod_Number,0) end) AS 'Prod_Number3',
 sum(case when billtype2 in ('4','16') then 0 else isnull(DisMoney,0) end) AS 'DisMoney1',
 sum(case when billtype2 in ('4','16') then -isnull(DisMoney,0) else 0 end) AS 'DisMoney2',
 sum(case when billtype2 in ('4','16') then -isnull(DisMoney,0) else isnull(DisMoney,0) end) AS 'DisMoney3',
 sum(case when billtype2 in ('4','16') then 0 else isnull(CostMoney,0) end) AS 'CostMoney1',
 sum(case when billtype2 in ('4','16') then -isnull(CostMoney,0) else 0 end) AS 'CostMoney2',
 sum(case when billtype2 in ('4','16') then -isnull(CostMoney,0) else isnull(CostMoney,0) end) AS 'CostMoney3',
 sum(case when billtype2 in ('4','16') then -(isnull(dismoney,0)-isnull(costmoney,0)) else (isnull(dismoney,0)-isnull(costmoney,0)) end) AS 'ProfitMoney'
from [DB_Name].[dbo].[ListSale] A
left join (select autoid,unit_id,billtype,billdate,s_Syb,emp_id from [DB_Name].[dbo].[masterbill]) b on a.bill_id=b.autoid
left join (select s_id,u_Code,u_Name from [DB_Name].[dbo].[units]) c on b.unit_id=c.s_id
left join (select billtype billtype2,billname from [DB_Name].[dbo].[billtype]) d on b.billtype=d.billtype2
left join (select s_id,u_Code,u_Name,ProdType,Back1,Back2,Back3 from [DB_Name].[dbo].[product]) e on a.prod_id=e.s_id
left join (select s_ID,u_Code,u_Name from [DB_Name].[dbo].[StorHouse]) f on a.Stor_ID = f.s_ID
left join (select s_ID,u_Name from [DB_Name].[dbo].[commoninfo] where s_class=14)comib on e.Back1 = comib.s_ID
left join (select s_ID,u_Name from [DB_Name].[dbo].[commoninfo] where s_class=15)comic on e.Back2 = comic.s_ID
left join (select s_ID,u_Name from [DB_Name].[dbo].[commoninfo] where s_class=16)comid on e.Back3 = comid.s_ID
left join (select s_id,u_Code,u_name from [DB_Name].[dbo].[employee]) k on b.emp_id = k.s_id
where b.s_Syb = 0 and d.billtype2 in ('15','16','1','4') And left(b.billdate,7) = 'XXXX-XX'
GROUP BY c.s_id,c.u_Code,c.u_Name,k.s_id,k.u_Code,k.u_name,left(b.billdate,7),left(d.billname,2)
union all
select 'DB_Name' AS 'DataBase_Name','8' AS 'mode',
 f.s_ID AS 'Stor_ID',f.u_Code AS 'Stor_Code',f.u_Name AS 'Stor_Name',
 c.s_id AS 'Unit_ID',c.u_Code AS 'Unit_Code',c.u_Name AS 'Unit_Name',
 '0' AS 'Emp_ID','0' AS 'Emp_Code','0' AS 'Emp_Name',
 '0' AS 'Prod_ProdType',
 '0' AS 'Prod_Back1',
 left(b.billdate,7) AS 'BillDate',left(d.billname,2) AS 'BillType',
 sum(case when billtype2 in ('4','16') then 0 else isnull(Prod_Number,0) end) AS 'Prod_Number1',
 sum(case when billtype2 in ('4','16') then -isnull(Prod_Number,0) else 0 end) AS 'Prod_Number2',
 sum(case when billtype2 in ('4','16') then -isnull(Prod_Number,0) else isnull(Prod_Number,0) end) AS 'Prod_Number3',
 sum(case when billtype2 in ('4','16') then 0 else isnull(DisMoney,0) end) AS 'DisMoney1',
 sum(case when billtype2 in ('4','16') then -isnull(DisMoney,0) else 0 end) AS 'DisMoney2',
 sum(case when billtype2 in ('4','16') then -isnull(DisMoney,0) else isnull(DisMoney,0) end) AS 'DisMoney3',
 sum(case when billtype2 in ('4','16') then 0 else isnull(CostMoney,0) end) AS 'CostMoney1',
 sum(case when billtype2 in ('4','16') then -isnull(CostMoney,0) else 0 end) AS 'CostMoney2',
 sum(case when billtype2 in ('4','16') then -isnull(CostMoney,0) else isnull(CostMoney,0) end) AS 'CostMoney3',
 sum(case when billtype2 in ('4','16') then -(isnull(dismoney,0)-isnull(costmoney,0)) else (isnull(dismoney,0)-isnull(costmoney,0)) end) AS 'ProfitMoney'
from [DB_Name].[dbo].[ListSale] A
left join (select autoid,unit_id,billtype,billdate,s_Syb,emp_id from [DB_Name].[dbo].[masterbill]) b on a.bill_id=b.autoid
left join (select s_id,u_Code,u_Name from [DB_Name].[dbo].[units]) c on b.unit_id=c.s_id
left join (select billtype billtype2,billname from [DB_Name].[dbo].[billtype]) d on b.billtype=d.billtype2
left join (select s_id,u_Code,u_Name,ProdType,Back1,Back2,Back3 from [DB_Name].[dbo].[product]) e on a.prod_id=e.s_id
left join (select s_ID,u_Code,u_Name from [DB_Name].[dbo].[StorHouse]) f on a.Stor_ID = f.s_ID
left join (select s_ID,u_Name from [DB_Name].[dbo].[commoninfo] where s_class=14)comib on e.Back1 = comib.s_ID
left join (select s_ID,u_Name from [DB_Name].[dbo].[commoninfo] where s_class=15)comic on e.Back2 = comic.s_ID
left join (select s_ID,u_Name from [DB_Name].[dbo].[commoninfo] where s_class=16)comid on e.Back3 = comid.s_ID
left join (select s_id,u_Code,u_name from [DB_Name].[dbo].[employee]) k on b.emp_id = k.s_id
where b.s_Syb = 0 and d.billtype2 in ('15','16','1','4') And left(b.billdate,7) = 'XXXX-XX'
GROUP BY f.s_ID,f.u_Code,f.u_Name,c.s_id,c.u_Code,c.u_Name,left(b.billdate,7),left(d.billname,2)
union all
select 'DB_Name' AS 'DataBase_Name','9' AS 'mode',
 '0' AS 'Stor_ID','0' AS 'Stor_Code','0' AS 'Stor_Name',
 c.s_id AS 'Unit_ID',c.u_Code AS 'Unit_Code',c.u_Name AS 'Unit_Name',
 k.s_id AS 'Emp_ID',k.u_Code AS 'Emp_Code',k.u_name AS 'Emp_Name',
 e.ProdType AS 'Prod_ProdType',
 '0' AS 'Prod_Back1',
 left(b.billdate,7) AS 'BillDate',left(d.billname,2) AS 'BillType',
 sum(case when billtype2 in ('4','16') then 0 else isnull(Prod_Number,0) end) AS 'Prod_Number1',
 sum(case when billtype2 in ('4','16') then -isnull(Prod_Number,0) else 0 end) AS 'Prod_Number2',
 sum(case when billtype2 in ('4','16') then -isnull(Prod_Number,0) else isnull(Prod_Number,0) end) AS 'Prod_Number3',
 sum(case when billtype2 in ('4','16') then 0 else isnull(DisMoney,0) end) AS 'DisMoney1',
 sum(case when billtype2 in ('4','16') then -isnull(DisMoney,0) else 0 end) AS 'DisMoney2',
 sum(case when billtype2 in ('4','16') then -isnull(DisMoney,0) else isnull(DisMoney,0) end) AS 'DisMoney3',
 sum(case when billtype2 in ('4','16') then 0 else isnull(CostMoney,0) end) AS 'CostMoney1',
 sum(case when billtype2 in ('4','16') then -isnull(CostMoney,0) else 0 end) AS 'CostMoney2',
 sum(case when billtype2 in ('4','16') then -isnull(CostMoney,0) else isnull(CostMoney,0) end) AS 'CostMoney3',
 sum(case when billtype2 in ('4','16') then -(isnull(dismoney,0)-isnull(costmoney,0)) else (isnull(dismoney,0)-isnull(costmoney,0)) end) AS 'ProfitMoney'
from [DB_Name].[dbo].[ListSale] A
left join (select autoid,unit_id,billtype,billdate,s_Syb,emp_id from [DB_Name].[dbo].[masterbill]) b on a.bill_id=b.autoid
left join (select s_id,u_Code,u_Name from [DB_Name].[dbo].[units]) c on b.unit_id=c.s_id
left join (select billtype billtype2,billname from [DB_Name].[dbo].[billtype]) d on b.billtype=d.billtype2
left join (select s_id,u_Code,u_Name,ProdType,Back1,Back2,Back3 from [DB_Name].[dbo].[product]) e on a.prod_id=e.s_id
left join (select s_ID,u_Code,u_Name from [DB_Name].[dbo].[StorHouse]) f on a.Stor_ID = f.s_ID
left join (select s_ID,u_Name from [DB_Name].[dbo].[commoninfo] where s_class=14)comib on e.Back1 = comib.s_ID
left join (select s_ID,u_Name from [DB_Name].[dbo].[commoninfo] where s_class=15)comic on e.Back2 = comic.s_ID
left join (select s_ID,u_Name from [DB_Name].[dbo].[commoninfo] where s_class=16)comid on e.Back3 = comid.s_ID
left join (select s_id,u_Code,u_name from [DB_Name].[dbo].[employee]) k on b.emp_id = k.s_id
where b.s_Syb = 0 and d.billtype2 in ('15','16','1','4') And left(b.billdate,7) = 'XXXX-XX'
GROUP BY c.s_id,c.u_Code,c.u_Name,k.s_id,k.u_Code,k.u_name,e.ProdType,left(b.billdate,7),left(d.billname,2)
union all
select 'DB_Name' AS 'DataBase_Name','10' AS 'mode',
 '0' AS 'Stor_ID','0' AS 'Stor_Code','0' AS 'Stor_Name',
 c.s_id AS 'Unit_ID',c.u_Code AS 'Unit_Code',c.u_Name AS 'Unit_Name',
 k.s_id AS 'Emp_ID',k.u_Code AS 'Emp_Code',k.u_name AS 'Emp_Name',
 '0' AS 'Prod_ProdType',
 comib.u_Name AS 'Prod_Back1',
 left(b.billdate,7) AS 'BillDate',left(d.billname,2) AS 'BillType',
 sum(case when billtype2 in ('4','16') then 0 else isnull(Prod_Number,0) end) AS 'Prod_Number1',
 sum(case when billtype2 in ('4','16') then -isnull(Prod_Number,0) else 0 end) AS 'Prod_Number2',
 sum(case when billtype2 in ('4','16') then -isnull(Prod_Number,0) else isnull(Prod_Number,0) end) AS 'Prod_Number3',
 sum(case when billtype2 in ('4','16') then 0 else isnull(DisMoney,0) end) AS 'DisMoney1',
 sum(case when billtype2 in ('4','16') then -isnull(DisMoney,0) else 0 end) AS 'DisMoney2',
 sum(case when billtype2 in ('4','16') then -isnull(DisMoney,0) else isnull(DisMoney,0) end) AS 'DisMoney3',
 sum(case when billtype2 in ('4','16') then 0 else isnull(CostMoney,0) end) AS 'CostMoney1',
 sum(case when billtype2 in ('4','16') then -isnull(CostMoney,0) else 0 end) AS 'CostMoney2',
 sum(case when billtype2 in ('4','16') then -isnull(CostMoney,0) else isnull(CostMoney,0) end) AS 'CostMoney3',
 sum(case when billtype2 in ('4','16') then -(isnull(dismoney,0)-isnull(costmoney,0)) else (isnull(dismoney,0)-isnull(costmoney,0)) end) AS 'ProfitMoney'
from [DB_Name].[dbo].[ListSale] A
left join (select autoid,unit_id,billtype,billdate,s_Syb,emp_id from [DB_Name].[dbo].[masterbill]) b on a.bill_id=b.autoid
left join (select s_id,u_Code,u_Name from [DB_Name].[dbo].[units]) c on b.unit_id=c.s_id
left join (select billtype billtype2,billname from [DB_Name].[dbo].[billtype]) d on b.billtype=d.billtype2
left join (select s_id,u_Code,u_Name,ProdType,Back1,Back2,Back3 from [DB_Name].[dbo].[product]) e on a.prod_id=e.s_id
left join (select s_ID,u_Code,u_Name from [DB_Name].[dbo].[StorHouse]) f on a.Stor_ID = f.s_ID
left join (select s_ID,u_Name from [DB_Name].[dbo].[commoninfo] where s_class=14)comib on e.Back1 = comib.s_ID
left join (select s_ID,u_Name from [DB_Name].[dbo].[commoninfo] where s_class=15)comic on e.Back2 = comic.s_ID
left join (select s_ID,u_Name from [DB_Name].[dbo].[commoninfo] where s_class=16)comid on e.Back3 = comid.s_ID
left join (select s_id,u_Code,u_name from [DB_Name].[dbo].[employee]) k on b.emp_id = k.s_id
where b.s_Syb = 0 and d.billtype2 in ('15','16','1','4') And left(b.billdate,7) = 'XXXX-XX'
GROUP BY c.s_id,c.u_Code,c.u_Name,k.s_id,k.u_Code,k.u_name,comib.u_Name,left(b.billdate,7),left(d.billname,2)
完了
 回到顶部
帅哥哟，离线，有人找我吗？
狐狸爸爸
 小大  26楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级：管理员 帖子：47448 积分：251054 威望：0 精华：91 注册：2008/6/17 17:14:00 		  发帖心情 Post By：2009/7/28 15:15:00 [只看该作者]

你可以在foxtable的SQL命令执行窗口执行看看。
在设计阶段，利用文本文件读取的时候，最好在一个文本框中显示一个读取的内容，在另一个文本框显示替换的内容，然后将替换后的内容复制到SQL命令执行窗口或者SQL查询分析器执行一下。
这个帮不了你的忙，自己耐心搞定，查询分析器能执行，Foxtable应该也能执行。
 回到顶部
帅哥哟，离线，有人找我吗？
migold
 小大  27楼 | QQ | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级：一尾狐 帖子：447 积分：4572 威望：0 精华：0 注册：2009/1/11 11:00:00 		  发帖心情 Post By：2009/7/28 15:26:00 [只看该作者]

我试过了将TXT内容：
去掉：union all
替换：Insert into MiMonthTotal (DataBase_Name,mode,Stor_ID,Stor_Code,Stor_Name,Unit_ID,Unit_Code,Unit_Name,Emp_ID,Emp_Code,Emp_Name,Prod_ProdType,Prod_Back1,BillDate,BillType,Prod_Number1,Prod_Number2,Prod_Number3,DisMoney1,DisMoney2,DisMoney3,CostMoney1,CostMoney2,CostMoney3,ProfitMoney)

测试成功。没有问题了。谢谢你
 回到顶部
帅哥哟，离线，有人找我吗？
migold
 小大  28楼 | QQ | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级：一尾狐 帖子：447 积分：4572 威望：0 精华：0 注册：2009/1/11 11:00:00 		  发帖心情 Post By：2009/7/28 15:28:00 [只看该作者]

老大，指点我一下：
怎样使用存储过程方法，及调用变量
 回到顶部
帅哥哟，离线，有人找我吗？
migold
 小大  29楼 | QQ | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级：一尾狐 帖子：447 积分：4572 威望：0 精华：0 注册：2009/1/11 11:00:00 		  发帖心情 Post By：2009/7/28 15:35:00 [只看该作者]

如果使用存储过程方法，太重要了

我的SQL数据库里面已有220个存储过程，有用于查询，有用于录入等，
以前是使用VS2005写的进行调用

我不会使用狐表进行调用，如果改用TXT读取掉用，太浪费精力。
220个存储过程将近1年的修来修去过修好。
 回到顶部
帅哥哟，离线，有人找我吗？
狐狸爸爸
 小大  30楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级：管理员 帖子：47448 积分：251054 威望：0 精华：91 注册：2008/6/17 17:14:00 		  发帖心情 Post By：2009/7/28 16:03:00 [只看该作者]

foxtable可以直接执行存储过程的，你看看：

http://www.foxtable.com/dispbbs.asp?BoardID=2&ID=2772

 回到顶部
总数 31 上一页 1 2 3 4 下一页
RSS2.0 | Xhtml无图版 | Xslt无图版 Copyright © 2000 - 2018 foxtable.com Tel: 4000-810-820 粤ICP备11091905号
   Powered By Dvbbs Version 8.3.0
页面执行时间 0.04395 秒, 4 次数据查询