SQL查询帮助 [英] SQL Query Assistance
问题描述
我有一个如下的sql：
SELECT ib.branch_no，
ib.on_hand，
p.weightedav，
p.item_code，
FROM physical p
INNER JOIN
item_branch as ib on p.item_code = ib.item_code
WHERE ib.on_hand< ;> 0
此SQL仅返回具有on_hand<>的branch_no 0.
我试图得到所有的branch_nos而不管on_hand字段，但仍然使用where on_hand子句。
取走on_hand子句解决了我的问题，但是给了我大量不需要的行0'。
我是使用SQL SERVER 2008 R2。
提前感谢任何指导。如果我遗漏任何信息，请道歉。
-------------------------------- SAMPLE结果--------------
这是使用on_hand<> 0
<的结果pre lang =SQL> branchno weighted_av item_code x_value y_value on_hand PhysicalOH
999 72 。< span class =code-digit> 00 80S1211001墨水/白色 10 16 16
999 72 。 00 80S1211002墨水/白色 12 19 19
1 72 。 00 80S1211003墨水/白色 14 -1 17
17 72 。 00 80S1211003墨水/白色 14 1 17
999 72 。 00 80S1211003墨水/白色 14 17 17
1 72 。 00 80S1211004墨水/白色 16 -1 15
-------------------------------- SAMPLE RESULTSET ------- -------
这是因为没有使用on_hand<> 0 （全部显示 - 无过滤）。
如果你仔细观察，这个结果集有额外的分支，即分支号码2 3 4 5. By添加过滤器我失去了这些我不想要的分支。我仍然希望在使用过滤器时保留它们。
branch_no weighted_av item_code x_value y_value on_hand PhysicalOH
999 72 。 00 80S1211001墨水/白色 10 16 16
999 72 。 00 80S1211002墨水/白色 12 19 19
1 72 。 00 80S1211003墨水/白色 14 -1 17
17 72 。 00 80S1211003墨水/白色 14 1 17
999 72 。 00 80S1211003墨水/白色 14 17 17
999 72 。 00 80S1211003墨水/白色 14 -1 17
2 72 。 00 80S1211003墨水/白色 14 0 17
3 < span class =code-digit> 72 。 00 80S1211003墨水/白色 14 < span class =code-digit> 0 17
4 72 。 00 80S1211003墨水/白色 14 0 17
5 72 。 00 80S1211003墨水/白色 14 0 17
1 72 。 00 80S1211004墨水/白色 16 -1 15
没有过滤器运行导致300K行崩溃报告服务。
---------------------------------- --------整个SQL QUERY --------------------------------------- ------
选择
ib .branch_no，
p.weighted_av，
p.item_code，
p.x_value，
p.y_value，
ib.on_hand，
p.on_hand as PhysicalOH，
ip.price，
i.item_code as StyleCode，
i.description，
i.cat1，
i.cat2，
i.cat3，
i.cat4，
np.is_style_yn，
si.supplier_code，
ysv.sort as YSort
来自 physical as p
left outer JOIN
item_branch as ib 上的ss =code-keyword> p.item_code = ib.item_code - 和ib.on_hand <> 0
INNER JOIN
item_price as ip on p.item_code = ip.item_code 和 ip.price_type = ' P1'
INNER JOIN
style_values as sv on p.style_code = sv.style_code 和 p.x_value = sv.value
INNER JOIN
style_values as ysv on p.style_code = ysv.style_code 和 p.y_value = ysv.value 和 ysv.axis = < span class =code-string>' Y'
INNER JOIN
ITEM < span class =code-keyword> as i on p.style_code = i.item_code
INNER JOIN
NON_PHYSICAL as np ON i.item_code = np.item_code 和 np.is_style_yn = 1
INNER JOIN
supplier_item as si ON i.item_code = si.item_code 和 si.pref_supp_no = 1
其中 - ib.on_hand<> 0和
sv.axis = ' X'
（ SELECT span> ITEM.item_code
FROM ITEM
INNER JOIN
NON_PHYSICAL ON ITEM.item_code = NON_PHYSICAL.item_code
LEFT JOIN
supplier_item ON Item.item_code = supplier_item.item_code 和 pref_supp_no = 1
WHERE NON_PHYSICAL。 is_style_yn = 1 和 ITEM.cat1 = ' Verge Sportswear Ltd'）
order by
si.supplier_code，
i.cat4，
i.cat3，
i.cat2，
i.cat1，
sv。 sort
这没有任何意义。如果你正在使用where，那么你就是在过滤。为什么要过滤你不关心的事情？为什么你不需要过滤时得到的记录不需要？你有没有重复，需要使用DISTINCT？
这里不清楚的是，你得到的数据太多，但是你的数据是什么'得到了，你不想要的数据？你希望过滤什么来获得少于300k的行？
I have a sql that is as under:
SELECT ib.branch_no,
ib.on_hand,
p.weightedav,
p.item_code,
FROM physical p
INNER JOIN
item_branch as ib on p.item_code = ib.item_code
WHERE ib.on_hand <> 0
This SQL returns only those branch_no that have on_hand <> 0.
I am trying to get all the branch_nos irrespective of the on_hand field, but while still using the where on_hand clause.
Taking the on_hand clause away solves my problem, but gives me large amount of un-needed rows with 0''s.
I am using SQL SERVER 2008 R2.
Thanks in advance for any guidance. Please apologize if I am missing any information.
--------------------------------SAMPLE RESULTSET --------------
This is the result of using on_hand<>0
branchno weighted_av item_code x_value y_value on_hand PhysicalOH
999 72.00 80S1211001 Ink/White 10 16 16
999 72.00 80S1211002 Ink/White 12 19 19
1 72.00 80S1211003 Ink/White 14 -1 17
17 72.00 80S1211003 Ink/White 14 1 17
999 72.00 80S1211003 Ink/White 14 17 17
1 72.00 80S1211004 Ink/White 16 -1 15
--------------------------------SAMPLE RESULTSET --------------
This is the result of NOT using the on_hand<>0 (SHOW ALL - NO FILTERING).
If you look closely this result set has extra branches i.e. Branch numbers 2 3 4 5. By adding the filter I lose these branches which I dont want to. I still want to retain them while I am using a filter.
branch_no weighted_av item_code x_value y_value on_hand PhysicalOH
999 72.00 80S1211001 Ink/White 10 16 16
999 72.00 80S1211002 Ink/White 12 19 19
1 72.00 80S1211003 Ink/White 14 -1 17
17 72.00 80S1211003 Ink/White 14 1 17
999 72.00 80S1211003 Ink/White 14 17 17
999 72.00 80S1211003 Ink/White 14 -1 17
2 72.00 80S1211003 Ink/White 14 0 17
3 72.00 80S1211003 Ink/White 14 0 17
4 72.00 80S1211003 Ink/White 14 0 17
5 72.00 80S1211003 Ink/White 14 0 17
1 72.00 80S1211004 Ink/White 16 -1 15
Running without a filter results in 300K rows which crashes reporting services.
------------------------------------------ENTIRE SQL QUERY ---------------------------------------------
select
ib.branch_no,
p.weighted_av,
p.item_code,
p.x_value,
p.y_value,
ib.on_hand,
p.on_hand as PhysicalOH,
ip.price,
i.item_code as StyleCode,
i.description,
i.cat1,
i.cat2,
i.cat3,
i.cat4,
np.is_style_yn,
si.supplier_code ,
ysv.sort as YSort
from physical as p
left outer JOIN
item_branch as ib on p.item_code = ib.item_code -- and ib.on_hand <> 0
INNER JOIN
item_price as ip on p.item_code = ip.item_code and ip.price_type = 'P1'
INNER JOIN
style_values as sv on p.style_code = sv.style_code and p.x_value = sv.value
INNER JOIN
style_values as ysv on p.style_code = ysv.style_code and p.y_value = ysv.value and ysv.axis = 'Y'
INNER JOIN
ITEM as i on p.style_code = i.item_code
INNER JOIN
NON_PHYSICAL as np ON i.item_code = np.item_code and np.is_style_yn = 1
INNER JOIN
supplier_item as si ON i.item_code = si.item_code and si.pref_supp_no = 1
where --ib.on_hand <> 0 and
sv.axis = 'X' and
i.item_code in
(SELECT ITEM.item_code
FROM ITEM
INNER JOIN
NON_PHYSICAL ON ITEM.item_code = NON_PHYSICAL.item_code
LEFT JOIN
supplier_item ON Item.item_code = supplier_item.item_code and pref_supp_no = 1
WHERE NON_PHYSICAL.is_style_yn = 1 and ITEM.cat1 = 'Verge Sportswear Ltd' )
order by
si.supplier_code,
i.cat4,
i.cat3,
i.cat2,
i.cat1,
sv.sort
This makes no sense. IF you''re using the where, then you''re filtering. Why do you want to filter on things you don''t care about ? Why is it the case that the records you get when you don''t filter, are not needed ? Are you getting duplicates and need to use DISTINCT ?
What is not clear here is, the data that you''re getting is too much, but what data that you''re getting, is data you don''t want ? What do you want to filter on to get less than 300k rows ?
这篇关于SQL查询帮助的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
