如何将来自2个表查询的数据放在同一个表行上？ [英] How do I place data from 2 table queries on same table row?

查看：101
本文介绍了如何将来自2个表查询的数据放在同一个表行上？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我正试图从一个输出表格线上的两个独立保龄球队得分。

想想我可以使用连接进行2个单独的选择查询，如下所示。



我加入的输入表是

附表表

日期主页#AWAY TEAM＃
 2017-02-13 6 7 
 2017-02-13 3 2 
 2017-02-13 9 8 
 2017-02-13 4 5 
 2017-02- 13 10 1





数据表：

日期TeamNum Gm1 Gm2 
 12-09-16 4 726 811 
 12-09-16 6 540 573 
 12-09-16 5 450 480 
 12-09-16 7 640 497





希望这不会太混乱。

任何帮助都会受到赞赏。

谢谢< br $> b $ b

我尝试过： 



 SELECT 'DATE'，'Match'，'TEAM'，'HmTeamNo'，'Game 1'，'Game 2'，'Game 3'，' Total'，'Away Tm'
 UNION（选择`MatchDate`，
 FORMAT（MatchNumber，0），
 TEAM，
 FORMAT（HmTeamNo，0），
格式（SUM（gm1），0），
格式（SUM（gm2），0），
格式（SUM（gm3），0），
格式（（SUM（gm1）+ SUM（gm2）+ SUM（gm3）），0）asTtl，
 FORMAT（AwayTeamNo，0）
 FROM Rainbow_Lanes_Matches 
 JOIN Rainbow_Lanes ON 
 week = MatchWeek AND TEAMNUM = HmTeamNo 
 WHERE MatchWeek = 21 
 GROUP BY团队
 ORDER BY MatchNumber 
 
）





如果我重复查询并将hometeam变量更改为awayteam团队并重复加入但却出错，那么认为它应该有效。



 UNION（SELECT`MatchDate`，
 FORMAT（MatchNumber，0），
 TEAM，
 FORMAT（HmTeamNo，0），
格式（SUM（gm1），0），
格式（SUM（gm2），0），
格式（SUM（gm3），0），
格式（（SUM（gm1）+ SUM（gm2）+ SUM（gm3）），0）为Ttl，
 FROM Rainbow_Lanes_Matches 
 JOIN Rainbow_Lanes ON 
周= MatchWeek AND TEAMNUM =  AwayTeamNo  
 WHERE MatchWeek = 21 
 GROUP BY团队
 ORDER BY MatchNumber 
 
）





第二张表包括各队的得分。





输出或结果表如下：

 DATE比赛HmTm Gm1 Gm2 AwayTeam Gm1 Gm2 
 13-02-17 1 4 726 811 9 480 433 
 13-02-17 2 6 540 573 7 640 497

解决方案

我建​​议使用两个子查询，就像你所做的那样。但是，我不使用UNION，而是使用JOIN，因为您需要将它们视为两个单独的数据表，并且只从每个数据表中获取所需的内容。使用UNION可能会显示某些记录的空结果。



尝试此查询。第一个SELECT将定义您的输出，子查询将获得主队和客队所需的数据。



希望这对您有用 - 我不确定您的表结构可能会抛出一些初始语法错误。



 SELECT sub_home.MatchDate，
 sub_home.MatchWeek，
 sub_home.Team，
 sub_home.HomeTeamNo，
 sub_home.Game1，
 sub_home.Game2，
 sub_home.Game3，
 sub_home.TotalScore 
 sub_away .AwayTeamNo，
 sub_away.Game1，
 sub_away.Game2，
 sub_away.Game3，
 sub_away.TotalScore 
 FROM（
 SELECT`MatchDate`，
 FORMAT（MatchNumber，0）AS MatchNumber，
 TEAM，
 FORMAT（HmTeamNo，0）AS HomeTeamNo，
 FORMAT（SUM（gm1），0）AS Game1，
格式（SUM（gm2），0）AS Game2，
格式（SUM（gm3），0）AS Game3，
格式（（SUM（gm1）+ SUM（gm2）+ SUM（gm3） ），0）AS TotalScore 
来自Rainbow_Lanes_Matches 
加入Rainbow_Lanes ON周= MatchWeek和TEAMNUM = HmTeamNo 
 GROUP BY te am 
