将表连接到自身 [英] Joining table to itself

问题描述

我的区经理想要一份报告，显示所有未在x天内从配送仓库订购商品的商店。



只有一张桌子有数百和数千条记录。



该表有一个OrdCreateDt字段。我想我需要查询这个表，其中OrdCreateDt字段是< =区域mgr进入的任何天数。



但是，dist mgr想要看到在这些相同天数内没有订单的商店编号以及在x天数之前订购的商品的详细信息。



我创建了这个查询：

My district manager wants a report to show all stores that haven't ordered merchandise from the distribution warehouse in x number of days.

There is just one table with hundreds and of thousands of records.

The table has an OrdCreateDt field. I am thinking I need to query this table where the OrdCreateDt field is <= whatever number of days the district mgr enters.

But, the dist mgr wants to see the store numbers that did not have orders within these same number of days and the details of what the ordered before the x number of days.

I created this query:

select t.storenum, t.storename, t.mercnum, t.ordcreatedt from all_orders t left join (select storenum from all_orders where OrdCreateDt <= dateadd("dd", -days, convert (varchar(10), getdate(),101)) ) t2 on t1.storenum =t2 storenum where OrdCreateDt < dateadd("dd", -days, convert(varchar(10), getdate(),101))

这需要几秒钟才能运行。这是最有效的方法吗？



我尝试过：



Msdn搜索，谷歌搜索，stackoverflow网站

This takes several seconds to run. Is this the most efficient way to do this?

What I have tried:

Msdn search, Google search, stackoverflow website

推荐答案

我认为1合1表是一个你应该解决的概念问题。



我不是SQL程序员，但我知道数据库是如何工作的。

1）建立所有商店的临时表

2 ）找到每个商店的最后一个订单

3）过滤不需要的商店

由于1表中的问题，改变是效率非常低。

使用商店表，可以很容易地存储最后一个订单的日期。

I think the all in 1 table is a conception problem you should address.

I am not an SQL programmer, but I know how databases works.
1) build a temporary table of all shops
2) find the last order for each shop
3) filter unwanted shops
Because of the all in 1 table problem, changes are that it will be highly inefficient.
With a shop table, it would be easy to store the date of last order.

这篇关于将表连接到自身的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！