SQL查询对访问数据库的麻烦 [英] Trouble with SQL query against access database
问题描述
是的我知道参数化查询但是为了简单起见，这个问题是在没有使用的情况下发布的。
以下示例数据来自Access数据库
ID Ticket_Year Ticket_Number Ticket_Amount Amount_Paid Transaction_Date_Time
8 2013 06879 256.65 200 2/10/2018 11:02:00 AM
9 2013 06879 256.65 56 2/10/2018 11:03:00 AM
10 2013 06880 29.3 27 2/10/2018 11:55:47 AM
11 2013 06880 29.3 2 2/10/2018 11:55:56 AM
12 2013 06881 58.5 57 2/10/2018 11:57:44 AM
13 2013 06881 58.5 1 2 / 10/2018 11:57:53 AM
14 2013 06882 36 35 2/10/2018 12:05:05 PM
15 2013 06882 36 1 2/10 / 2018 12:05:15 PM
16 2013 06883 85.05 80 2/10/2018 12:19:12 PM
17 2013 06883 85.05 5 2/10/2018 12 ：19：28 PM
18 2013 06884 514.5 200 2/10/2018 1:47:01 PM
19 2013 06884 514.5 200 2/10/2018 2:05 ：下午46点
20 2013 06884 514.5 110 2/10/2018 2:09:58 PM
21 2013 06886 57.28 57 2/10/2018 7:16:39 PM
数据样本在上面
I想要选择[Ticket_Number]，[Ticket_Amount]，SUM（[Amount_Paid]），[Transaction_Date_Time]，[Ticket_Year]列，然后按[Ticket_Number]对数据进行分组，然后按列[Transaction_Date_Time] ASC
对于上面的示例，我希望查询返回
06879 256.65 256 2/10/2018 11:02:00 AM 2013 >
06880 29.3 29 2/10/2018 11:55:47 AM
06881 58.5 58 2/10/2018 11:57:44 AM
06882 36 36 2/10/2018 12:05:05 2013
06883 85.05 85 2/10/2018 12:19:12 PM
06884 514.5 519 2/10/2018 2:09:58 PM 2013
06886 57.28 57 2/10/2018 7:16:39 PM 2013
我尝试过：
SELECT [Ticket_Number]，[Ticket_Amount]，SUM （[Amount_Paid]），[Transaction_Date_Time]，[Ticket_Year] FROM付费WHERE [Transaction_Date_Time] LIKE'％2018％'ORDER BY [Transaction_Date_Time] ASC GROUP BY [Ticket_Amount]
以上给出了关于聚合函数的错误
SELECT [Ticket_Number]，[Ticket_Amount]，SUM（[Amount_Paid]），[Transaction_Date_Time]，[Ticket_Year]来自付费WHERE [Transaction_Date_Time] LIKE'％2018％'ORDER BY [Transaction_Date_Time] ASC GROUP BY [Ticket_Number]，[Ticket_Amount]，[Transaction_Date_Time]，[Ticket_Year]
哪个完成没有错误但是返回的数据与数据原理相同
Yes I am aware of parameterized queries however for simplicity this question is posted without utilization.
Sample Data Below Taken From Access Database
ID Ticket_Year Ticket_Number Ticket_Amount Amount_Paid Transaction_Date_Time
8 2013 06879 256.65 200 2/10/2018 11:02:00 AM
9 2013 06879 256.65 56 2/10/2018 11:03:00 AM
10 2013 06880 29.3 27 2/10/2018 11:55:47 AM
11 2013 06880 29.3 2 2/10/2018 11:55:56 AM
12 2013 06881 58.5 57 2/10/2018 11:57:44 AM
13 2013 06881 58.5 1 2/10/2018 11:57:53 AM
14 2013 06882 36 35 2/10/2018 12:05:05 PM
15 2013 06882 36 1 2/10/2018 12:05:15 PM
16 2013 06883 85.05 80 2/10/2018 12:19:12 PM
17 2013 06883 85.05 5 2/10/2018 12:19:28 PM
18 2013 06884 514.5 200 2/10/2018 1:47:01 PM
19 2013 06884 514.5 200 2/10/2018 2:05:46 PM
20 2013 06884 514.5 110 2/10/2018 2:09:58 PM
21 2013 06886 57.28 57 2/10/2018 7:16:39 PM
Sample of Data Is Above
I would like to select columns [Ticket_Number], [Ticket_Amount], SUM([Amount_Paid]), [Transaction_Date_Time], [Ticket_Year] and then Group The Data By [Ticket_Number] and then order the data by column [Transaction_Date_Time] ASC
For The Example above I would like the query to return
06879 256.65 256 2/10/2018 11:02:00 AM 2013
06880 29.3 29 2/10/2018 11:55:47 AM 2013
06881 58.5 58 2/10/2018 11:57:44 AM 2013
06882 36 36 2/10/2018 12:05:05 PM 2013
06883 85.05 85 2/10/2018 12:19:12 PM 2013
06884 514.5 519 2/10/2018 2:09:58 PM 2013
06886 57.28 57 2/10/2018 7:16:39 PM 2013
What I have tried:
SELECT [Ticket_Number], [Ticket_Amount], SUM([Amount_Paid]), [Transaction_Date_Time], [Ticket_Year] FROM Paid WHERE [Transaction_Date_Time] LIKE '%2018%' ORDER BY [Transaction_Date_Time] ASC GROUP BY [Ticket_Amount]
Above Gives Error About Aggregate Functions
SELECT [Ticket_Number], [Ticket_Amount], SUM([Amount_Paid]), [Transaction_Date_Time], [Ticket_Year] FROM Paid WHERE [Transaction_Date_Time] LIKE '%2018%' ORDER BY [Transaction_Date_Time] ASC GROUP BY [Ticket_Number], [Ticket_Amount], [Transaction_Date_Time], [Ticket_Year]
Which Completes With No Errors However Data Returned Is Identical To Data Orriginal
推荐答案
如果列在您的查询中，则它必须在您的group by语句中。例如：您的第一个查询在您的组中只有Ticket_Amount，您的第二个查询包含group by语句中的所有列，因为它们都在您的Select语句中，这有助于为什么数据看起来正常。
您可能最好将此作为子查询或重新考虑您需要的数据而不是查询所有列。您可能需要重新考虑您的架构，因为我的理解有限，似乎您的付费表中每张票都有多个记录...我认为您还有另一张名为say的表...Ticketwith a您的付费表的外键，然后您就可以更轻松地进行此查询。
否则，我可能最终只能拥有做一个类似的子查询。
If the column is within your query, it must be in your group by statement. Ex: your first query only has "Ticket_Amount" in your group by where your second query has all of your columns in the group by statement because they are all in your Select statement which makes sense why the data looks "normal" for you.
You might be better off doing this as a sub query or rethinking what data you need instead of querying all the columns. You may need to rethink your schema as, with my limited understanding, it seems you've got multiple records per ticket in your paid table...i would think you'd have another table called say..."Ticket" with a foreign key to your Paid table and then you'd be able to this query more easily.
Otherwise off the top of my head you'll probably end up just having to do a subquery that would look something like.
SELECT *, (SELECT SUM([Amount_Paid]) FROM Paid AS B WHERE A.Transaction_DateTime LIKE '%2018%' GROUP BY Ticket_Number) FROM Paid AS A ORDER BY Transaction_Date_Time
鉴于我不知道你的架构这个是我认为可能更好的另一种替代子查询形式...可能
Given i don't know your schema this is the other alternative subquery form that I think may work better...possibly
SELECT *, (SELECT SUM([Amount_Paid]) FROM Paid AS B WHERE A.Ticket_Bumber = B.Ticket_Number GROUP BY Ticket_Number) FROM Paid AS A ORDER BY Transaction_Date_Time
如果你不能改变你的模式，上面的子查询是我能想到完成你想要完成的事情的唯一方法。
If you can't alter your schema, the above subquery is the only way i can think of accomplishing what you want to accomplish.
这篇关于SQL查询对访问数据库的麻烦的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