）sub_home 
 JOIN（
 SELECT`MatchDate`，
 FORMAT（MatchNumber，0）AS MatchNumber，
 TEAM，
 FORMAT（AwayTeamNo，0 ）AS AwayTeamNo，
 FORMAT（SUM（gm1），0）AS Game1，
 FORMAT（SUM（gm2），0）AS Game2，
 FORMAT（SUM（gm3），0）AS Game3，
 FORMAT（（SUM（gm1）+ SUM（gm2）+ SUM（gm3）），0）AS TotalScore 
 FROM Rainbow_Lanes_Matches 
 JOIN Rainbow_Lanes ON week = MatchWeek AND TEAMNUM = AwayTeamNo 
 GROUP BY团队
 ORDER BY MatchNumber 
）sub_away 
 ON sub_home.matchnumber = sub_away.MatchNumber 
 AND sub_home.MatchWeek = sub_away.MatchWeek; 


 SELECTDate，Wk，Match，Hm Team，HDCP，Gm 1，Gm 2， Gm 3，Ttl，Away Team，HDCP，Gm 1，Gm 2，Gm 3，Ttl
 UNION 
 
（ SELECT 
 sub_home.MatchDate，
 sub_home.MatchWeek，
 sub_home.Matchnumber，
 sub_home.HomeTeamNo，
 sub_home.HDCP，
 / * sub_home.Bowler ，* / 
 sub_home.Game1，
 sub_home。 Game2，
 sub_home.Game3，
 sub_home.TotalScore，
 
 sub_away.AwayTeamNo，
 sub_away.HDCP，
 sub_away.Game1，
 sub_away.Game2，
 sub_away.Game3，
 sub_away.TotalScore 
 
 FROM 
（SELECT 
`MatchDate`，
 FORMAT（MatchWeek， 0）AS MatchWeek，
 FORMAT（MatchNumber，0）AS MatchNumber，
 TEAM，
 FORMAT（HmTeamNo，0）AS HomeTeamNo，
 
 FORMAT（（250  - （SELECT SUM（Gm1 + Gm2 + Gm3）/（SELECT COUNT（Gm1）* 3）* 0.8）），0）AS HDCP，
 
 
格式（SUM（gm1），0 ）AS Game1，
 FORMAT（SUM（gm2），0）AS Game2，
 FORMAT（SUM（gm3），0）AS Game3，
 FORMAT（（SUM（gm1）+ SUM（ gm2）+ SUM（gm3）），0）AS TotalScore 
 FROM Rainbow_Lanes_Matches 
 JOIN Rainbow_Lanes ON周= MatchWeek 
 AND TEAMNUM = HmTeamNo 
 WHERE`Week`> = 19 
 GROUP BY`Match_id` 
 
 ORDER BY`MatchWeek`和`MatchNumber` 
）
 sub_home 
 JOIN（SELECT 
`MatchDate`， 
 FORMAT（MatchWeek，0）AS MatchWeek， 
 FORMAT（MatchNumber，0）AS MatchNumber，
 TEAM，
 FORMAT（AwayTeamNo，0）AS AwayTeamNo，
 FORMAT（（250  - （SELECT SUM（Gm1 + Gm2 + Gm3） ）/（SELECT COUNT（Gm1）* 3）* 0.8）），0）AS HDCP，
 FORMAT（SUM（gm1），0）AS Game1，
 FORMAT（SUM（gm2），0） AS Game2，
 FORMAT（SUM（gm3），0）AS Game3，
 FORMAT（（SUM（gm1）+ SUM（gm2）+ SUM（gm3）），0）AS TotalScore 
 FROM Rainbow_Lanes_Matches 
 JOIN Rainbow_Lanes ON周= MatchWeek 
 AND TEAMNUM = AwayTeamNo 
 WHERE`Week`> = 19 
 GROUP BY`Match_id` 
 ORDER BY`MatchWeek` AND`MatchNumber` 
）
 
 sub_away 
 ON sub_home.matchnumber = sub_away.MatchNumber 
 AND sub_home.MatchWeek = sub_away.MatchWeek）;


