我有以下语句 select cus_no as 客户，os_no as 订单号,prd_no as 产品,qty as 数量,up as 单价,amt as 金额 from order union all select cust as 客户，'' as 订单号,'' as 产品,sum(qty) as 数量,sum(up) as 单价,sum(amt) as 金额 from order union all group by cus_no 可是我不想让单价也加总，但我用'' as 单价时系统报错，请教我应该处理，请前辈们回复，谢谢！

Hi chamstar,    用 0 as 單價

select         cus_no as 客戶,
        os_no as 訂單號,
        prd_no as 產品,
        qty as 數量,
        up as 單價,
        amt as 金額
from    order 
union all
select  cust as 客戶,
        '' as 訂單號,
        '' as 產品,
        sum(qty) as 數量,
        0 as 單價,
        sum(amt) as 金額
from    order  
group by
        cus_no 

---------------------------------- 小弟才疏學淺，若有謬誤尚請不吝指教 ----------------------------------

------
Fishman

这个我知道，能不能找到不用0的方式，或者说如果用0的话，在显示出来的结果上不显示0的数字？

Hi chamstar,    兩個 Query 中的 Type 必須一致，也可以轉換第一個 up 為 char 啊！

select         cus_no as 客戶,
        os_no as 訂單號,
        prd_no as 產品,
        qty as 數量,
        to_char(up) as 單價, --> 轉會為 char，視你用的 db 而有不同函數
        amt as 金額
from    order 
union all
select  cust as 客戶,
        '' as 訂單號,
        '' as 產品,
        sum(qty) as 數量,
        '' as 單價,
        sum(amt) as 金額
from    order  
group by
        cus_no 

---------------------------------- 小弟才疏學淺，若有謬誤尚請不吝指教 ----------------------------------

------
Fishman

Hi chamstar,    另一種寫法，只是不知你的 Databse 是否接受？

select         cus_no as 客戶,
        os_no as 訂單號,
        prd_no as 產品,
        qty as 數量,
        up as 單價,
        amt as 金額
from    order 
union all
select  cust as 客戶,
        '' as 訂單號,
        '' as 產品,
        sum(qty) as 數量,
        to_number(NULL) as 單價, --> convert null to number
        sum(amt) as 金額
from    order  
group by
        cus_no 

---------------------------------- 小弟才疏學淺，若有謬誤尚請不吝指教 ----------------------------------

------
Fishman

引言: 这个我知道，能不能找到不用0的方式，或者说如果用0的话，在显示出来的结果上不显示0的数字？

用 null as 單價 如何?

谢谢 Fishman和wuabc，两位的说法方法都可以，谢谢了。w