SELECT, LEFT JOIN, UNION ALL, DTOC для MS FoxPro
Вторник 28 Август 2018 - 12:32:55  |  Admin

В ходе работы появлялись задачи по переносу информацию из одной БД в другую. Программная среда работает под управлением MS FoxPro и означает, что данные находятся в файлах DBF. Для этого приходилось писать различные запросы для изымания информации из БД и размещении её в файле XLS. Решил поделиться данными записями, возможно кому-то понадобиться.

1)
SELECT C.LIC ;
,C.LICERC ;
,C.AID ;
,A.IDSA ;
,A.KODST ;
,A.VIDST ;
,A.STATUS ;
,A.TIP ;
,A.MODEL_PU ;
,A.MARKA_PU ;
,A.ZNOM ;
,DTOC(A.Dat_akt ) Dat_akt ;
,DTOC(A.Dat_p1 ) Dat_p1 ;
,DTOC(A.Dat_p2 ) Dat_p2 ;
from dat_s_a A LEFT JOIN dataa C ON A.aid = C.aid ;
WHERE A.STATUS = 'Проверка' ;
ORDER BY A.STATUS

2)
SELECT A.Street, A.HOME ,A.FLAT ;
, COUNT(*) AS KKK ;
FROM dataa A ;
GROUP BY A.Street, A.HOME ,A.FLAT HAVING COUNT(*) > 1 ;
order by A.HOME ;
INTO CURSOR CUR1
SELECT * FROM dataa A INTO CURSOR CUR2
SELECT CUR2.LIC, CUR2.LICERC,CUR1.Street, CUR1.HOME, CUR1.FLAT , CUR1.KKK FROM CUR1 LEFT JOIN CUR2 ON CUR1.Street = CUR2.Street AND CUR1.HOME = CUR2.HOME AND CUR1.FLAT = CUR2.FLAT ;
WHERE CUR1.KKK

3)
select A.AID, A.LIC, A.LICERC,B.KAT P1 , "Подогрев воды" AS P2, B.TNPL93 AS P3 FROM datab B LEFT JOIN dataa A ON A.KAT= B.KAT WHERE B.TNPL93 <> 0 ;
UNION ALL ;
select A.AID, A.LIC, A.LICERC,B.KAT P1 , "Холодное водоснабжение ФВ" AS P2, B.OPLS51 AS P3 FROM datab B LEFT JOIN dataa A ON A.KAT= B.KAT WHERE B.OPLS51 <> 0;
UNION ALL ;
select A.AID, A.LIC, A.LICERC,B.KAT P1 , "Водоотведение по холодной воде" AS P2, B.OPLS52 AS P3 FROM datab B LEFT JOIN dataa A ON A.KAT= B.KAT WHERE B.OPLS52 <> 0;
ORDER BY 2

4)
select A.AID, A.LIC, A.LICERC, A.Street , A.Home , A.Flat , D.KAT P1 , "ТО ВКГО" AS P2, LPLSTR P3 , IIF ( A.OPLKV = A.OPL AND A.OPL <> 0, 0 , (LPLSTR / A.OPLKV * A.OPL)) AS P4, A.OPLKV OPL_KV , A.OPL OPL_ , LPLSTR TARIFF FROM datad D LEFT JOIN dataa A ON A.LGOTA= D.KAT WHERE LPLSTR <> 0 ;
ORDER BY 2


5)

select LS_DGH P1 , "UST_ODPU" AS P2 , UST_ODPU AS P3 FROM PER WHERE VAL(UST_ODPU) <> 0 ;

UNION ALL ;

select LS_DGH P1 , "DOP_USL" AS P2 , DOP_USL AS P3 FROM PER WHERE VAL(DOP_USL ) <> 0 ;

UNION ALL ;

select LS_DGH P1 , "DOMOFON" AS P2 , DOMOFON AS P3 FROM PER WHERE VAL(DOMOFON ) <> 0 ;

UNION ALL ;

select LS_DGH P1 , "TO_VKGO" AS P2 , TO_VKGO AS P3 FROM PER WHERE VAL(TO_VKGO ) <> 0 ;

Order by LS_DGH