I am trying to get scores from two separate bowling teams on one output table line.
Think i can do it with 2 separate select queries using joins as i showed below.

Input tables that i joined are
Schedule Table

DATE	    HOME TEAM #	      AWAY TEAM #
2017-02-13	     6	                7
2017-02-13	     3	                2
2017-02-13	     9           	    8
2017-02-13	     4	                5
2017-02-13	    10	                1



Data Table:

Date	TeamNum	Gm1	Gm2
12-09-16	4	726	811
12-09-16	6	540	573
12-09-16	5	450	480
12-09-16	7	640	497



Hope this is not too confusing.
Any help would be appreciated.
Thank you

What I have tried:

SELECT 'DATE','Match','TEAM', 'HmTeamNo' ,'Game 1', 'Game 2', 'Game 3', 'Total' , 'Away Tm' 
UNION (SELECT `MatchDate`, 
                   FORMAT(MatchNumber,0), 
       				TEAM, 
                   FORMAT(HmTeamNo,0), 
                   FORMAT(SUM(gm1),0), 
                   FORMAT(SUM(gm2),0), 
                   FORMAT(SUM(gm3),0), 
                   FORMAT((SUM(gm1)+SUM(gm2)+SUM(gm3)),0) as "Ttl" , 
                   FORMAT(AwayTeamNo,0) 
FROM Rainbow_Lanes_Matches 
      JOIN Rainbow_Lanes ON 
week = MatchWeek AND TEAMNUM = HmTeamNo 
WHERE MatchWeek =21
 			GROUP BY team 
ORDER BY MatchNumber
      
      )



Think it should work if i repeat the querie and change the hometeam variable to awayteam team and repeat the join but get errors.

UNION (SELECT `MatchDate`, 
                   FORMAT(MatchNumber,0), 
       				TEAM, 
                   FORMAT(HmTeamNo,0), 
                   FORMAT(SUM(gm1),0), 
                   FORMAT(SUM(gm2),0), 
                   FORMAT(SUM(gm3),0), 
                   FORMAT((SUM(gm1)+SUM(gm2)+SUM(gm3)),0) as "Ttl" , 
FROM Rainbow_Lanes_Matches 
      JOIN Rainbow_Lanes ON 
week = MatchWeek AND TEAMNUM = AwayTeamNo 
WHERE MatchWeek =21
 			GROUP BY team 
ORDER BY MatchNumber
      
      )



The second table includes the scores from the teams.


Output or Results Table will look like this:

DATE	Match	HmTm	 Gm1	 Gm2	   AwayTeam	         Gm1	Gm2	
13-02-17	1	4	     726	811	      9                  480	433
13-02-17	2	6	     540	573	      7	                 640	497

解决方案

I would suggest using two subqueries, like what you have done. But, instead of using UNION, I would use a JOIN, because you need to treat them as two separate tables of data and only get what you need from each of them. Using UNION could show empty results for some records.

Try this query. The first SELECT will define your output, and the subqueries get the data you need for the home and away teams.

Hope this works for you - I'm not sure of your table structure so it might throw some initial syntax errors.

SELECT sub_home.MatchDate,
sub_home.MatchWeek,
sub_home.Team,
sub_home.HomeTeamNo,
sub_home.Game1,
sub_home.Game2,
sub_home.Game3,
sub_home.TotalScore
sub_away.AwayTeamNo,
sub_away.Game1,
sub_away.Game2,
sub_away.Game3,
sub_away.TotalScore
FROM (
	SELECT `MatchDate`, 
	FORMAT(MatchNumber,0) AS MatchNumber, 
	TEAM, 
	FORMAT(HmTeamNo,0) AS HomeTeamNo, 
	FORMAT(SUM(gm1),0) AS Game1, 
	FORMAT(SUM(gm2),0) AS Game2, 
	FORMAT(SUM(gm3),0) AS Game3, 
	FORMAT((SUM(gm1)+SUM(gm2)+SUM(gm3)),0) AS TotalScore
	FROM Rainbow_Lanes_Matches 
	JOIN Rainbow_Lanes ON week = MatchWeek AND TEAMNUM = HmTeamNo 
	GROUP BY team  
) sub_home
JOIN (
	SELECT `MatchDate`, 
	FORMAT(MatchNumber,0) AS MatchNumber, 
	TEAM, 
	FORMAT(AwayTeamNo,0) AS AwayTeamNo, 
	FORMAT(SUM(gm1),0) AS Game1, 
	FORMAT(SUM(gm2),0) AS Game2, 
	FORMAT(SUM(gm3),0) AS Game3, 
	FORMAT((SUM(gm1)+SUM(gm2)+SUM(gm3)),0) AS TotalScore 
	FROM Rainbow_Lanes_Matches 
	JOIN Rainbow_Lanes ON week = MatchWeek AND TEAMNUM = AwayTeamNo 
	GROUP BY team 
	ORDER BY MatchNumber
) sub_away
ON sub_home.matchnumber = sub_away.MatchNumber
AND sub_home.MatchWeek = sub_away.MatchWeek;


