有这样的一段代码:
select 数量=count(*), 年份=2005 from jibenqingkuang where (xb='男' and datediff(year,cssj,'2005-1-1')=60) or (xb='女' and datediff(year,cssj,'2005-1-1')=50 and ygxs like '%工%'其中年份与时间的'2005','2005-1-1'是动态变化的,如何生成形如如下的代码呢
select 数量=count(*), 年份=2005 from jibenqingkuang where (xb='男' and datediff(year,cssj,'2005-1-1')=60) or (xb='女' and datediff(year,cssj,'2005-1-1')=50 and ygxs like '%工%'
union
select 数量=count(*), 年份=2006 from jibenqingkuang where (xb='男' and datediff(year,cssj,'2006-1-1')=60) or (xb='女' and datediff(year,cssj,'2006-1-1')=50 and ygxs like '%工%'
union
select 数量=count(*), 年份=2007 from jibenqingkuang where (xb='男' and datediff(year,cssj,'2007-1-1')=60) or (xb='女' and datediff(year,cssj,'2007-1-1')=50 and ygxs like '%工%'
如输入2009则代码会依次类推union到2009年的时间
select 数量=count(*), 年份=2005 from jibenqingkuang where (xb='男' and datediff(year,cssj,'2005-1-1')=60) or (xb='女' and datediff(year,cssj,'2005-1-1')=50 and ygxs like '%工%'其中年份与时间的'2005','2005-1-1'是动态变化的,如何生成形如如下的代码呢
select 数量=count(*), 年份=2005 from jibenqingkuang where (xb='男' and datediff(year,cssj,'2005-1-1')=60) or (xb='女' and datediff(year,cssj,'2005-1-1')=50 and ygxs like '%工%'
union
select 数量=count(*), 年份=2006 from jibenqingkuang where (xb='男' and datediff(year,cssj,'2006-1-1')=60) or (xb='女' and datediff(year,cssj,'2006-1-1')=50 and ygxs like '%工%'
union
select 数量=count(*), 年份=2007 from jibenqingkuang where (xb='男' and datediff(year,cssj,'2007-1-1')=60) or (xb='女' and datediff(year,cssj,'2007-1-1')=50 and ygxs like '%工%'
如输入2009则代码会依次类推union到2009年的时间
解决方案 »
- 请教高手TSTRINGLIST的详细解释
- 怎么转换哦???
- OCX中使用为什么不能使用Synchronize进行多线程同步?
- 欢迎加入QQ群:Delphi专家,群号:8036747
- 怎样把STRINGGRID中的数据通过报表的形式打印出来?
- 关于 QuickRept 的打印问题,请进。。。。。。。。
- 怪。。。。。。。。。。。。。。。。。。。。。。。。
- 怎样用语句写FileListBox1的路径(现用)
- 打印报表时可不可以设置一个图片为背图.(关于报表的两个头痛的问题)
- 紧急救助:请教一个TClientDataSet.LoadFromFile问题
- 如何把一张GIF或JPG图片裁剪成自己想要的图片?
- gdi, ERROR_NOT_ENOUGH_MEMORY ,help !
edit1输入2005-1-1 ,edit2输入2008-12-31则会统计出如下数据
数量 年份
1000 2005
1200 2006
1100 2007
1200 2008
function TForm1.GetUnionSQL(Year: Integer): String;
const
SQL = 'select 数量=count(*), 年份=%d from jibenqingkuang where (xb=''男'' '
+ 'and datediff(year,cssj,''%d-1-1'')=60) or (xb=''女'' '
+ 'and datediff(year,cssj,''%d-1-1'')=50 and ygxs like ''%%工%%'' ';
CLStr = #13#10;
var
Strtmp: string;
i: Integer;
begin
Result := '';
for i := 2005 to Year do
begin
if Result = '' then
Result := Format(SQL, [i, i, i])
else
Result := Format('%s %s union %s ' + SQL , [Result, CLStr, CLStr, i, i, i])
end;
end;
//调用
procedure TForm1.Button1Click(Sender: TObject);
begin
Memo1.Lines.Add( GetUnionSQL(2009) );
end;//返回结果
{select 数量=count(*), 年份=2005 from jibenqingkuang where (xb='男' and datediff(year,cssj,'2005-1-1')=60) or (xb='女' and datediff(year,cssj,'2005-1-1')=50 and ygxs like '%工%'
union
select 数量=count(*), 年份=2006 from jibenqingkuang where (xb='男' and datediff(year,cssj,'2006-1-1')=60) or (xb='女' and datediff(year,cssj,'2006-1-1')=50 and ygxs like '%工%'
union
select 数量=count(*), 年份=2007 from jibenqingkuang where (xb='男' and datediff(year,cssj,'2007-1-1')=60) or (xb='女' and datediff(year,cssj,'2007-1-1')=50 and ygxs like '%工%'
union
select 数量=count(*), 年份=2008 from jibenqingkuang where (xb='男' and datediff(year,cssj,'2008-1-1')=60) or (xb='女' and datediff(year,cssj,'2008-1-1')=50 and ygxs like '%工%'
union
select 数量=count(*), 年份=2009 from jibenqingkuang where (xb='男' and datediff(year,cssj,'2009-1-1')=60) or (xb='女' and datediff(year,cssj,'2009-1-1')=50 and ygxs like '%工%'
}