請問 sql中union語法的使用 (firebird資料庫)

edited 十月 2013 in 資料庫
資料庫為 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
table1.jpg

第二個語法選出的table
table2.jpg


這兩個語法都是可以run的

但是union在一起就不行了

我的目標是要把這兩個選出來的 union在一起 然後再以AMT這個欄位排序

選出前三名 不知道怎麼寫比較好? SQL我不太熟

似乎是 union 跟 group by 跟 order by 有些衝突?

原始討論: http://twpug.net/x/modules/newbb/viewtopic.php?topic_id=6947

評論

  • edited 九月 2012
    這個問題解決了 應該是選出來union的 欄位的資料型態要完全一樣
    Varchar(20) 跟 varchar(50) 就不行

    現在想問另一個問題
    =======================================================
    SELECT a.CUST_ID, a.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

    UNION

    SELECT C.MEMBER_ID, C.MEMBER_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
    =======================================================

    這段語法已經ok了
    但卡在要如何再從這個選出來 union的 table 再選出前十名

    我用 select first 10 from ( 上述一卡車 )
    會出錯
  • 我沒用過 firebird 1.5
    上面已說明 "第一個語法"是可以run的
    所以 sql 應該是

    select first 10 x1,x2,x3
    from
    (
    SELECT a.CUST_ID as x1, a.CUST_NAME as x2,sum(b.total_money) AS x3

    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

    UNION

    SELECT C.MEMBER_ID as x1, C.MEMBER_NAME as x2,sum(D.SAL_AMT) AS as x3

    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
    ) as temp
    order by 3 desc

    以上 sql 未測,參考看看
Sign In or Register to comment.