//子查询语句不能用,需改为视图
SQLQuery1.SQL.Add('select b.id,b.meter_num,b.gas_cust_code,c.Unit_Price from gas_cust a inner join meter_info b on a.id=b.gas_cust_code '+
' and CHAR_LENGTH(a.code)=5 and a.cust_status=''正常'' '+
' and b.gas_cust_code not in '+
'( '+
'select cust_id as gas_cust_code from busi_read_meter_log where fee_month='''+yf+''' '+
')'+
' inner join gas_price c on b.price_type=c.id '
);
以上代码中
(select cust_id as gas_cust_code from busi_read_meter_log where ..)
改为固定值后没问题,直接用sql语句查有问题,不知具体原因,错误提示(
(''2015'',''2016'')
新建视图,改为如下代码后没有问题,并且sqlquery1.recordcount可以返回正常值,可能delphi用recordcount只能在查单表的时候可以返回正确记录数
{SQLQuery1.SQL.Add('select * from v_gas0tocreate ');
SQLQuery1.SQL.Add(' where gas_cust_code not in (select cust_id as gas_cust_code from busi_read_meter_log where fee_month='''+yf+''')'); }
以下循环数据,不管是关联表还是单表,循环次数都是正确的:
adoq1.first;
while not adoq1.Eof do
begin
if 布尔变量=true then
begin
adoq1.Next;//一定要加这一句,否则会死循环
Continue;
end;
adoq1.Next; //这个是不是也要加呢 不然如果不是第一个不是true 也会死循环把
end;
第二种情况:delphi连接mysql,使用limit 0,30会报 list index out of bounds (-1),如下
SQLQuery1.Close;
SQLQuery1.SQL.Clear;
SQLQuery1.SQL.Add('select *,''居民'' type,'''+cbbmonth.Text+''' fee_month,'''' No from ( '
+'select xqname,sum(ql) ql from pbql where fee_month='''+cbbmonth.Text+''' and type=''居民普表'' group by xqname'
+') a order by convert(xqname USING gbk) COLLATE gbk_chinese_ci limit 0,30');
SQLQuery1.Active:=True;
改成limit 30后就正常,如下
SQLQuery1.Close;
SQLQuery1.SQL.Clear;
SQLQuery1.SQL.Add('select *,''居民'' type,'''+cbbmonth.Text+''' fee_month,'''' No from ( '
+'select xqname,sum(ql) ql from pbql where fee_month='''+cbbmonth.Text+''' and type=''居民普表'' group by xqname'
+') a order by convert(xqname USING gbk) COLLATE gbk_chinese_ci limit 30');
SQLQuery1.Active:=True;