如何實現%比統計 |
尚未結案
|
suntao008
一般會員 發表:70 回覆:38 積分:21 註冊:2003-10-19 發送簡訊給我 |
Dear all: 我現在資料庫結構如下 : line_name model_name QTY OQA
2A FT500 1200 100
2A FT500 1300 150
2C FT120 1000 90
2B FT130 1000 90 想運行後得到結果如下: line_name Model_name Qty OQA OK
2A FT500 2500 250 10%
2C FT120 1000 90 9%
2B FT130 1000 90 9%
total 3500 430 12.3% 我現在只能運行 select line_name sum(qty),sum(oqa) from table group by line_name 以上不足敬請大大們指教! 向你學習!
|
Mickey
版主 發表:77 回覆:1882 積分:1390 註冊:2002-12-11 發送簡訊給我 |
suntao008 你好:
試試看 :
SELECT line_name,Model_name,SUM(QTY) AS QTY1,SUM(OQA) AS OQA1, OQA1/QTY1*100 AS PERCENT1 FROM TABLE2 GROUP BY line_name,Model_name UNION SELECT 'Total','',SUM(QTY) AS QTY1,SUM(OQA) AS OQA1, OQA1/QTY1*100 AS PERCENT1 FROM TABLE2 |
suntao008
一般會員 發表:70 回覆:38 積分:21 註冊:2003-10-19 發送簡訊給我 |
各位大大,感謝你指教! 經修改後如下: query1.Close;
query1.SQL.Clear;
query1.sql.add('select line_name,Model_name,sum(qty) as qty1,sum(oqa) as oqa1 from table group by line_name,Model_name');
query1.Open; 以上可以運行: 但加入下面語句不可以運行: OQA1/QTY1*100 AS PERCENT1 且下面的語句不知如何使用 UNION
SELECT 'Total','',SUM(QTY) AS QTY1,SUM(OQA) AS OQA1,
OQA1/QTY1*100 AS PERCENT1
FROM TABLE2 以上敬請指教! 向你學習!
|
Fishman
尊榮會員 發表:120 回覆:1949 積分:2163 註冊:2006-10-28 發送簡訊給我 |
Hi suntao008,
SELECT LINE_NAME, MODEL_NAME, SUM(QTY) AS QTY, SUM(OQA) AS OQA, SUM(OQA)/SUM(QTY)*100 AS PERCENTAGE FROM TABLE1 GROUP BY LINE_NAME, MODEL_NAME UNION ALL SELECT 'TOTAL', '', SUM(QTY) AS QTY, SUM(OQA) AS OQA, SUM(OQA)/SUM(QTY)*100 AS PERCENTAGE FROM TABLE1---------------------------------- 小弟才疏學淺,若有謬誤尚請不吝指教 ---------------------------------- 發表人 -
------
Fishman |
suntao008
一般會員 發表:70 回覆:38 積分:21 註冊:2003-10-19 發送簡訊給我 |
|
Fishman
尊榮會員 發表:120 回覆:1949 積分:2163 註冊:2006-10-28 發送簡訊給我 |
Hi suntao008, TQuery 下有一個 SQL 屬性,裡面即可定義該 TQuery 的 SQL Command
若是要動態組合 SQL Command,可以
procedure TForm1.Button1Click(Sender: TObject); begin Query1.Close; Query1.SQL.Clear; Query1.SQL.Add('SELECT LINE_NAME,' + #13 + ' MODEL_NAME,' + #13 + ' SUM(QTY) AS QTY,' + #13 + ' SUM(OQA) AS OQA,' + #13 + ' SUM(OQA)/SUM(QTY)*100 AS PERCENTAGE' + #13 + 'FROM TABLE1' + #13 + 'GROUP BY' + #13 + ' LINE_NAME,' + #13 + ' MODEL_NAME' + #13 + 'UNION ALL' + #13 + 'SELECT ' + QuotedStr('TOTAL') + ',' + #13 + ' ' + QuotedStr('') + ',' + #13 + ' SUM(QTY) AS QTY,' + #13 + ' SUM(OQA) AS OQA,' + #13 + ' SUM(OQA)/SUM(QTY)*100 AS PERCENTAGE' + #13 + 'FROM TABLE1'); Query1.Open; end;執行結果如下 測試環境 D7 + SQL Server 2000 ---------------------------------- 小弟才疏學淺,若有謬誤尚請不吝指教 ----------------------------------
------
Fishman |
本站聲明 |
1. 本論壇為無營利行為之開放平台,所有文章都是由網友自行張貼,如牽涉到法律糾紛一切與本站無關。 2. 假如網友發表之內容涉及侵權,而損及您的利益,請立即通知版主刪除。 3. 請勿批評中華民國元首及政府或批評各政黨,是藍是綠本站無權干涉,但這裡不是政治性論壇! |