left join reviews on reviews.STATION_ID=stations.id
and reviews.STAT_MONTH=STATS.[MONTH]left join OPERATORS on OPERATORS.ID=reviews.OPERATOR_ID
结果：
2.查询xml数据
select stations.*,
(select stats.*,
(select OPERATORS.*
from OPERATORS
inner join reviews on OPERATORS.ID=reviews.OPERATOR_ID
where reviews.STATION_ID=STATS.STATION_ID
and reviews.STAT_MONTH=STATS.MONTH
for xml path('operator'),type
) operators
from STATS
where STATS.STATION_ID=stations.ID
for xml path('stat'),type
) stats
from stations
for xml path('station'),type
结果：
<station>
<ID>13</ID>
<CITY>Phoenix</CITY>
<STATE>AZ</STATE>
<LAT_N>3.3000000e+001</LAT_N>
<LONG_W>1.1200000e+002</LONG_W>
<stats>
<stat>
<STATION_ID>13</STATION_ID>
<MONTH>1</MONTH>
<TEMP_F>5.7400002e+001</TEMP_F>
<RAIN_I>3.1000000e-001</RAIN_I>
<operators>
<operator>
<ID>50</ID>
<NAME>John "The Fox"</NAME>
<SURNAME>Brown</SURNAME>
</operator>
</operators>
</stat>
<stat>
<STATION_ID>13</STATION_ID>
<MONTH>7</MONTH>
<TEMP_F>9.1699997e+001</TEMP_F>
<RAIN_I>5.1500001e+000</RAIN_I>
<operators>
<operator>
<ID>50</ID>
<NAME>John "The Fox"</NAME>
<SURNAME>Brown</SURNAME>
</operator>
</operators>
</stat>
</stats>
</station>
<station>
<ID>44</ID>
<CITY>Denver</CITY>
<STATE>CO</STATE>
<LAT_N>4.0000000e+001</LAT_N>
<LONG_W>1.0500000e+002</LONG_W>
<stats>
<stat>
<STATION_ID>44</STATION_ID>
<MONTH>1</MONTH>
<TEMP_F>2.7299999e+001</TEMP_F>
<RAIN_I>1.8000001e-001</RAIN_I>
</stat>
<stat>
<STATION_ID>44</STATION_ID>
<MONTH>7</MONTH>
<TEMP_F>7.4800003e+001</TEMP_F>
<RAIN_I>2.1099999e+000</RAIN_I>
<operators>
<operator>
<ID>51</ID>
<NAME>Paul</NAME>
