sql查询从三个表中获取数据 [英] sql query to get data from three table
问题描述
你好朋友....
i有三张表格如下
创建 表 pds(
edatetime datetime 不 null ,
caustic decimal ( 10 , 5 ),
比率 decimal ( 10 , 5 ),
solid 十进制( 10 , 5 ),
)
创建 table ast_wash_gpl_soda(
edatetime datetime 不 null ,
nt_6o 十进制( 10 , 5 ),
nt_6u decimal ( 10 , 5 ),
)
创建 table dms1
(
edatetime datetime 不 null ,
sodagpldms < span class =code-keyword> decimal ( 10 , 5 ),
soliddms 十进制( 10 , 5 ),
)
这三张桌子之间没有任何关系。它们只包含edatetime字段。
我想生成报告,其中用户将提供月份号和年份作为参数。我需要从这三个表中获取数据(每个字段的平均值)。它应该显示每月中每个字段的平均天数,如下所示
date caustic_average nt_6o_average soliddms_average
1-apr-2014 25 。 00 23 . 00 18 。 32
2-apr-2014 89 。 65 65 。 47 56 。 21
3-apr-2014 25 。 95 65 。 65 51 。 71 跨度>
。
。
。
31-apr-2014 95 。 36 68 。 47 51 。 25
caustic_avarage是该月和该年的平均值。
即25.00是一个刻薄的平均值对于1-apr-2014。
nt_6o_average是该月和该年的平均值。
即65.47是2的平均值nt_6o -apr-2014。
soliddms_average是该月和该年的平均值。
即51.71是2014年3月3日的平均值。
i希望在sql server中进行此类查询。
plz help
SELECT CONVERT ( varchar ( 50 ),pds.edatetime,< span class =code-digit> 106 ),AVG(pds.caustic) AS caustic_average,
AVG(ast_wash_gpl_soda.nt_6o)< span class =code-keyword> AS nt_6o_average,AVG(dms1.soliddms) AS soliddms_average
FROM ast_wash_gpl_soda INNER JOIN
dms1 ON CONVERT ( varchar ( 50 ),ast_wash_gpl_soda.edatetime,106 )= CONVERT ( varchar ( 50 ),dms1.edatetime, 106 ) INNER JOIN
pds ON CONVERT ( varchar ( 50 ),dms1.edatetime, 106 )= CONVERT ( varchar ( 50 ),pds.edatetime, 106 )
GROUP BY pds.edatetime
您可以使用日期范围的条件
其中 pds.edatetime cast之间( @Month as varchar ( 2 ))+ ' / 01 /' + cast( @Year as varchar ( 4 ))和 DATEADD(DAY,-1,DATEADD(MONTH, 1 ,CAST(强制转换) ( @ Month as varchar ( 2 ))+ ' / 01 /' + cast( @ Year as varchar (< span class =code-digit> 4 )) as datetime )))
尝试这个
SELECT PD.edatetime,AVG(PD.caustic) AS CAUSTIC,AVG(AW.nt_6o) AS NT_60,AVG(AW.nt_6u) AS NT6U,
AVG(DM.sodagpldms) AS SODA
FROM pds PD
INNER < span class =code-keyword> JOIN ast_wash_gpl_soda AS AW ON AW.edatetime = PD.edatetime
INNER JOIN dms1 AS DM ON DM.edatetime = PD.edatetime
GROUP BY PD.edatetime
谢谢
MANGESH
HI
添加条件
AS MONTH(PD.edatetime)='8',年份(PD.edatetime) )='2014'
谢谢
MANGESH
hello friends ....
i have three tables as follows
create table pds(
edatetime datetime not null,
caustic decimal(10,5),
ratio decimal(10,5),
solid decimal(10,5),
)
create table ast_wash_gpl_soda(
edatetime datetime not null,
nt_6o decimal(10,5),
nt_6u decimal(10,5),
)
create table dms1
(
edatetime datetime not null,
sodagpldms decimal(10,5),
soliddms decimal(10,5),
)
all three tables have no reationship among them. they just contained edatetime field as common.
i want to generate report in which user will provide month no and year as parameter. i need to fetch data (average of every fields) from these three tables. it should show average of day of every field in month such as follows
date caustic_average nt_6o_average soliddms_average
1-apr-2014 25.00 23.00 18.32
2-apr-2014 89.65 65.47 56.21
3-apr-2014 25.95 65.65 51.71
.
.
.
31-apr-2014 95.36 68.47 51.25
caustic_avarage is average for day in that month and year.
i.e 25.00 is a caustic average for 1-apr-2014.
nt_6o_average is average for day in that month and year.
i.e 65.47 is a nt_6o average for 2-apr-2014.
soliddms_average is average for day in that month and year.
i.e 51.71 is a soliddms average for 3-apr-2014.
i wanted to make such query in sql server.
plz help
SELECT CONVERT(varchar(50), pds.edatetime, 106) , AVG(pds.caustic) AS caustic_average, AVG(ast_wash_gpl_soda.nt_6o) AS nt_6o_average, AVG(dms1.soliddms) AS soliddms_average FROM ast_wash_gpl_soda INNER JOIN dms1 ON CONVERT(varchar(50), ast_wash_gpl_soda.edatetime, 106) = CONVERT(varchar(50), dms1.edatetime, 106) INNER JOIN pds ON CONVERT(varchar(50), dms1.edatetime, 106) = CONVERT(varchar(50), pds.edatetime, 106) GROUP BY pds.edatetime
You can use where condition for date range
Where pds.edatetime between cast(@Month as varchar(2)) + '/01/' + cast(@Year as varchar(4)) and DATEADD(DAY, -1, DATEADD(MONTH, 1, CAST(cast(@Month as varchar(2)) + '/01/' + cast(@Year as varchar(4)) as datetime)))
HI
TRY THIS
SELECT PD.edatetime,AVG(PD.caustic ) AS CAUSTIC ,AVG(AW.nt_6o) AS NT_60,AVG(AW.nt_6u) AS NT6U, AVG(DM.sodagpldms) AS SODA FROM pds PD INNER JOIN ast_wash_gpl_soda AS AW ON AW.edatetime=PD.edatetime INNER JOIN dms1 AS DM ON DM.edatetime=PD.edatetime GROUP BY PD.edatetime
THANKS
MANGESH
HI
ADD WHERE CONDITION
AS MONTH(PD.edatetime)='8',YEAR(PD.edatetime)='2014'
THANKS
MANGESH
这篇关于sql查询从三个表中获取数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!