如何在连接中使用clouse组 [英] How to use group by clouse in joins

问题描述

SELECT        tb_Ticket.TicketName, tb_Sales.DateDraw, tb_UserManagement.UserName, 
tb_Sales.BillNo, tb_PrizeNumbers.PrizePosition, tb_PrizeNumbers.PrizeNo, 
                         tb_PrizeDetails.Stockist, tb_PrizeDetails.Substockist, tb_PrizeDetails.Agent
FROM            tb_Bill INNER JOIN
                         tb_PrizeDetails INNER JOIN
                         tb_LotteryPrizeDetails ON tb_PrizeDetails.TID = tb_LotteryPrizeDetails.TID INNER JOIN
                         tb_PrizeNumbers ON tb_LotteryPrizeDetails.TID = tb_PrizeNumbers.TID ON 
                         tb_Bill.SerialNo = tb_PrizeNumbers.PrizeNo CROSS JOIN
                         tb_Ticket INNER JOIN
                         tb_Sales ON tb_Ticket.TicketID = tb_Sales.TicketID INNER JOIN
                         tb_UserManagement ON tb_Sales.UserID = tb_UserManagement.UserID INNER JOIN
                         tb_WinningTable ON tb_Ticket.TicketID = tb_WinningTable.TicketID GROUP BY tb_Bill.BillNo

列'tb_Ticket.TicketName'在选择列表中无效，因为它不包含在eith中一个聚合函数或GROUP BY子句。



这是错误显示..帮我plzzzzzzzzzz



我尝试过：

Column 'tb_Ticket.TicketName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

this is the error showing.. help me plzzzzzzzzzz

What I have tried:

SELECT        tb_Ticket.TicketName, tb_Sales.DateDraw, tb_UserManagement.UserName, 
tb_Sales.BillNo, tb_PrizeNumbers.PrizePosition, tb_PrizeNumbers.PrizeNo, 
                         tb_PrizeDetails.Stockist, tb_PrizeDetails.Substockist, tb_PrizeDetails.Agent
FROM            tb_Bill INNER JOIN
                         tb_PrizeDetails INNER JOIN
                         tb_LotteryPrizeDetails ON tb_PrizeDetails.TID = tb_LotteryPrizeDetails.TID INNER JOIN
                         tb_PrizeNumbers ON tb_LotteryPrizeDetails.TID = tb_PrizeNumbers.TID ON 
                         tb_Bill.SerialNo = tb_PrizeNumbers.PrizeNo CROSS JOIN
                         tb_Ticket INNER JOIN
                         tb_Sales ON tb_Ticket.TicketID = tb_Sales.TicketID INNER JOIN
                         tb_UserManagement ON tb_Sales.UserID = tb_UserManagement.UserID INNER JOIN
                         tb_WinningTable ON tb_Ticket.TicketID = tb_WinningTable.TicketID GROUP BY tb_Bill.BillNo

列'tb_Ticket。 TicketName'在选择列表中无效，因为它不包含在聚合函数或GROUP BY子句中。



这是错误显示..帮助我plzzzzzzzzzz

Column 'tb_Ticket.TicketName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

this is the error showing.. help me plzzzzzzzzzz

推荐答案

GROUP BY子句将一系列行折叠为具有相同值的单个行 - 并且您只能直接返回这些相同的值 - 所有其他列来自行组只能作为聚合值返回 - SUM，AVERAGE等。

所以如果你有三行

A GROUP BY clause "collapses" a range of rows into a single row with identical values - and you can only return those identical values directly - all other columns from the group of rows can only be returned as an aggregate value - the SUM, the AVERAGE, and so on.
So if you have three rows

ID   Date        Sales
1    2016-01-02  100
2    2016-01-02  200
3    2016-01-02  300
4    2016-01-03  400
5    2016-01-03  500
6    2016-01-03  600

你是GROUP BY日期，那么你不能直接返回Sales值，因为SQL没有知道返回哪一行值n：

And you GROUP BY Date, then you can't return the Sales value directly because SQL doesn't know which row value to return:

SELECT Date, Sales FROM MyTable GROUP BY Date

Date        Sales
2016-01-02  ??? Should this be 100, 200, or 300?
2016-01-03  ??? Should this be 400, 500, or 600?

您可以返回一个聚合值：

You can return an aggregate value though:

SELECT Date, SUM(Sales) FROM MyTable GROUP BY Date

Date        Sales
2016-01-02  600
2016-01-03  1500

您正在尝试返回值不一定是单行值，因为你是GROUP BY BillNo所以SQL抱怨。

你需要确切地计算你的数据是什么样的，以及你需要返回什么 - 我们可以帮助你，因为我们不知道你的数据是什么，更不用说你想要选择的了！

You are trying to return values which aren't necessarily a single row value because you GROUP BY BillNo so SQL complains.
You need to work out exactly what your data looks like, and what you need to return - and we can;t help you with that, because we have no idea what your data is, much less what you want to select!

这篇关于如何在连接中使用clouse组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！