我希望在ms访问plz帮助中跟随SQL查询 [英] I want following SQL query in ms access plz help

问题描述

我想在ms访问中跟踪sql查询

I want following sql query in ms access

DECLARE @BreakFast INT =(SELECT  sum( OM.ItemQuantity)  FROM VW_EmpOrderMaster OM WHERE  OM.MealType=1 AND MONTH(OM.OrderTime)
	=MONTH(GETDATE()) 	 and day(OM.OrderTime)=day(GETDATE()) AND YEAR(OM.OrderTime)=YEAR(GETDATE()))
	DECLARE @Lunch INT =(SELECT  sum( OM.ItemQuantity)  FROM VW_EmpOrderMaster OM WHERE  OM.MealType=2 AND MONTH(OM.OrderTime)=MONTH(GETDATE()) 
		 and day(OM.OrderTime)=day(GETDATE()) AND YEAR(OM.OrderTime)=YEAR(GETDATE())) 

DECLARE @BreakFastvisitor INT =(SELECT  sum( OM1.ItemQuantity)  FROM VW_EmpOrderMaster_Visitor OM1 WHERE  OM1.MealType=1 AND MONTH(OM1.OrderTime)
	=MONTH(GETDATE()) 	 and day(OM1.OrderTime)=day(GETDATE()) AND YEAR(OM1.OrderTime)=YEAR(GETDATE()))
	DECLARE @Lunchvisitor INT =(SELECT  sum( OM1.ItemQuantity)  FROM VW_EmpOrderMaster_Visitor OM1 WHERE  OM1.MealType=2
	 AND MONTH(OM1.OrderTime)=MONTH(GETDATE())	 and day(OM1.OrderTime)=day(GETDATE()) 
	AND YEAR(OM1.OrderTime)=YEAR(GETDATE())) 


declare @totalbreakfast int=sum((ISNULL((@BreakFast), 0))+(ISNULL((@BreakFastvisitor), 0))) 
	declare @totalLunch int = sum((ISNULL((@Lunch), 0))+(ISNULL((@Lunchvisitor), 0)))

SELECT  

		 @totalbreakfast AS BreakFast,  
	 @totalLunch AS Lunch,  

我的尝试：

What I have tried:

      ( (SELECT  sum( OM.ItemQuantity) as BreakFast FROM VW_EmpOrderMaster OM WHERE  OM.MealType=1  AND MONTH(OM.OrderTime)
	=MONTH(now()) 	 and day(OM.OrderTime)=day(now()) AND YEAR(OM.OrderTime)=YEAR(now())) union
(SELECT  sum( OM1.ItemQuantity) as BreakFast  FROM VW_EmpOrderMaster_Visitor OM1 WHERE  OM1.MealType=1 AND MONTH(OM1.OrderTime)
	=MONTH(now()) 	 and day(OM1.OrderTime)=day(now()) AND YEAR(OM1.OrderTime)=YEAR(now()))   )  

推荐答案

正如Dave上面所说，您的原始代码是存储过程，即Access不支持。因此，您需要编写一个查询，一次性返回所有数据（没有临时变量，没有临时表），或者编写多个查询并从另一个调用。



您还需要了解功能的差异;所以SQL Server使用GetDate（），而Access使用NOW（）。我不建议单独测试年/月/日元素，而是建议测试字段的日期部分，使其更具可读性（更高效，更不容易出错），因此您的日期测试变为AND DATEVALUE（OM.OrderTime）= DATEVALUE（NOW（））



Access ISNULL函数只返回True / False值，所以你需要将它与内联IIF语句结合起来。这样做的一个缺点是，如果结果不为null，则必须重复在ISNULL函数内调用的任何函数，因此可能会有重大的性能损失。但是，嘿，你正在使用Access，所以性能显然不是你的担心。



要获得你想要的两列结果，并将其分离出来午餐和早餐组件（即一些行有助于一列，其他行有助于另一行）您需要使用CASE语句，对膳食类型进行测试。 Access不支持SQL Server的CASE语句，但它确实有一个SWITCH，我们可以以类似的方式使用它。



把这些放在一起，得到类似的结果：

As Dave said above, your original code is a Stored Procedure, which Access doesn't support. So you'll need to write either a single query that returns all your data in one go (no temporary variables, no temporary tables), or write multiple queries and call one from another.

You also need to be aware of differences in functions; so SQL Server uses GetDate(), whereas Access uses NOW(). Rather than testing the year/month/day elements separately, I'd suggest it's more readable (and more efficient and less error prone) to just test the date part of the fields, so your date tests become AND DATEVALUE(OM.OrderTime) = DATEVALUE(NOW())

The Access ISNULL function simply returns a True/False value, so you need to combine this with an inline IIF statement. One downside of this is that any functions called within the ISNULL function have to be repeated if the result is not null, so there's a potentially significant performance hit. But hey, you're using Access, so performance clearly isn't a worry for you.

To get the two column result you're after, and separating out the lunch and breakfast components (i.e. some rows contribute to one column, other rows to another) you'll need to use a CASE statement, testing on the meal type. Access doesn't support SQL Server's CASE statement, but it does have a SWITCH which we can use in a similar way.

Putting this together, we get something like:

SELECT IIF(ISNULL(SUM(SWITCH(OM.MealType=1,OM.ItemQuantity,OM.MealType=2,0))),0,SUM(SWITCH(OM.MealType=1,OM.ItemQuantity,OM.MealType=2,0))) AS Breakfast,
       IIF(ISNULL(SUM(SWITCH(OM.MealType=2,OM.ItemQuantity,OM.MealType=1,0))),0,SUM(SWITCH(OM.MealType=2,OM.ItemQuantity,OM.MealType=1,0))) AS Lunch
FROM VW_EmpOrderMaster OM 
WHERE (OM.MealType=1 OR OM.MealType=2)
AND DATEVALUE(OM.OrderTime)=DATEVALUE(NOW());

这将早餐和午餐价值分开;仅按今天过滤日期（我猜你会想要参数化，所以用适当的查询参数替换NOW（）函数）;如果没有找到午餐/早餐，则给出零值 - 并在表格的单个查询中完成所有。



您的原始请求也会增加游客。首先，我要问的是，这是否真的是你的意思 - 感觉就像添加苹果和梨来给出一些橘子。



假设那是你的意思但是，我想要采用的方法是编写一个非常相似的查询但是针对访问者表;然后编写第三个查询，结合两者的结果。这将提高可测试性并避免进一步使查询复杂化，因此您仍然希望了解正在发生的事情！ （我花了一些时间来正确匹配上面的括号，而不是使它们的数量级更复杂......）以上都是针对样本数据测试的，顺便说一句。



希望你能从这里拿走它。

This separates out the breakfast and lunch values; filters by date for TODAY only (I guess you will want to parameterise that, so replace the NOW() function with your appropriate query parameter); gives a zero value if there are no lunch/breakfasts found - and does it all in a single query of the table.

Your original request also adds in the number of visitors. Firstly, I'd query if that's really what you mean to do - it feels rather like adding apples and pears to give a number of oranges.

Assuming that's what you want, though, the approach I would probably take is to write a very similar query but against the visitors table; then write a third query that combines the results from the two. That will improve testability and avoid complicating the query still further, so you still have a hope of understanding what's going on! (It took me a while to match the brackets up correctly in the above, without making it all an order of magnitude more complex...) The above is all tested against sample data, btw.

Hopefully you can take it from here.

创建一个简单的选择查询 - 访问 [ ^ ]

这篇关于我希望在ms访问plz帮助中跟随SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！