6)
select DISTINCT A.AID, A.LIC, A.LICERC, A.Street, A.Home, A.Flat, "Горячая вода ФВ" AS P1 , A.PL91 AS P2 ;
FROM dataa A LEFT JOIN dat_s_a S ON A.AID = S.AID ;
WHERE S.VIDST ='Горячая вода' AND S.STATUS= 'Действует' AND A.PL91 >= 0 INTO CURSOR C1

select DISTINCT A.AID, A.LIC, A.LICERC, A.Street, A.Home, A.Flat, "Горячая вода ФВ" AS P1 , G.DPL91 AS P2 ;
FROM dataa A LEFT JOIN dat_s_a S ON A.AID = S.AID LEFT JOIN datag G ON A.AID = G.AID ;
WHERE S.VIDST ='Горячая вода' AND S.STATUS= 'Действует' AND G.DPL91 <> 0 AND G.DATRAS = CTOD('05.06.2018') INTO CURSOR C61

select DISTINCT A.AID, A.LIC, A.LICERC, A.Street, A.Home, A.Flat, "Горячая вода ФВ" AS P1 , G.PL91 AS P2 ;
FROM dataa A LEFT JOIN dat_s_a S ON A.AID = S.AID LEFT JOIN datag G ON A.AID = G.AID ;
WHERE S.VIDST ='Горячая вода' AND S.STATUS= 'Действует' AND G.PL91 > 0 AND G.DATRAS = CTOD('05.06.2018') INTO CURSOR C62

select DISTINCT A.AID, A.LIC, A.LICERC, A.Street, A.Home, A.Flat, "Горячая вода ФВ" AS P1 , G.DPL91 AS P2 ;
FROM dataa A LEFT JOIN dat_s_a S ON A.AID = S.AID LEFT JOIN datag G ON A.AID = G.AID ;
WHERE S.VIDST ='Горячая вода' AND S.STATUS= 'Действует' AND G.DPL91 <> 0 AND G.DATRAS = CTOD('05.05.2018') INTO CURSOR C51

select DISTINCT A.AID, A.LIC, A.LICERC, A.Street, A.Home, A.Flat, "Горячая вода ФВ" AS P1 , G.PL91 AS P2 ;
FROM dataa A LEFT JOIN dat_s_a S ON A.AID = S.AID LEFT JOIN datag G ON A.AID = G.AID ;
WHERE S.VIDST ='Горячая вода' AND S.STATUS= 'Действует' AND G.PL91 > 0 AND G.DATRAS = CTOD('05.05.2018') INTO CURSOR C52

select DISTINCT A.AID, A.LIC, A.LICERC, A.Street, A.Home, A.Flat, "Горячая вода ФВ" AS P1 , G.DPL91 AS P2 ;
FROM dataa A LEFT JOIN dat_s_a S ON A.AID = S.AID LEFT JOIN datag G ON A.AID = G.AID ;
WHERE S.VIDST ='Горячая вода' AND S.STATUS= 'Действует' AND G.DPL91 <> 0 AND G.DATRAS = CTOD('05.04.2018') INTO CURSOR C41

select DISTINCT A.AID, A.LIC, A.LICERC, A.Street, A.Home, A.Flat, "Горячая вода ФВ" AS P1 , G.PL91 AS P2 ;
FROM dataa A LEFT JOIN dat_s_a S ON A.AID = S.AID LEFT JOIN datag G ON A.AID = G.AID ;
WHERE S.VIDST ='Горячая вода' AND S.STATUS= 'Действует' AND G.PL91 > 0 AND G.DATRAS = CTOD('05.04.2018') INTO CURSOR C42

select DISTINCT A.AID, A.LIC, A.LICERC, A.Street, A.Home, A.Flat, "Горячая вода ФВ" AS P1 , G.DPL91 AS P2 ;
FROM dataa A LEFT JOIN dat_s_a S ON A.AID = S.AID LEFT JOIN datag G ON A.AID = G.AID ;
WHERE S.VIDST ='Горячая вода' AND S.STATUS= 'Действует' AND G.DPL91 <> 0 AND G.DATRAS = CTOD('05.03.2018') INTO CURSOR C31

