Query2Str :=' if object_id(''tempdb..#temp'') is not null DROP TABLE #temp '+
' select identity(int,1,1) as 序列, aa.*,cast(100*aa.销售金额/bb.合计销售金额 as varchar)+''%'' as 比例 '+
' into #temp from '+
' (SELECT d.ID, a.销售日期,(select 姓名 from 用户 d where a.制表= d.ID ) as 销售代表, '+
' (select b.订货单号 from 订货单 b where a.销售ID=b.销售ID) as 订货单号, a.销售单号, '+
' a.相关单据号 as 撤消单号, '+
' (case when 状态=''销售'' then cast(金额 as varchar) when 状态=''撤消'' then ''-----'' else null end) as 销售金额 '+
' FROM 销售单 a LEFT OUTER JOIN '+
' 用户 b ON a.制表 = b.ID LEFT OUTER JOIN '+
' 用户与组 c ON c.用户号 = b.ID LEFT OUTER JOIN '+
' 用户组 d ON d.ID = c.组号 '+
' where (a.状态=''销售'' or a.状态=''撤消'') and 销售日期 between '''+
DateTimetostr(DateTimePicker1.Date - 1) +''' and ''' + DateTimetostr(DateTimepicker2.Date)+ '''and d.ID= '''+
Datamodule2.ADOQuery1.FieldByName('ID').AsString+''') aa,'+
' (select sum(case 状态 when ''销售'' then 金额 else null end) as 合计销售金额 from 销售单'+
' where 销售日期 between '''+
DateTimetostr(DateTimePicker1.Date - 1) +''' and ''' + DateTimetostr(DateTimepicker2.Date)+ '''and 制表= '''+
Datamodule2.ADOQuery1.FieldByName('ID').AsString+''') bb'+
' select * from #temp ';
SELECT d.ID as 组ID, a.销售日期,(select 姓名 from 用户 d where a.制表= d.ID ) as 销售代表,
(select b.订货单号 from 订货单 b where a.销售ID=b.销售ID) as 订货单号, a.销售单号,
a.相关单据号 as 撤消单号,
(case 状态 when '撤消' then '----' else cast(金额 as varchar) end) as 销售金额
FROM 销售单 a LEFT OUTER JOIN
用户 b ON a.制表 = b.ID LEFT OUTER JOIN
用户与组 c ON c.用户号 = b.ID LEFT OUTER JOIN
用户组 d ON d.ID = c.组号
where (a.状态='销售' or a.状态='撤消')
SELECT d.ID as 组ID, a.销售日期,(select 姓名 from 用户 d where a.制表= d.ID ) as 销售代表,
(select b.订货单号 from 订货单 b where a.销售ID=b.销售ID) as 订货单号, a.销售单号,
a.相关单据号 as 撤消单号,
(case 状态 when '撤消' then '----' else 金额 end) as 销售金额
FROM 销售单 a LEFT OUTER JOIN
用户 b ON a.制表 = b.ID LEFT OUTER JOIN
用户与组 c ON c.用户号 = b.ID LEFT OUTER JOIN
用户组 d ON d.ID = c.组号
where (a.状态='销售' or a.状态='撤消')