有三張表如下，我的SQL該怎么寫才能查詢到二樓的結果
uj5u.com熱心網友回復：
uj5u.com熱心網友回復：
要什么表？沒有說明uj5u.com熱心網友回復：
二樓在哪兒？uj5u.com熱心網友回復：
case I of1: field1:= '材料費';
2: field1 :='機械費;
3: field1 :='人工費';
end;
sql1.text :='select 日期,'+ field1 + ''' from table1 ';
sql1.open;
uj5u.com熱心網友回復：
樓主想實作的效果是不是這樣？
因為你原來沒有列出欄位名，姑且認為是兩個欄位ADate和Afee吧，至于材料費、人工費等等，我認為應該是類別，定義欄位為AType
那么查詢的第一個表就應該是
SELECT ADate, AFee FROM Table1 WHERE AType='材料費'；
SELECT ADate, AFee FROM Table1 WHERE AType='機械費'；
SELECT ADate, AFee FROM Table1 WHERE AType='人工費'；
那么在這個基礎之上想實作第二個表的結果，就得組合了。
SELECT ADate, SUM(AFee1) AS 材料費, SUM(AFee2) AS 機械費, SUM(AFee3) AS 人工費 FROM
(
SELECT ADate, AFee AS AFee1, 0 AS AFee2, 0 AS AFee3 FROM Table1 WHERE AType='材料費'
UNION ALL
SELECT ADate, 0 AS AFee1, AFee AS AFee2, 0 AS AFee3 FROM Table1 WHERE AType='機械費'
UNION ALL
SELECT ADate, 0 AS AFee1, 0 AS AFee2, AFee AS AFee3 FROM Table1 WHERE AType='人工費'
) A
GROUP BY ADate
uj5u.com熱心網友回復：
以日期為索引主細表組合就好。uj5u.com熱心網友回復：
create view t5 as select a.日期,nvl(a.材料費,0) c1,nvl(b.機械費) c2 from t1 a full join t2 b on a.日期=b.日期;create view t6 as select a.日期,a.c1,a.c2 ,nvl(b.人工費) c3 from t5 a full join t3 b on a.日期=b.日期;
select 日期,sum(c1)+sum(c2)+sum(c3) t7 from t6 group by 日期 ;
uj5u.com熱心網友回復：
兩表連接,即可查出uj5u.com熱心網友回復：
兩年前的貼子還沒有關閉？問題是二樓在哪兒？
uj5u.com熱心網友回復：
SELECT dbo.a.日期, dbo.a.材料費, dbo.b.機械費, dbo.c.人工費FROM dbo.a INNER JOIN
dbo.b ON dbo.a.日期 = dbo.b.日期 INNER JOIN
dbo.c ON dbo.a.日期 = dbo.c.日期
uj5u.com熱心網友回復：
SELECT a.日期,
ISNULL (b.sCLF,0) AS CLF2,
ISNULL (c.sJXF,0) AS JXF2,
ISNULL (d.sRGF,0) AS RGF2
FROM(
SELECT 日期 FROM tbl_CLF UNION
SELECT 日期 FROM tbl_JXF UNION
SELECT 日期 FROM tbl_RGF
)a
LEFT JOIN (
SELECT 日期,SUM (材料費) AS sCLF FROM tbl_CLF GROUP BY 日期
)b ON a.日期=b.日期
LEFT JOIN (
SELECT 日期,SUM (機械費) AS sJXF FROM tbl_JXF GROUP BY 日期
)c ON a.日期=c.日期
LEFT JOIN (
SELECT 日期,SUM (人工費) AS sRGF FROM tbl_RGF GROUP BY 日期
)d ON a.日期=d.日期
ORDER BY a.日期
uj5u.com熱心網友回復：
三年前的帖子了，
uj5u.com熱心網友回復：
兩年前的貼子還沒有關閉？問題是二樓在哪兒？
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/9633.html
標籤：數據庫相關
下一篇：求解