資料庫為 firebird 1.5
我總共有四個table
其中 CUSTOMER 跟 MEMBER 是類似的table
SAL_MASTER 跟 POS_MASTER 是類似的table
第一個語法
======================================================
SELECT first 10 a.CUST_ID as CUST_ID, a.CUST_NAME as CUST_NAME,sum(b.total_money) AS AMT
FROM CUSTOMER a,SAL_MASTER b
where a.CUST_ID=b.CUST_ID and b.BILL_DATE like '2012/09%'
group by a.CUST_ID, a.CUST_NAME
order by 3 desc
======================================================
第二個語法
======================================================
SELECT first 10 c.MEMBER_ID as CUST_ID, c.MEMBER_NAME as CUST_NAME,sum(d.SAL_AMT) AS AMT
FROM MEMBER c,POS_MASTER d
where c.MEMBER_ID=d.MEMBER_ID and d.SAL_DATE like '2012/09%'
group by c.MEMBER_ID, c.MEMBER_NAME
order by 3 desc
======================================================
第一個語法選出的table
第二個語法選出的table
這兩個語法都是可以run的
但是union在一起就不行了
我的目標是要把這兩個選出來的 union在一起 然後再以AMT這個欄位排序
選出前三名 不知道怎麼寫比較好? SQL我不太熟
似乎是 union 跟 group by 跟 order by 有些衝突?