如下:
str:='Select name,'+
'(Select vegetable_price From tablemenu AA where name=AA.name and paying_time=AA.paying_time) as vegetable_price,'+
'(Select fruit_price From tablemenu AA where name=AA.name and paying_time=AA.paying_time) as fruit_price,'+
'(Select rice_price From tablemenu AA where name=AA.name and paying_time=AA.paying_time) as rice_price,'+
'(Select adjuvant_price From tablemenu AA where name=AA.name and paying_time=AA.paying_time) as adjuvant_price,'+
'(Select meat_price From tablemenu AA where name=AA.name and paying_time=AA.paying_time) as meat_price,'+
'(Select living_use_price From tablemenu AA where name=AA.name and paying_time=AA.paying_time) as living_use_price,'+
'(Select other_price From tablemenu AA where name=AA.name and paying_time=AA.paying_time) as other_price,'+
'(Select eat_only_price From tablemenu AA where name=AA.name and paying_time=AA.paying_time) as eat_only_price,'+
'(Select total_price From tablemenu AA where name=AA.name and paying_time=AA.paying_time) as total_price,'+ '(Select chummage From tableother where name=AA.name and pay_date=AA.paying_time) as chummage,'+
'(Select rubbish_cost From tableother where name=AA.name and pay_date=AA.paying_time) as rubbish_cost,'+
'(Select water_cost From tableother where name=AA.name and pay_date=AA.paying_time) as water_cost,'+
'(Select peace_cost From tableother where name=AA.name and pay_date=AA.paying_time) as peace_cost,'+
'(Select eletricity_cost From tableother where name=AA.name and pay_date=AA.paying_time) as eletricity_cost,'+
'(Select phone_cost From tableother where name=AA.name and pay_date=AA.paying_time) as phone_cost,'+
'(Select adsl_cost From tableother where name=AA.name and pay_date=AA.paying_time) as adsl_cost,'+
'(Select tv_cost From tableother where name=AA.name and pay_date=AA.paying_time) as tv_cost,'+
'(Select earnest From tableother where name=AA.name and pay_date=AA.paying_time) as earnest,'+
'(Select other_cost From tableother where name=AA.name and pay_date=AA.paying_time) as other_cost,'+
'(Select incidental_expenses From tableother where name=AA.name and pay_date=AA.paying_time) as incidental_expenses,'+
'(Select total_cost From tableother where name=AA.name and pay_date=AA.paying_time) as total_cost,'+
//'(Select incidental_expenses From tableother where name=AA.name and pay_date=AA.date) as incidental_expenses,'+ 'paying_time '+
'From (Select distinct name,paying_time From Tablemenu Union Select distinct name,pay_date From Tableother) AA ';
//'where '+
//'table1.name="aa" '; ADOQuery3.Close;
ADOQuery3.SQL.Clear;
ADOQuery3.SQL.Add(str);
ADOQuery3.Open;提示是“子查询最多能返回一个记录”,我用的是access,看大家能否用较简单的语句实现相同的功能,谢谢了。
str:='Select name,'+
'(Select vegetable_price From tablemenu AA where name=AA.name and paying_time=AA.paying_time) as vegetable_price,'+
'(Select fruit_price From tablemenu AA where name=AA.name and paying_time=AA.paying_time) as fruit_price,'+
'(Select rice_price From tablemenu AA where name=AA.name and paying_time=AA.paying_time) as rice_price,'+
'(Select adjuvant_price From tablemenu AA where name=AA.name and paying_time=AA.paying_time) as adjuvant_price,'+
'(Select meat_price From tablemenu AA where name=AA.name and paying_time=AA.paying_time) as meat_price,'+
'(Select living_use_price From tablemenu AA where name=AA.name and paying_time=AA.paying_time) as living_use_price,'+
'(Select other_price From tablemenu AA where name=AA.name and paying_time=AA.paying_time) as other_price,'+
'(Select eat_only_price From tablemenu AA where name=AA.name and paying_time=AA.paying_time) as eat_only_price,'+
'(Select total_price From tablemenu AA where name=AA.name and paying_time=AA.paying_time) as total_price,'+ '(Select chummage From tableother where name=AA.name and pay_date=AA.paying_time) as chummage,'+
'(Select rubbish_cost From tableother where name=AA.name and pay_date=AA.paying_time) as rubbish_cost,'+
'(Select water_cost From tableother where name=AA.name and pay_date=AA.paying_time) as water_cost,'+
'(Select peace_cost From tableother where name=AA.name and pay_date=AA.paying_time) as peace_cost,'+
'(Select eletricity_cost From tableother where name=AA.name and pay_date=AA.paying_time) as eletricity_cost,'+
'(Select phone_cost From tableother where name=AA.name and pay_date=AA.paying_time) as phone_cost,'+
'(Select adsl_cost From tableother where name=AA.name and pay_date=AA.paying_time) as adsl_cost,'+
'(Select tv_cost From tableother where name=AA.name and pay_date=AA.paying_time) as tv_cost,'+
'(Select earnest From tableother where name=AA.name and pay_date=AA.paying_time) as earnest,'+
'(Select other_cost From tableother where name=AA.name and pay_date=AA.paying_time) as other_cost,'+
'(Select incidental_expenses From tableother where name=AA.name and pay_date=AA.paying_time) as incidental_expenses,'+
'(Select total_cost From tableother where name=AA.name and pay_date=AA.paying_time) as total_cost,'+
//'(Select incidental_expenses From tableother where name=AA.name and pay_date=AA.date) as incidental_expenses,'+ 'paying_time '+
'From (Select distinct name,paying_time From Tablemenu Union Select distinct name,pay_date From Tableother) AA ';
//'where '+
//'table1.name="aa" '; ADOQuery3.Close;
ADOQuery3.SQL.Clear;
ADOQuery3.SQL.Add(str);
ADOQuery3.Open;提示是“子查询最多能返回一个记录”,我用的是access,看大家能否用较简单的语句实现相同的功能,谢谢了。
里面分别有字段为
name,a1,a2,date
aa 1 1 2005-1-1
bb 2 1 2005-1-2
aa 1 2005-1-2
aa 1 2005-1-3name,b1,b2,date
aa 1 1 2005-1-1
bb
bb 1 2 2005-1-3
aa 1 2005-1-4现在我想用adoquery查询这两表中的aa,并在DBGrid上一齐显示,
如何在表中做到如下显示
name a1 a2 b1 b2 date
aa 1 1 1 1 2005-1-1
aa 1 2005-1-2
aa 1 2005-1-3
aa 1 2005-1-4
with adoquery1 do
begin
sql.close
sql.clear;
sql.add('select [table1].name,[table1].a1,[table1].a2,[table2].b1,[table2].b2 from table1,table2 where [table1].name=name and [table1].name=[table2].name and (([table1].date=[table2].date) or ([table1].date is not null and [table2].date is null)or([table1].date is null and [table2].date is not null))');
open;
end;
'Select name,'+
'(Select vegetable_price From tablemenu AA where name=AA.name and paying_time=AA.paying_time) as vegetable_price,'+之类
但是有一条或多条子查询返回的不是一条记录而是一个记录集,所以报错,你把你的子查询语句都挑出来执行一遍然后把返回多条的修改查询条件就搞定了
str:='Select name,'+
'(Select a1 From table1 where name=AA.name and date=AA.date) as a1,'+
'(Select a2 From table1 where name=AA.name and date=AA.date) as a2,'+
'(Select b1 From table2 where name=AA.name and date=AA.date) as b1,'+
'(Select b2 From table2 where name=AA.name and date=AA.date) as b2,'+
'date '+
'From (Select distinct name,date From Table1 Union Select distinct name,date From Table2) AA '+
'where '+
'table1.name="aa" ';
请详细指教一下,谢谢
在一個一個嵌套查詢,
例如先嵌套兩個運行,沒問題的話在嵌套下一個,這樣就能找出問題所在
请问兄台能否帮我写一下存储过程的语句,谢谢
里面分别有字段为
name,a1,a2,date
aa 1 1 2005-1-1
bb 2 1 2005-1-2
aa 1 2005-1-2
aa 1 2005-1-3name,b1,b2,date
aa 1 1 2005-1-1
bb
bb 1 2 2005-1-3
aa 1 2005-1-4现在我想用adoquery查询这两表中的aa,并在DBGrid上一齐显示,
如何在表中做到如下显示
name a1 a2 b1 b2 date
aa 1 1 1 1 2005-1-1
aa 1 2005-1-2
aa 1 2005-1-3
aa 1 2005-1-4
这个实现很简单:
select 表1.a1,表1.a2,表2.b1,表2.b2,表1.date from 表1 left join 表2 on 表1.name=表2.name