SELECT  "Date", "Wk", "Match", "Hm Team", "HDCP", "Gm 1", "Gm 2", "Gm 3", "Ttl","Away Team","HDCP", "Gm 1", "Gm 2", "Gm 3", "Ttl"   
UNION     

(SELECT 	
sub_home.MatchDate,
sub_home.MatchWeek,
sub_home.Matchnumber,
sub_home.HomeTeamNo,
sub_home.HDCP,
/*  sub_home.Bowler,  */
sub_home.Game1,
sub_home.Game2,
sub_home.Game3,
sub_home.TotalScore,

sub_away.AwayTeamNo,
sub_away.HDCP,
sub_away.Game1,
sub_away.Game2,
sub_away.Game3,
sub_away.TotalScore

 FROM 
(SELECT 
	`MatchDate`, 
 	FORMAT(MatchWeek,0) AS MatchWeek, 	
 	FORMAT(MatchNumber,0) AS MatchNumber, 
	TEAM, 
	FORMAT(HmTeamNo,0) AS HomeTeamNo, 

FORMAT ((250 -( SELECT SUM(Gm1+Gm2+Gm3) / ( SELECT COUNT(Gm1)*3)*0.8)),0) AS HDCP,
 
 
        FORMAT(SUM(gm1),0) AS Game1, 
	FORMAT(SUM(gm2),0) AS Game2, 
	FORMAT(SUM(gm3),0) AS Game3, 
	FORMAT((SUM(gm1)+SUM(gm2)+SUM(gm3)),0) AS TotalScore
	FROM Rainbow_Lanes_Matches 
	JOIN Rainbow_Lanes ON week = MatchWeek 
		AND TEAMNUM = HmTeamNo 
 									WHERE `Week` >= 19 
	GROUP BY `Match_id` 

 ORDER BY `MatchWeek` AND `MatchNumber`
) 
sub_home
JOIN (SELECT 
	`MatchDate`, 
 	FORMAT(MatchWeek,0) AS MatchWeek, 
    FORMAT(MatchNumber,0) AS MatchNumber, 
	TEAM, 
	FORMAT(AwayTeamNo,0) AS AwayTeamNo, 
FORMAT ((250 -( SELECT SUM(Gm1+Gm2+Gm3) / ( SELECT COUNT(Gm1)*3)*0.8)),0) AS HDCP,
      FORMAT(SUM(gm1),0) AS Game1, 
	FORMAT(SUM(gm2),0) AS Game2, 
	FORMAT(SUM(gm3),0) AS Game3, 
	FORMAT((SUM(gm1)+SUM(gm2)+SUM(gm3)),0) AS TotalScore 
	FROM Rainbow_Lanes_Matches 
	JOIN Rainbow_Lanes ON week = MatchWeek 
      AND TEAMNUM = AwayTeamNo
       								WHERE `Week` >= 19 
	GROUP BY `Match_id`
	ORDER BY `MatchWeek` AND `MatchNumber`
) 

sub_away
ON sub_home.matchnumber = sub_away.MatchNumber
AND sub_home.MatchWeek = sub_away.MatchWeek);


这篇关于如何将来自2个表查询的数据放在同一个表行上？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
其他开发语言最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