请教sql问题 |
答題得分者是:wuabc
|
chamstar
一般會員 ![]() ![]() 發表:24 回覆:36 積分:12 註冊:2004-10-16 發送簡訊給我 |
我有以下语句
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 单价时系统报错,请教我应该处理,请前辈们回复,谢谢!
|
Fishman
尊榮會員 ![]() ![]() ![]() ![]() ![]() ![]() 發表:120 回覆:1949 積分:2163 註冊:2006-10-28 發送簡訊給我 |
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 |
chamstar
一般會員 ![]() ![]() 發表:24 回覆:36 積分:12 註冊:2004-10-16 發送簡訊給我 |
|
Fishman
尊榮會員 ![]() ![]() ![]() ![]() ![]() ![]() 發表:120 回覆:1949 積分:2163 註冊:2006-10-28 發送簡訊給我 |
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 |
Fishman
尊榮會員 ![]() ![]() ![]() ![]() ![]() ![]() 發表:120 回覆:1949 積分:2163 註冊:2006-10-28 發送簡訊給我 |
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 |
wuabc
初階會員 ![]() ![]() 發表:6 回覆:60 積分:33 註冊:2002-10-28 發送簡訊給我 |
|
chamstar
一般會員 ![]() ![]() 發表:24 回覆:36 積分:12 註冊:2004-10-16 發送簡訊給我 |
本站聲明 |
1. 本論壇為無營利行為之開放平台,所有文章都是由網友自行張貼,如牽涉到法律糾紛一切與本站無關。 2. 假如網友發表之內容涉及侵權,而損及您的利益,請立即通知版主刪除。 3. 請勿批評中華民國元首及政府或批評各政黨,是藍是綠本站無權干涉,但這裡不是政治性論壇! |