请问下各位大神，这个SQL该怎么写？

[csdn987q]

有三张表如下，我的SQL该怎么写才能查询到二楼的结果

[lyhoo163]

[lyhoo163]

要什么表？没有说明

[kenlewis]

二楼在哪儿？

[whb00120012]

case I of 1: field1:= '材料费'; 2: field1 :='机械费; 3: field1 :='人工费'; end; sql1.text :='select 日期,'+ field1 + ''' from table1 '; sql1.open;

[kenlewis]

楼主想实现的效果是不是这样？


因为你原来没有列出字段名，姑且认为是两个字段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

[sixgj]

以日期为索引主细表组合就好。

[whb00120012]

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 日期 ;

[智汇探长]

两表连接,即可查出

[xrbaa]

两年前的贴子还没有关闭？ 问题是二楼在哪儿？

[中药中药诊所管理软件]

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.日期

[dreamzhjX]

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.日期

[qqq112]

三年前的帖子了，

[sixgj]

两年前的贴子还没有关闭？ 问题是二楼在哪儿？