请问这两句联合查询如何写？

[ivanl]

有两个表，一个销售表data_saledata，另外一个采购表data_buy

这句是按月查出每个月销售总金额并按年月排序：
SELECT DatePart('yyyy',saledate) AS colyear, DatePart('m',saledate) AS colmonth, sum(lastamount) AS saleamount
FROM data_saledata
GROUP BY DatePart('yyyy',saledate), DatePart('m',saledate)
ORDER BY DatePart('yyyy',saledate) DESC , DatePart('m',saledate);

以下这句是按月查出每个月采购总金额并按年月排序：
SELECT DatePart('yyyy',purchasedate) AS colyear, DatePart('m',purchasedate) AS colmonth, sum(amount) AS purchaseamount
FROM data_buy
GROUP BY DatePart('yyyy',purchasedate), DatePart('m',purchasedate)
ORDER BY DatePart('yyyy',purchasedate) DESC , DatePart('m',purchasedate);

我现在想把两个查询的结果显示在一起,并按年月排序，请问SQL命令如何写，谢谢

[---涛声依旧---]

select t1.colyear,t1.colmonth,t1.saleamount,t2.purchaseamount (SELECT DatePart('yyyy',saledate) AS colyear, DatePart('m',saledate) AS colmonth, sum(lastamount) AS saleamount FROM data_saledata GROUP BY DatePart('yyyy',saledate), DatePart('m',saledate)) t1 left join (SELECT DatePart('yyyy',purchasedate) AS colyear, DatePart('m',purchasedate) AS colmonth, sum(amount) AS purchaseamount FROM data_buy GROUP BY DatePart('yyyy',purchasedate), DatePart('m',purchasedate)) t2 on t1.colyear=t2.colyear and t1.colmonth=t2.colmonth order by colyear desc,colmonth

[Ny-6000]

使用inner join也行吧. 关联条件,就是 年=年,月=月 ------ 现实上,在某一个月内,数据为空的可能性几乎不存在,可以不考虑

[szm341]

将两个结果集去掉order by，然后用full join关联，最后再order by

[幸运的意外]

SELECT A.colyear, A.colmonth, saleamount, purchaseamount FROM( SELECT DatePart('yyyy',saledate) AS colyear, DatePart('m',saledate) AS colmonth, sum(lastamount) AS saleamount FROM data_saledata GROUP BY DatePart('yyyy',saledate), DatePart('m',saledate)) JOIN( SELECT DatePart('yyyy',purchasedate) AS colyear, DatePart('m',purchasedate) AS colmonth, sum(amount) AS purchaseamount FROM data_buy GROUP BY DatePart('yyyy',purchasedate), DatePart('m',purchasedate)) ORDER BY colyear, colmonth

[szivanl]

引用 2 楼 acupofnescafe 的回复:

SELECT A.colyear, A.colmonth, saleamount, purchaseamount FROM( SELECT DatePart('yyyy',saledate) AS colyear, DatePart('m',saledate) AS colmonth, sum(lastamount) AS saleamount FROM data_……

貌似不行。。。