我正在使用SQL Server Manager 2008。
我有一个查询，如下所示：
SELECT dbo.iLines.Part,
dbo.iLines.Pg,
SUM(dbo.iLines.Qty) as sales6months,
dbo.iLines.Prefix
FROM
Autopart.dbo.iLines
RIGHT JOIN
dbo.product
ON
dbo.product.keycode = dbo.ilines.part
where prefix = 'i'
and ([datetime] > dateadd(month, -6, getdate()))
and dbo.ilines.part = 'BK939'
group by
dbo.ilines.pg,
dbo.ilines.part,
dbo.ilines.prefix
order by sales6months desc
因此，请解释一下，我想获得产品表中所有产品的最近6个月的销售额。
问题在于某些产品将不会有任何销售。我仍然需要他们展示。
因此，我要问的是显示所有产品（包括0个销售）的最近6个月的销售情况。
“ iLines”是导致问题的表格，因为零件号仅在有销售的情况下才存在。
我知道可能有多种方法可以执行多个查询等。但是我只需要在1个查询中进行所有查询。
我试着让null通过，但什么也不做，加上使用null的效果真的很吓人。
任何要添加到该查询中的代码片段都将非常棒！！
非常感谢！
另外，如果您需要更多信息，请询问！
更新！
是的，抱歉，日期时间仅存在于Ilines中。
Ilines =销售表
产品=我们所有产品的表格
这是主要的查询，它的意思是在过去6个月中拉出销量最高的“ n”个零件。
它可以正常工作，但就像我说的那样，它不显示未售出的零件。
ALTER PROCEDURE [dbo].[MyPareto]
@pgParam varchar(255)
AS
SELECT
i.pg,
dbo.OldParetoAnalysis.Pareto,
i.part,
i.sales6months,
a.LostSales6Months,
dbo.NewParetoAnalysis.Pareto
FROM
OPENQUERY(SACBAUTO, 'SELECT dbo.iLines.Part,
dbo.iLines.Pg,
SUM(dbo.iLines.Qty) as sales6months,
dbo.iLines.Prefix
FROM Autopart.dbo.iLines
where prefix = ''i''
and [datetime] > dateadd(month, -6, getdate())
group by
dbo.ilines.pg,
dbo.ilines.part,
dbo.ilines.prefix
order by sales6months desc') i
RIGHT JOIN
dbo.OldParetoAnalysis
on
i.part collate SQL_Latin1_General_CP1_CI_AS = dbo.OldParetoAnalysis.Part
INNER JOIN
dbo.NewParetoAnalysis
ON
dbo.OldParetoAnalysis.Part collate SQL_Latin1_General_CP1_CI_AS = dbo.NewParetoAnalysis.Part
LEFT JOIN
OPENQUERY(SACBAUTO, 'SELECT dbo.aLines.Part,
dbo.aLines.Pg,
SUM(dbo.aLines.Qty) as LostSales6Months,
dbo.aLines.Prefix
FROM Autopart.dbo.aLines
where prefix = ''d''
and [datetime] > dateadd(month, -6, getdate())
group by
dbo.alines.pg,
dbo.alines.part,
dbo.alines.prefix
order by LostSales6Months desc') a
ON
dbo.NewParetoAnalysis.Part collate SQL_Latin1_General_CP1_CI_AS = a.part
WHERE
i.pg = @pgParam
GROUP BY
i.pg,
dbo.OldParetoAnalysis.Pareto,
i.part,
i.sales6months,
a.LostSales6Months,
dbo.NewParetoAnalysis.Pareto
ORDER BY
dbo.OldParetoAnalysis.Pareto asc
将pareto视为最好的联盟。
希望这会有所帮助，我试图避免添加它，因为它可能会使某些人不愿发表评论。
好的新更新，此打开的查询有效！！！
SELECT
dbo.product.Keycode,
dbo.iLines.Pg,
SUM(COALESCE(dbo.iLines.Qty, 0)) as sales6months,
dbo.iLines.Prefix
FROM
Autopart.dbo.product
LEFT OUTER JOIN
Autopart.dbo.ilines
ON
dbo.product.keycode = dbo.ilines.part
AND ([datetime] > dateadd(month, -6, getdate()) OR [datetime] is null )
WHERE
(dbo.iLines.Prefix = 'i' OR dbo.iLines.Prefix is null)
AND dbo.product.keycode = 'BK939'
group by
dbo.ilines.pg,
dbo.product.keycode,
dbo.ilines.prefix
order by sales6months desc#
但是当我然后按如下方式加入我的pareto表时：
SELECT
i.pg,
dbo.OldParetoAnalysis.Pareto,
i.keycode,
i.sales6months
FROM
OPENQUERY(SACBAUTO, 'SELECT
dbo.product.Keycode,
dbo.iLines.Pg,
SUM(COALESCE(dbo.iLines.Qty, 0)) as sales6months,
dbo.iLines.Prefix
FROM
Autopart.dbo.product
LEFT OUTER JOIN
Autopart.dbo.ilines
ON
dbo.product.keycode = dbo.ilines.part
AND ([datetime] > dateadd(month, -6, getdate()) OR [datetime] is null )/* must be this*/
WHERE
(dbo.iLines.Prefix = ''i'' OR dbo.iLines.Prefix is null)
group by
dbo.ilines.pg,
dbo.product.keycode,
dbo.ilines.prefix
order by sales6months desc') i
LEFT JOIN
dbo.OldParetoAnalysis
on
i.keycode collate SQL_Latin1_General_CP1_CI_AS = dbo.OldParetoAnalysis.Part
WHERE i.pg = '40' AND i.keycode = 'BK939'
结果返回相同，所以这意味着问题是当我加入联接时，但旧的pareto确实包含零件号，而我也尝试更改联接...。我希望这可以缩小对问题的搜索范围我希望有人知道为什么会这样！
最后更新！
哇，这真是麻烦，但最后我发现了问题，我不得不使用产品表PG字段重新检查！因为它不会为空！这是代码！
ALTER PROCEDURE [dbo].[MyPareto32TEST]
@pgParam varchar(255)
AS
SELECT
i.pg,
dbo.OldParetoAnalysis.Pareto,
i.keycode,
i.sales6months,
a.LostSales6Months,
dbo.NewParetoAnalysis.Pareto
FROM
OPENQUERY(SACBAUTO, 'SELECT
dbo.product.Keycode,
dbo.iLines.Pg,
dbo.product.pg as ppg,
SUM(COALESCE(dbo.iLines.Qty, 0)) as sales6months,
dbo.iLines.Prefix
FROM
Autopart.dbo.product
LEFT OUTER JOIN
Autopart.dbo.ilines
ON
dbo.product.keycode = dbo.ilines.part
AND ([datetime] > dateadd(month, -6, getdate()) OR [datetime] is null )
WHERE
(dbo.iLines.Prefix = ''i'' OR dbo.iLines.Prefix is null)
group by
dbo.ilines.pg,
dbo.product.keycode,
dbo.ilines.prefix,
dbo.product.pg
order by sales6months desc') i
RIGHT JOIN
dbo.OldParetoAnalysis
on
i.keycode collate SQL_Latin1_General_CP1_CI_AS = dbo.OldParetoAnalysis.Part
AND (i.pg = @pgParam or (i.pg is null AND i.ppg = @pgParam))
INNER JOIN
dbo.NewParetoAnalysis
ON
dbo.OldParetoAnalysis.Part collate SQL_Latin1_General_CP1_CI_AS = dbo.NewParetoAnalysis.Part
LEFT JOIN
OPENQUERY(SACBAUTO, 'SELECT
dbo.product.Keycode,
dbo.aLines.Pg,
SUM(COALESCE(dbo.aLines.Qty, 0)) as lostsales6months,
dbo.aLines.Prefix
FROM
Autopart.dbo.product
LEFT OUTER JOIN
Autopart.dbo.alines
ON
dbo.product.keycode = dbo.alines.part
AND ([datetime] > dateadd(month, -6, getdate()) OR [datetime] is null )
WHERE
(dbo.aLines.Prefix = ''d'' OR dbo.aLines.Prefix is null)
group by
dbo.alines.pg,
dbo.product.keycode,
dbo.alines.prefix
order by lostsales6months desc') a
ON
dbo.NewParetoAnalysis.Part collate SQL_Latin1_General_CP1_CI_AS = a.keycode
WHERE(i.pg = @pgParam or (i.pg is null AND i.ppg = @pgParam) AND dbo.NewParetoAnalysis.Pareto is not null)
GROUP BY
i.pg,
dbo.OldParetoAnalysis.Pareto,
i.keycode,
i.sales6months,
a.LostSales6Months,
dbo.NewParetoAnalysis.Pareto
ORDER BY
dbo.OldParetoAnalysis.Pareto asc
最佳答案
以下条件过滤出right join失败的任何行：
dbo.ilines.part = 'BK939'
要解决此问题，请将条件移至联接：
RIGHT JOIN
dbo.product
ON
dbo.product.keycode = dbo.ilines.part
and dbo.ilines.prefix = 'i'
and dbo.ilines.part = 'BK939'
where
([datetime] > dateadd(month, -6, getdate()))
假设
[datetime]是product的列，则可以将其保留在where子句中。