从两个表中选择一个XML。

I'm trying to execute the following SQL:

我正在尝试执行以下SQL语句:

SELECT * FROM Table2
INNER JOIN
    (SELECT * FROM Table1
    INNER JOIN
    (SELECT MAX(DateTime) AS LastMeasurement, MeasurementId as LastMeasurementId
    FROM Table1 GROUP BY MeasurementId) as lastMeasurement
    ON (Table1.MeasurementId = lastMeasurement.LastMeasurementId) 
    AND (Table1.DateTime = lastMeasurement.LastMeasurement)) as hLastMeasurement
ON Table2.Id = hLastMeasurement.Id
ORDER BY Table2.Id ASC

This works just fine, but because I want to get the result in XML format and had to cast the geography column, I've changed the first line to:

这样做很好，但是因为我想要得到XML格式的结果，并且不得不抛出地理专栏，我已经将第一行更改为:

SELECT Name, Description, CAST(Location as nvarchar(MAX)) FROM Table2

and added this line to the end:

最后加上这一行:

FOR XML RAW ('Object'), ROOT ('Objects'), ELEMENTS

Now I'm getting the XML, but with data only from Table2.
Any advice will be welcome.

现在我得到了XML，但是只有Table2的数据。欢迎提出任何建议。

1 个解决方案

#1

Obviously , if you want to display data from other tables then you have to select these columns like this way. IO normally use MYSQL, so this is the way I normally use.But if you are using oracle or other database, then you have to select proper alias names to display custom columns that you wish.see basic example for join using sql server here

显然，如果您想显示来自其他表的数据，那么您必须像这样选择这些列。IO通常使用MYSQL，所以这是我通常使用的方式。但是，如果您正在使用oracle或其他数据库，那么您必须选择合适的别名来显示您希望的自定义列。在这里可以看到使用sql server进行连接的基本示例

SELECT Table2.Name,Table2.Description,lastMeasurement.MeasurementId 

FROM Table2 t2
    INNER JOIN
        (SELECT * FROM Table1
        INNER JOIN
        (SELECT MAX(DateTime) AS LastMeasurement, MeasurementId as LastMeasurementId
        FROM Table1 GROUP BY MeasurementId) as lastMeasurement
        ON (Table1.MeasurementId = lastMeasurement.LastMeasurementId) 
        AND (Table1.DateTime = lastMeasurement.LastMeasurement)) as hLastMeasurement
    ON Table2.Id = hLastMeasurement.Id
    ORDER BY Table2.Id ASC