select DISTINCT A.AID, A.LIC, A.LICERC, A.Street, A.Home, A.Flat, "Горячая вода ФВ" AS P1 , G.PL91 AS P2 ;
FROM dataa A LEFT JOIN dat_s_a S ON A.AID = S.AID LEFT JOIN datag G ON A.AID = G.AID ;
WHERE S.VIDST ='Горячая вода' AND S.STATUS= 'Действует' AND G.PL91 > 0 AND G.DATRAS = CTOD('05.03.2018') INTO CURSOR C32

select DISTINCT A.AID, A.LIC, A.LICERC, A.Street, A.Home, A.Flat, "Горячая вода ФВ" AS P1 , G.DPL91 AS P2 ;
FROM dataa A LEFT JOIN dat_s_a S ON A.AID = S.AID LEFT JOIN datag G ON A.AID = G.AID ;
WHERE S.VIDST ='Горячая вода' AND S.STATUS= 'Действует' AND G.DPL91 <> 0 AND G.DATRAS = CTOD('05.02.2018') INTO CURSOR C21

select DISTINCT A.AID, A.LIC, A.LICERC, A.Street, A.Home, A.Flat, "Горячая вода ФВ" AS P1 , G.PL91 AS P2 ;
FROM dataa A LEFT JOIN dat_s_a S ON A.AID = S.AID LEFT JOIN datag G ON A.AID = G.AID ;
WHERE S.VIDST ='Горячая вода' AND S.STATUS= 'Действует' AND G.PL91 > 0 AND G.DATRAS = CTOD('05.02.2018') INTO CURSOR C22

select DISTINCT A.AID, A.LIC, A.LICERC, A.Street, A.Home, A.Flat, "Горячая вода ФВ" AS P1 , G.DPL91 AS P2 ;
FROM dataa A LEFT JOIN dat_s_a S ON A.AID = S.AID LEFT JOIN datag G ON A.AID = G.AID ;
WHERE S.VIDST ='Горячая вода' AND S.STATUS= 'Действует' AND G.DPL91 <> 0 AND G.DATRAS = CTOD('05.01.2018') INTO CURSOR C11

select DISTINCT A.AID, A.LIC, A.LICERC, A.Street, A.Home, A.Flat, "Горячая вода ФВ" AS P1 , G.PL91 AS P2 ;
FROM dataa A LEFT JOIN dat_s_a S ON A.AID = S.AID LEFT JOIN datag G ON A.AID = G.AID ;
WHERE S.VIDST ='Горячая вода' AND S.STATUS= 'Действует' AND G.PL91 > 0 AND G.DATRAS = CTOD('05.01.2018') INTO CURSOR C12

* ==========================================================
* ЗАПРОС 1 Горячая вода
* ==========================================================

SELECT C1.P1 ;
, C1.AID ;
, C1.LIC ;
, C1.LICERC;
, C1.Street;
, C1.Home;
, C1.Flat;
, THISFORM.metod_tip(C1.P2) AS "M_ИЮЛЬ" ;
, THISFORM.metod_tip(C61.P2) AS "M_ИЮНЬ" ;
, THISFORM.metod_tip (C62.P2) AS "M_ИЮНЬ_Н" ;
, THISFORM.metod_tip (C51.P2) AS "M_МАЙ" ;
, THISFORM.metod_tip (C52.P2) AS "M_МАЙ_Н" ;
, THISFORM.metod_tip (C41.P2) AS "M_АПР" ;
, THISFORM.metod_tip (C42.P2) AS "M_АПР_Н" ;
, THISFORM.metod_tip (C31.P2) AS "M_МАРТ" ;
, THISFORM.metod_tip (C32.P2) AS "M_МАРТ_Н" ;
, THISFORM.metod_tip (C21.P2) AS "M_ФЕВ" ;
, THISFORM.metod_tip (C22.P2) AS "M_ФЕВ_Н" ;
, THISFORM.metod_tip (C11.P2) AS "M_ЯНВ" ;
, THISFORM.metod_tip (C12.P2) AS "M_ЯНВ_Н" ;
FROM C1 ;
LEFT JOIN C61 ON C1.AID = C61.AID ;
LEFT JOIN C62 ON C1.AID = C62.AID ;
LEFT JOIN C51 ON C1.AID = C51.AID ;
LEFT JOIN C52 ON C1.AID = C52.AID ;
LEFT JOIN C41 ON C1.AID = C41.AID ;
LEFT JOIN C42 ON C1.AID = C42.AID ;
LEFT JOIN C31 ON C1.AID = C31.AID ;
LEFT JOIN C32 ON C1.AID = C32.AID ;
LEFT JOIN C21 ON C1.AID = C21.AID ;
LEFT JOIN C22 ON C1.AID = C22.AID ;
LEFT JOIN C11 ON C1.AID = C11.AID ;
LEFT JOIN C12 ON C1.AID = C12.AID ;
ORDER BY C1.AID INTO CURSOR GLOB1

