SQL Server多表多个过滤器
问题描述:
我是SQL中的新用户,我可以使用一些帮助与我的代码,请帮助!SQL Server多表多个过滤器
我有两个表:表1(项目主)和表2(购买历史)
- 表1具有像的说明中,供应商,仓库等的物品信息
- 表2具有每一个交易。
表1例
ITEM Warehouse Vendor
------------------------------
ITEM-0001 ATLANTA AMAZON
ITEM-0002 ATLANTA AMAZON
ITEM-0003 ATLANTA GOOGLE
ITEM-0004 ATLANTA AMAZON
表2中的例子
ITEM Purchase Date Price WAREHOUSE RECEIVE DATE
----------------------------------------------------------------
ITEM-0001 01/01/2017 $5 ATLANTA 03/05/2017
ITEM-0001 01/01/2017 $5.2 DENVER 02/03/2017
ITEM-0001 01/02/2017 $5.4 L.A. 05/07/2017
ITEM-0002 .... AND SO ON
我想有一个综合作用的结果集 - 这样的:
ITEM(T1) WAREHOUSE(T1) VENDOR(T1) Avg_Price(T2) LEADTIME
------------------------------------------------------------------
ITEM-0001 ATLANTA AMAZON 5.33333 65 DAYS
ITEM-0002 ATLANTA AMAZON ... so on
这里是我的SQL代码:
SELECT
A.ITEM, A.WAREHOUSE, A.VENDOR, B.PRICE, B.LEADTIME
FROM
TABLE1 A, TABLE2 B
WHERE
A.ITEM = B.ITEM
AND B.PRICE = (SELECT AVG(B.PRICE)
FROM TABLE2 B
WHERE A.ITEM = B.ITEM
GROUP BY B.ITEM)
该部分增加了平均价格没有任何问题。现在我挣扎的是当我想添加LEADTIME的时候。任何想法如何编码?
LEADTIME是物料到达特定供应商和特定仓库的平均时间。例如,item0001在6,8和10天内从供应商x抵达仓库y,则交货时间将是该供应商和该仓库的这些天(8天)的平均值。
请帮忙!
在此先感谢
编辑:AFTER帮助在这里是我的新代码..谢谢!
SELECT B.ITEM,
A.VENDOR,
B.WAREHOUSE,
SUM(A.QUANTITY),
AVG(A.PRICE) AS AVGERAGE_PURCHASE PRICE,
AVG(A.RECEIVEDATE- A.PURCHASEDATE) AS LEADTIME,
FROM ITEMTABLE B
JOIN ITEMPURCHASETABLE A
ON (B.ITEM=A.ITEM AND B.WAREHOUE=A.WAREHOUSE)
WHERE A.PURCHASEDATE > 1170101
GROUP BY B.ITEM, B.WAREHOUSE, A.VENDOR
HAVING (MIN(A.PRICE)/MAX(A.PRICE)) !<0.1 AND COUNT(A.PRICE) >3
ORDER BY INVENTORY_COST DESC
新问题:日期格式是1YYMMDD是否有解决方案?
答
你的代码是不正确的,你加入到B,但然后有一个子查询的值在哪里。这里的
=
过滤B中的值不设置值。
由于@marc_s建议这里是如何编写这个SQL,而不使用25岁的语法。
SELECT A.ITEM, A.WAREHOUSE, A.VENDOR,
AVG(B.PRICE) AS AVG_PRICE
--, B.LEADTIME not sure this works don't know requirements.
FROM TABLE1 A
JOIN TABLE2 B ON A.ITEM = B.ITEM
GROUP BY A.ITEM, A.WAREHOUSE, A.VENDOR
你也可以做到这一点与一窗口功能(参见之前的版本,如果你的关心。)
+0
谢谢!我发布了我的新代码,它功能强大。日期格式为1yymmdd,有没有办法解决这个问题? – MrKen
答
下面是查询以获得类似的结果在Microsoft SQL Server。
SELECT A.ITEM
, A.WAREHOUSE
, A.VENDOR
, AVG(B.PRICE)
, AVG(DATEDIFF(day,B.PurchaseDate,B.ReceiveDate)) AS LeadTime
FROM TABLE1 A
JOIN TABLE2 B
ON A.ITEM = B.ITEM
GROUP BY A.ITEM, A.WAREHOUSE, A.VENDOR
[不良习惯踢:使用旧样式的JOIN(http://sqlblog.com/blogs/aaron_bertrand/archive/2009/10/08/bad-habits-to-kick-using-old -style-joins.aspx) - 在ANSI - ** 92 ** SQL标准中(** 25年*),旧式*逗号分隔的表*样式列表已替换为* proper * ANSI'JOIN'语法*前),其使用是不鼓励的 –
你想添加前置时间,你告诉我们,但你不告诉我们什么时间。什么是交货时间? – Hogan