delphi中union的问题

[新自由呼吸]

with data1.ADOpublic do
begin
close;
sql.Clear;
sql.Add('select c.xm as 姓名,c.xzh as 工资号,c.cccc as 出乘车次,c.fccc as 返乘车次,count(c.fccc) as 合计趟数 from T_renshibiao as r join T_cwbg AS c on r.bh=c.xzh where r.ssbm =:a');
parameters.ParamByName('a').Value:=trim(combobox1.Text);
sql.Add('and ');
sql.add('(fssj between :a1');
parameters.ParamByName('a1').Value:=strtodatetime(formatdatetime('yyyy-mm-dd',dateTimePicker1.datetime));
sql.Add(' and :a2)');
parameters.ParamByName('a2').Value:=strtodatetime(formatdatetime('yyyy-mm-dd',dateTimePicker2.datetime));
mydate:= strtodatetime(formatdatetime('yyyy-mm-dd',datetimepicker2.Datetime));
sql.Add('group by c.xm,c.xzh,c.cccc,c.fccc,r.bh,r.ssbm');
sql.Add('union all select c.xm as 姓名,c.xzh as 工资号,c.cccc as 出乘车次,c.fccc as 返乘车次,count(c.fccc) as 加班趟数 from T_renshibiao as r join T_cwbg AS c on r.bh=c.xzh where r.ssbm =:b');
parameters.ParamByName('b').Value:=trim(combobox1.Text);
sql.Add('and ');

sql.add('(fssj between :b1');
parameters.ParamByName('b1').Value:=strtodatetime(formatdatetime('yyyy-mm-dd',dateTimePicker1.datetime));
sql.Add(' and :b2)');
parameters.ParamByName('b2').Value:=strtodatetime(formatdatetime('yyyy-mm-dd',dateTimePicker2.datetime));
sql.Add('and c.jianban=''是'' group by c.xm,c.xzh,c.cccc,c.fccc,r.bh,r.ssbm'); }
open;
end;


While Not(data1.ADOpublic.Eof) do
begin
for i:=1 to data1.ADOpublic.FieldCount do
begin
temp_sc:= data1.ADOpublic.FieldByName('加班趟数').Value ;
end;
data1.ADOpublic.Next;
end;

提示说找不到ADOpublic的加班趟数，但假如用 union前的却可以，如data1.ADOpublic.FieldByName('合计趟数').Value ，union后的不行。

[新自由呼吸]

谢谢小V、DBMH！我又改了下
c.xm as 姓名,c.xzh as 工资号,c.cccc as 出乘车次,c.fccc as 返乘车次,count(c.fccc) as 合计趟数,isNull(null,0) as 加班趟数
c.xm as 姓名,c.xzh as 工资号,c.cccc as 出乘车次,c.fccc as 返乘车次,null,count(c.fccc) as 加班趟数

比较合理了，合计等功能好像还是Ehlib比较好，用SQL写挺麻烦。

[varick_zhong]

union 前一个select 语句中的列要与后一个select 语句中的列一致的，你这里是后面有列"加班趟数"前面却没有

[新自由呼吸]

前面的是c.xm as 姓名,c.xzh as 工资号,c.cccc as 出乘车次,c.fccc as 返乘车次,count(c.fccc) as 合计趟数，后面的是c.xm as 姓名,c.xzh as 工资号,c.cccc as 出乘车次,c.fccc as 返乘车次,count(c.fccc) as 加班趟数，对应的呀~

[bdmh]

union后，会以前面的字段为名，应该是 合计趟数

[新自由呼吸]

那union后的数据，我如何取出？

[varick_zhong]

前面的是c.xm as 姓名,c.xzh as 工资号,c.cccc as 出乘车次,c.fccc as 返乘车次,count(c.fccc) as 趟数，'合计' as 类型
后面的是c.xm as 姓名,c.xzh as 工资号,c.cccc as 出乘车次,c.fccc as 返乘车次,count(c.fccc) as 趟数，'加班' as 类型

这样变通一下是不是可以

[kaikai_kk]

[Quote=引用 4 楼 enio 的回复:]
那union后的数据，我如何取出？
[/Quote]

多加一常数列,union后面可以不指定列名，简化一点:
...,c.cccc as 出乘车次,c.fccc as 返乘车次, count(c.fccc) as 合计趟数,0 as 加班趟数
union ...,c.cccc,c.fccc, 0,count(c.fccc)

[zlb_chen]

前面的是c.xm as 姓名,c.xzh as 工资号,c.cccc as 出乘车次,c.fccc as 返乘车次,count(c.fccc) as 合计趟数，0 as 加班趟数
后面的是c.xm as 姓名,c.xzh as 工资号,c.cccc as 出乘车次,c.fccc as 返乘车次,0 as 合计趟数, count(c.fccc) as 加班趟数，

对应的呀~