* ==========================================================
* ЗАПРОС 2 Холодная вода
* ==========================================================

select DISTINCT A.AID, A.LIC, A.LICERC, A.Street, A.Home, A.Flat, "Холодная вода ФВ" AS P1 , A.PL51 AS P2 ;
FROM dataa A LEFT JOIN dat_s_a S ON A.AID = S.AID ;
WHERE S.VIDST ='Холодная вода' AND S.STATUS= 'Действует' AND A.PL51 => 0 ;
INTO CURSOR C1

select DISTINCT A.AID, A.LIC, A.LICERC, A.Street, A.Home, A.Flat, "Холодная вода ФВ" AS P1 , G.DPL51 AS P2 ;
FROM dataa A LEFT JOIN dat_s_a S ON A.AID = S.AID LEFT JOIN datag G ON A.AID = G.AID ;
WHERE S.VIDST ='Холодная вода' AND S.STATUS= 'Действует' AND G.DPL51 <> 0 AND G.DATRAS = CTOD('05.06.2018') ;
INTO CURSOR C61

select DISTINCT A.AID, A.LIC, A.LICERC, A.Street, A.Home, A.Flat, "Холодная вода ФВ" AS P1 , G.PL51 AS P2 ;
FROM dataa A LEFT JOIN dat_s_a S ON A.AID = S.AID LEFT JOIN datag G ON A.AID = G.AID ;
WHERE S.VIDST ='Холодная вода' AND S.STATUS= 'Действует' AND G.PL51 > 0 AND G.DATRAS = CTOD('05.06.2018') ;
INTO CURSOR C62

select DISTINCT A.AID, A.LIC, A.LICERC, A.Street, A.Home, A.Flat, "Холодная вода ФВ" AS P1 , G.DPL51 AS P2 ;
FROM dataa A LEFT JOIN dat_s_a S ON A.AID = S.AID LEFT JOIN datag G ON A.AID = G.AID ;
WHERE S.VIDST ='Холодная вода' AND S.STATUS= 'Действует' AND G.DPL51 <> 0 AND G.DATRAS = CTOD('05.05.2018') ;
INTO CURSOR C51

select DISTINCT A.AID, A.LIC, A.LICERC, A.Street, A.Home, A.Flat, "Холодная вода ФВ" AS P1 , G.PL51 AS P2 ;
FROM dataa A LEFT JOIN dat_s_a S ON A.AID = S.AID LEFT JOIN datag G ON A.AID = G.AID ;
WHERE S.VIDST ='Холодная вода' AND S.STATUS= 'Действует' AND G.PL51 > 0 AND G.DATRAS = CTOD('05.05.2018') ;
INTO CURSOR C52

select DISTINCT A.AID, A.LIC, A.LICERC, A.Street, A.Home, A.Flat, "Холодная вода ФВ" AS P1 , G.DPL51 AS P2 ;
FROM dataa A LEFT JOIN dat_s_a S ON A.AID = S.AID LEFT JOIN datag G ON A.AID = G.AID ;
WHERE S.VIDST ='Холодная вода' AND S.STATUS= 'Действует' AND G.DPL51 <> 0 AND G.DATRAS = CTOD('05.04.2018') ;
INTO CURSOR C41

select DISTINCT A.AID, A.LIC, A.LICERC, A.Street, A.Home, A.Flat, "Холодная вода ФВ" AS P1 , G.PL51 AS P2 ;
FROM dataa A LEFT JOIN dat_s_a S ON A.AID = S.AID LEFT JOIN datag G ON A.AID = G.AID ;
WHERE S.VIDST ='Холодная вода' AND S.STATUS= 'Действует' AND G.PL51 > 0 AND G.DATRAS = CTOD('05.04.2018') ;
INTO CURSOR C42

