数据库使用不同的列多次选择相同的记录

发布于 2024-11-07 02:34:02 字数 525 浏览 0 评论 0原文

我有一个包含多个日期列的订单表（部门完成日期）。我想查询该表并为指定日期的每个匹配项返回唯一的行，并给出选择该行的原因的参考。

因此，从此表中：

OrderID   OrderName   Date1      Date2      Date3
456       feh         5/1/2011   6/1/2011   3/1/2011
487       meh         12/1/2010  2/1/2011   8/1/2011

如果查询任何大于 4/1/2011 的日期，我想返回：

456       feh         5/1/2011     Date1
456       feh         6/1/2011     Date2
487       meh         8/1/2011     Date3

数据位于 MS Access 中，我不确定这在查询级别是否可行，或者是否需要生成子报告。

非常感谢您的帮助！

原文

I have a table of orders with multiple date columns (Department finished dates). I'd like to query the table and return a unique line for each match of a specified date and give a reference to why that line was selected.

So from this table:

OrderID   OrderName   Date1      Date2      Date3
456       feh         5/1/2011   6/1/2011   3/1/2011
487       meh         12/1/2010  2/1/2011   8/1/2011

If queried for any date greater than 4/1/2011 I would like to return:

456       feh         5/1/2011     Date1
456       feh         6/1/2011     Date2
487       meh         8/1/2011     Date3

The data is in MS Access and I'm not sure if this is possible at the query level or would require subreports to produce.

Thanks much for the help!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

这个俗人 2024-11-14 02:34:02

Select OrderID, OrderName Date1 as Date, 'Date1' as ComparedDate
from table where date1>'4/21/2011'  
UNION ALL  
Select OrderID, OrderName Date2 as Date, 'Date2' as ComparedDate  
from table where Date2>'4/21/2011'  
UNION ALL  
Select OrderID, OrderName Date3 as Date, 'Date1' as ComparedDate
from table where Date3>'4/21/2011'

Select OrderID, OrderName Date1 as Date, 'Date1' as ComparedDate
from table where date1>'4/21/2011'  
UNION ALL  
Select OrderID, OrderName Date2 as Date, 'Date2' as ComparedDate  
from table where Date2>'4/21/2011'  
UNION ALL  
Select OrderID, OrderName Date3 as Date, 'Date1' as ComparedDate
from table where Date3>'4/21/2011'

回复收藏 0 原文

浊酒尽余欢 2024-11-14 02:34:02

尝试这样做来获取您的最后一列：

Select OrderID, OrderName, Date1 as SelDate, 'Date1' as Reason
  from table where date1>'4/21/2011'
UNION ALL  
Select OrderID, OrderName, Date2 as SelDate, 'Date2' as Reason
  from table where Date2>'4/21/2011'
UNION ALL  
Select OrderID, OrderName, Date3 as SelDate, 'Date3' as Reason
  from table where Date3>'4/21/2011'

另请注意，我更改了 SelDate 列的别名 - 即使它是别名时并不重要（在 Access 中），您确实应该避免使用“保留”来命名列字。

Try this to get your last column:

Select OrderID, OrderName, Date1 as SelDate, 'Date1' as Reason
  from table where date1>'4/21/2011'
UNION ALL  
Select OrderID, OrderName, Date2 as SelDate, 'Date2' as Reason
  from table where Date2>'4/21/2011'
UNION ALL  
Select OrderID, OrderName, Date3 as SelDate, 'Date3' as Reason
  from table where Date3>'4/21/2011'

Also note that I changed the alias of the SelDate column--even though it's not critical (in Access) when it's an alias, you really should avoid naming columns with Reserved Words.

回复收藏 0 原文

内心荒芜 2024-11-14 02:34:02

这可以通过查询来实现：

Select OrderID, OrderName, Date1 as Date 
from table where date1>'4/21/2011'

UNION ALL

Select OrderID, OrderName, Date2 as Date 
from table where Date2>'4/21/2011'

UNION ALL

Select OrderID, OrderName, Date3 as Date 
from table where Date3>'4/21/2011'

This is achievable through a query:

Select OrderID, OrderName, Date1 as Date 
from table where date1>'4/21/2011'

UNION ALL

Select OrderID, OrderName, Date2 as Date 
from table where Date2>'4/21/2011'

UNION ALL

Select OrderID, OrderName, Date3 as Date 
from table where Date3>'4/21/2011'

回复收藏 0 原文

~没有更多了~