Comments 6
Спасибо, однако меня более чем устраивает встроенный в SQLyog (Enterprise-версии).
в тривиальном access-запросе
TRANSFORM Last(b.RES) AS [Last-RES]
SELECT q1.TIP_NP,
q1.NAME_NP
FROM (SELECT geoobject_DBF.TIP_NP ,
geoobject_DBF.NAME_NP,
result_dbf.ID_P ,
MAX(result_dbf.ID_TIME) AS [Max-ID_TIME]
FROM result_dbf
INNER JOIN geoobject_DBF
ON result_dbf.ID_GEOOBJE = geoobject_DBF.ID_NP
GROUP BY geoobject_DBF.TIP_NP ,
geoobject_DBF.NAME_NP,
result_dbf.ID_P
ORDER BY geoobject_DBF.NAME_NP,
result_dbf.ID_P
) AS q1
INNER JOIN (result_dbf AS b
INNER JOIN geoobject_DBF
ON b.ID_GEOOBJE = geoobject_DBF.ID_NP)
ON (q1.NAME_NP = geoobject_DBF.NAME_NP
)
AND
(
q1.ID_P = b.ID_P
)
AND
(
q1.[Max-ID_TIME] = b.ID_TIME
)
GROUP BY q1.TIP_NP,
q1.NAME_NP
ORDER BY q1.NAME_NP,
q1.ID_P PIVOT q1.ID_P;
<nobr/>
выдало ошибку
SELECT q1.TIP_NP,
q1.NAME_NP
FROM (SELECT geoobject_DBF.TIP_NP ,
geoobject_DBF.NAME_NP,
result_dbf.ID_P ,
MAX(result_dbf.ID_TIME) AS [Max-ID_TIME]
FROM result_dbf
INNER JOIN geoobject_DBF
ON result_dbf.ID_GEOOBJE = geoobject_DBF.ID_NP
GROUP BY geoobject_DBF.TIP_NP ,
geoobject_DBF.NAME_NP,
result_dbf.ID_P
ORDER BY geoobject_DBF.NAME_NP,
result_dbf.ID_P
) AS q1
INNER JOIN (result_dbf AS b
INNER JOIN geoobject_DBF
ON b.ID_GEOOBJE = geoobject_DBF.ID_NP)
ON (q1.NAME_NP = geoobject_DBF.NAME_NP
)
AND
(
q1.ID_P = b.ID_P
)
AND
(
q1.[Max-ID_TIME] = b.ID_TIME
)
GROUP BY q1.TIP_NP,
q1.NAME_NP
ORDER BY q1.NAME_NP,
q1.ID_P PIVOT q1.ID_P;
<nobr/>
выдало ошибку
при том, что поддержка MS Access заявлена
SELECT q1.tip_np,
q1.name_np
FROM (SELECT geoobject_dbf.tip_np,
geoobject_dbf.name_np,
result_dbf.id_p,
Max(result_dbf.id_time) AS [Max-ID_TIME]
FROM result_dbf
INNER JOIN geoobject_dbf
ON result_dbf.id_geoobje = geoobject_dbf.id_np
GROUP BY geoobject_dbf.tip_np,
geoobject_dbf.name_np,
result_dbf.id_p
ORDER BY geoobject_dbf.name_np,
result_dbf.id_p) AS q1
INNER JOIN (result_dbf AS b
INNER JOIN geoobject_dbf
ON b.id_geoobje = geoobject_dbf.id_np)
ON (q1.name_np = geoobject_dbf.name_np)
AND (q1.id_p = b.id_p)
AND (q1.[Max-ID_TIME] = b.id_time)
GROUP BY q1.tip_np,
q1.name_np
ORDER BY q1.name_np,
q1.id_p
а так это уже другой запрос
q1.name_np
FROM (SELECT geoobject_dbf.tip_np,
geoobject_dbf.name_np,
result_dbf.id_p,
Max(result_dbf.id_time) AS [Max-ID_TIME]
FROM result_dbf
INNER JOIN geoobject_dbf
ON result_dbf.id_geoobje = geoobject_dbf.id_np
GROUP BY geoobject_dbf.tip_np,
geoobject_dbf.name_np,
result_dbf.id_p
ORDER BY geoobject_dbf.name_np,
result_dbf.id_p) AS q1
INNER JOIN (result_dbf AS b
INNER JOIN geoobject_dbf
ON b.id_geoobje = geoobject_dbf.id_np)
ON (q1.name_np = geoobject_dbf.name_np)
AND (q1.id_p = b.id_p)
AND (q1.[Max-ID_TIME] = b.id_time)
GROUP BY q1.tip_np,
q1.name_np
ORDER BY q1.name_np,
q1.id_p
а так это уже другой запрос
а вот как работает highlight.hohli.com в режиме SQL
- TRANSFORM Last(b.RES) AS [Last-RES]
- SELECT q1.TIP_NP, q1.NAME_NP
- FROM (SELECT geoobject_DBF.TIP_NP, geoobject_DBF.NAME_NP, result_dbf.ID_P, Max(result_dbf.ID_TIME) AS [Max-ID_TIME]
- FROM result_dbf INNER JOIN geoobject_DBF ON result_dbf.ID_GEOOBJE = geoobject_DBF.ID_NP
- GROUP BY geoobject_DBF.TIP_NP, geoobject_DBF.NAME_NP, result_dbf.ID_P
- ORDER BY geoobject_DBF.NAME_NP, result_dbf.ID_P
- ) AS q1 INNER JOIN (result_dbf AS b INNER JOIN geoobject_DBF ON b.ID_GEOOBJE = geoobject_DBF.ID_NP) ON (q1.NAME_NP = geoobject_DBF.NAME_NP) AND (q1.ID_P = b.ID_P) AND (q1.[Max-ID_TIME] = b.ID_TIME)
- GROUP BY q1.TIP_NP, q1.NAME_NP
- ORDER BY q1.NAME_NP, q1.ID_P
- PIVOT q1.ID_P;
Toad умеет в том числе код в порядок приводить
Sign up to leave a comment.
SQL beautifier