select DISTINCT A.AID, A.LIC, A.LICERC, A.Street, A.Home, A.Flat, "Холодная вода ФВ" AS P1 , G.DPL51 AS P2 ;
FROM dataa A LEFT JOIN dat_s_a S ON A.AID = S.AID LEFT JOIN datag G ON A.AID = G.AID ;
WHERE S.VIDST ='Холодная вода' AND S.STATUS= 'Действует' AND G.DPL51 <> 0 AND G.DATRAS = CTOD('05.03.2018') ;
INTO CURSOR C31

select DISTINCT A.AID, A.LIC, A.LICERC, A.Street, A.Home, A.Flat, "Холодная вода ФВ" AS P1 , G.PL51 AS P2 ;
FROM dataa A LEFT JOIN dat_s_a S ON A.AID = S.AID LEFT JOIN datag G ON A.AID = G.AID ;
WHERE S.VIDST ='Холодная вода' AND S.STATUS= 'Действует' AND G.PL51 > 0 AND G.DATRAS = CTOD('05.03.2018') ;
INTO CURSOR C32

select DISTINCT A.AID, A.LIC, A.LICERC, A.Street, A.Home, A.Flat, "Холодная вода ФВ" AS P1 , G.DPL51 AS P2 ;
FROM dataa A LEFT JOIN dat_s_a S ON A.AID = S.AID LEFT JOIN datag G ON A.AID = G.AID ;
WHERE S.VIDST ='Холодная вода' AND S.STATUS= 'Действует' AND G.DPL51 <> 0 AND G.DATRAS = CTOD('05.02.2018') ;
INTO CURSOR C21

select DISTINCT A.AID, A.LIC, A.LICERC, A.Street, A.Home, A.Flat, "Холодная вода ФВ" AS P1 , G.PL51 AS P2 ;
FROM dataa A LEFT JOIN dat_s_a S ON A.AID = S.AID LEFT JOIN datag G ON A.AID = G.AID ;
WHERE S.VIDST ='Холодная вода' AND S.STATUS= 'Действует' AND G.PL51 > 0 AND G.DATRAS = CTOD('05.02.2018') ;
INTO CURSOR C22

select DISTINCT A.AID, A.LIC, A.LICERC, A.Street, A.Home, A.Flat, "Холодная вода ФВ" AS P1 , G.DPL51 AS P2 ;
FROM dataa A LEFT JOIN dat_s_a S ON A.AID = S.AID LEFT JOIN datag G ON A.AID = G.AID ;
WHERE S.VIDST ='Холодная вода' AND S.STATUS= 'Действует' AND G.DPL51 <> 0 AND G.DATRAS = CTOD('05.01.2018') ;
INTO CURSOR C11

select DISTINCT A.AID, A.LIC, A.LICERC, A.Street, A.Home, A.Flat, "Холодная вода ФВ" AS P1 , G.PL51 AS P2 ;
FROM dataa A LEFT JOIN dat_s_a S ON A.AID = S.AID LEFT JOIN datag G ON A.AID = G.AID ;
WHERE S.VIDST ='Холодная вода' AND S.STATUS= 'Действует' AND G.PL51 > 0 AND G.DATRAS = CTOD('05.01.2018') ;
INTO CURSOR C12

* ==========================================================
* ЗАПРОС 2 Холодная вода
* ==========================================================

