Рефакторинг Excel в ADOQuery, DBGrid

[Viktor61]

Сообщение от Serega_SS

SELECT t1.*, t2.*, t3.*, t4.* FROM (SELECT * FROM [Лист1$] WHERE [#] BETWEEN 1 and 15000) as t1
left join (SELECT * FROM [Лист1$] WHERE [#] BETWEEN 15001 and 30000) as t2 on t1.[#]+15000 =t2.[#]
left join (SELECT * FROM [Лист1$] WHERE [#] BETWEEN 30001 and 45000) as t3 on t1.[#]+30000 =t3.[#]
left join (SELECT * FROM [Лист1$] WHERE [#] BETWEEN 45001 and 60000) as t4 on t1.[#]+45000 =t4.[#]

А если изменить на

SELECT t1.*, t2.*,  t3.*, t4.*  FROM (SELECT * FROM [Лист1$] WHERE [#] BETWEEN 1 and 15000) as t1
left join (SELECT * FROM [Лист1$] WHERE [#] BETWEEN 15001 and 30000) as t2 on (t1.[#]+15000) =t2.[#] 
left join (SELECT * FROM [Лист1$] WHERE [#] BETWEEN 30001 and 45000) as t3 on (t1.[#]+30000) =t3.[#]
left join (SELECT * FROM [Лист1$] WHERE [#] BETWEEN 45001 and 60000) as t4 on (t1.[#]+45000) =t4.[#]

т.е. t1.[#]+15000 заменить на (t1.[#]+15000) и проч?

[Serega_SS]

Пробовал и так. Не хватает оператора.

begin
  if not OpenDialog1.Execute then exit;
  ADOConnection1.Close;
  ADOConnection1.ConnectionString := 'Provider=Microsoft.ACE.OLEDB.12.0;Data Source=' + OpenDialog1.FileName + ';User ID=Admin;Password=;Extended Properties=Excel 8.0';
  Label1.Caption:=ExtractFileName(OpenDialog1.FileName);
  ADOConnection1.Open;
  ADOQuery1.SQL.Clear;
  ADOQuery1.SQL.Add('SELECT t1.*, t2.*, t3.* FROM (SELECT * FROM [Лист1$] WHERE [#] BETWEEN 1 and 15000) as t1' + char(10) +
  'LEFT JOIN (SELECT * FROM [Лист1$] WHERE [#] BETWEEN 15001 and 30000) as t2 on (t1.[#]+15000) =t2.[#]' (ВОТ СЮДА НУЖЕН КАКОЙТО ОПЕРАТОР)  + char(10) +
  'LEFT JOIN (SELECT * FROM [Лист1$] WHERE [#] BETWEEN 30001 and 45000) as t3 on (t2.[#]+15000) =t3.[#]');
  ADOQuery1.Open;
end;

пока нет третей строки все норм, как появляется еще одна строка пишет не хватает оператора.

[Serega_SS]

Если делаю так

begin
  if not OpenDialog1.Execute then exit;
  ADOConnection1.Close;
  ADOConnection1.ConnectionString := 'Provider=Microsoft.ACE.OLEDB.12.0;Data Source=' + OpenDialog1.FileName + ';User ID=Admin;Password=;Extended Properties=Excel 8.0';
  Label1.Caption:=ExtractFileName(OpenDialog1.FileName);
  ADOConnection1.Open;
  ADOQuery1.SQL.Clear;
  ADOQuery1.SQL.Add('SELECT t1.*, t2.* FROM (SELECT * FROM [Лист1$] WHERE [#] BETWEEN 1 and 15000) as t1' + char(10) +
  'LEFT JOIN (SELECT * FROM [Лист1$] WHERE [#] BETWEEN 15001 and 30000) as t2 on (t1.[#]+15000) =t2.[#]');
  ADOQuery1.Open;
end;

Все работает правильно

[Serega_SS]

Можно так сделать

begin
  if not OpenDialog1.Execute then exit;
  ADOConnection1.Close;
  ADOConnection1.ConnectionString := 'Provider=Microsoft.ACE.OLEDB.12.0;Data Source=' + OpenDialog1.FileName + ';User ID=Admin;Password=;Extended Properties=Excel 8.0';
  Label1.Caption:=ExtractFileName(OpenDialog1.FileName);
  ADOConnection1.Open;
  ADOQuery1.SQL.Clear;
  ADOQuery2.SQL.Clear;
  ADOQuery1.SQL.Add('SELECT t1.*, t2.* FROM (SELECT * FROM [Лист1$] WHERE NUM BETWEEN 1 and 15000) as t1' + char(10) +
  'LEFT JOIN (SELECT * FROM [Лист1$] WHERE NUM BETWEEN 15001 and 30000) as t2 on (t1.NUM+15000) = t2.NUM');

  ADOQuery2.SQL.Add('SELECT t3.*, t4.* FROM (SELECT * FROM [Лист1$] WHERE NUM BETWEEN 30001 and 45000) as t3' + char(10) +
  'LEFT JOIN (SELECT * FROM [Лист1$] WHERE NUM BETWEEN 45001 and 60000) as t4 on (t3.NUM+15000) = t4.NUM');
  ADOQuery1.Open;
  ADOQuery2.Open;
end;

только как теперь их собрать в одну ДБ

[evg_m]

а так ?..

select t12.*, t34.*
from ( --- 1 
          select t1.*, t2.* ...
       ) as t12
left join ( ---2
              select t3.*, t4.* ...
            ) as t34 on t12.... =t34.....

[Serega_SS]

Сообщение от evg_m

а так ?..

select t12.*, t34.*
from ( --- 1 
          select t1.*, t2.* ...
       ) as t12
left join ( ---2
              select t3.*, t4.* ...
            ) as t34 on t12.... =t34.....

Не понимаю как обратится к Adoquery1, чтобы запросить данные т1 и т2

[evg_m]

нет просто написать ОДИН большой

[Serega_SS]

Сообщение от evg_m

нет просто написать ОДИН большой

Спасибо тебе дружище!
Все получилось, только очень замудренно.
Думаю есть варианты по проще. Просто если надо будет поделить на 20 частей, мозг лопнет.
Пока буду использовать так, походу может быть найду другой вариант.
Еще раз спасибо.
Вот такой код получился у меня.

ADOQuery1.SQL.Add('SELECT t10.*, t20.*  FROM' + char(10) +
                          '(SELECT t1.*, t2.* FROM (SELECT * FROM [Лист1$] WHERE NUM BETWEEN 1 and 15000) as t1' + char(10) +
                          'LEFT JOIN (SELECT * FROM [Лист1$] WHERE NUM BETWEEN 15001 and 30000) as t2 on (t1.NUM+15000) = t2.NUM) as t10' + char(10) +

                    'LEFT JOIN'  + char(10) +
                          '(SELECT t3.*, t4.* FROM (SELECT * FROM [Лист1$] WHERE NUM BETWEEN 30001 and 45000) as t3' + char(10) +
                          'LEFT JOIN (SELECT * FROM [Лист1$] WHERE NUM BETWEEN 45001 and 60000) as t4 on (t3.NUM+15000) = t4.NUM) as t20 on (t10.t1.NUM +30000) = t20.t3.NUM');

[evg_m]

а попробуй так

SELECT t1.*, t2.*,  t3.*, t4.*  
FROM ( ( [Лист1$] as t1 
         left join [Лист1$] as t2 on t1.[#] +15000 =t2.[#]
       ) left join [Лист1$] as t3 on t1.[#] +30000 =t3.[#]
     )   left join [Лист1$] as t4 on t1.[#] +45000 =t4.[#]

where t1.[#] between 1 and 15000

[Serega_SS]

Сообщение от evg_m

а попробуй так

Так тоже сработало

ADOQuery1.SQL.Add('SELECT t1.*, t2.*,  t3.*, t4.* FROM ( ( [Лист1$] as t1' + char(10) +
         'left join [Лист1$] as t2 on t1.NUM +15000 =t2.NUM)' + char(10) +
         'left join [Лист1$] as t3 on t1.NUM +30000 =t3.NUM)' + char(10) +
         'left join [Лист1$] as t4 on t1.NUM +45000 =t4.NUM' + char(10) +
         'where t1.NUM between 1 and 15000');