SELECT C1.P1 ;
, C1.AID ;
, C1.LIC ;
, C1.LICERC;
, C1.Street;
, C1.Home;
, C1.Flat;
, THISFORM.metod_tip(C1.P2) AS "M_ИЮЛЬ" ;
, THISFORM.metod_tip(C61.P2) AS "M_ИЮНЬ" ;
, THISFORM.metod_tip (C62.P2) AS "M_ИЮНЬ_Н" ;
, THISFORM.metod_tip (C51.P2) AS "M_МАЙ" ;
, THISFORM.metod_tip (C52.P2) AS "M_МАЙ_Н" ;
, THISFORM.metod_tip (C41.P2) AS "M_АПР" ;
, THISFORM.metod_tip (C42.P2) AS "M_АПР_Н" ;
, THISFORM.metod_tip (C31.P2) AS "M_МАРТ" ;
, THISFORM.metod_tip (C32.P2) AS "M_МАРТ_Н" ;
, THISFORM.metod_tip (C21.P2) AS "M_ФЕВ" ;
, THISFORM.metod_tip (C22.P2) AS "M_ФЕВ_Н" ;
, THISFORM.metod_tip (C11.P2) AS "M_ЯНВ" ;
, THISFORM.metod_tip (C12.P2) AS "M_ЯНВ_Н" ;
FROM C1 ;
LEFT JOIN C61 ON C1.AID = C61.AID ;
LEFT JOIN C62 ON C1.AID = C62.AID ;
LEFT JOIN C51 ON C1.AID = C51.AID ;
LEFT JOIN C52 ON C1.AID = C52.AID ;
LEFT JOIN C41 ON C1.AID = C41.AID ;
LEFT JOIN C42 ON C1.AID = C42.AID ;
LEFT JOIN C31 ON C1.AID = C31.AID ;
LEFT JOIN C32 ON C1.AID = C32.AID ;
LEFT JOIN C21 ON C1.AID = C21.AID ;
LEFT JOIN C22 ON C1.AID = C22.AID ;
LEFT JOIN C11 ON C1.AID = C11.AID ;
LEFT JOIN C12 ON C1.AID = C12.AID ;
ORDER BY C1.AID INTO CURSOR GLOB2

* ==========================================================
* ЗАПРОС 3 Холодная вода и горячая
* ==========================================================

SELECT * FROM GLOB1 ;
UNION ALL ;
SELECT * FROM GLOB2 ;
ORDER BY AID

*=========
* Methods

Metod_tip

LPARAMETERS val AS Variant

TIP = VARTYPE(val )
VOZVRAT = 0

DO CASE
Case TIP = 'N'
VOZVRAT = val

OTHERWISE
VOZVRAT = 0

ENDCASE

RETURN VOZVRAT
Старые новости
Новые новости


 


Category:  iT  |   Comments: 0 для печати создать PDF документ из этой новости
Добро пожаловать,
Пользователь:

Пароль:



[ ]
Дата / Время
 
RSS трансляции
Наши новости могут транслироваться, используя rss.
rss1.0
rss2.0
rdf
Голосование


Оцените пожалуйста мой сайт


Отличный

Неплохой

Ужас



Добавил Admin
Голоса: 39
Предыдущие голосования



ФК Сатурн
Случайная картинка
Название: -пусто-
Описание: -пусто-
Каталог/Раздел: Osetr2019

-пусто-
Название: -пусто-
Описание: -пусто-
Каталог/Раздел: Voskresensk

-пусто-
Название: -пусто-
Описание: -пусто-
Каталог/Раздел: Voskresensk

-пусто-
Название: -пусто-
Описание: -пусто-
Каталог/Раздел: Osetr2019

-пусто-
Название: памятник Кириллу и Мефодию
Описание: -пусто-
Каталог/Раздел: Kolomna

памятник Кириллу и Мефодию
Название: -пусто-
Описание: -пусто-
Каталог/Раздел: Voskresensk

-пусто-
Название: -пусто-
Описание: -пусто-
Каталог/Раздел: Dubrovichy

-пусто-
Название: -пусто-
Описание: -пусто-
Каталог/Раздел: Dubrovichy

-пусто-
Название: -пусто-
Описание: -пусто-
Каталог/Раздел: Rostov

-пусто-
Название: -пусто-
Описание: -пусто-
Каталог/Раздел: Voskresensk

-пусто-
Новости за 2024
ПнВтСрЧтПтСбВс
1234567
891011121314
15161718192021
22232425262728
2930 
 
Архив
Яндекс.Метрика

Сколько дней блогу
Статистика
Эта страница сегодня ...
всего: 3
уникальных: 3

Эта страница когда-либо ...
всего: 45859
уникальных: 30175

Сайт...
всего: 78349
уникальных: 43462

cf9a9b4bec8034b6
© 2002-2012 e107 Inc. All Rights Reserved. © e107 Club, 2004-2012. All Rights Reserved.