我查看了示例,我认为我应该使用 JOIN 但我无法获得正确的语法,它只是错误。我需要根据条件返回一个显示计数的表格。
我的程序看起来像这样
SELECT SalesMan, Count(SalesMan) AS SalesCount
FROM Customers
WHERE SaleDate Between @BeginDate And @EndDate AND Status = 'SOLD'
GROUP BY SalesMan
ORDER BY SalesCount DESC
SELECT DISTINCT SalesMan2, Count(Salesman2) AS HalfCount
FROM Customers
WHERE SaleDate Between @BeginDate And @EndDate AND Status = 'SOLD'
GROUP BY SalesMan2
ORDER BY HalfCount DESC
这返回两个看起来像这样的表
SalesMan | SalesCount
BOB 8
ANDY 5
JOE 3
SalesMan2 | HalfCount
(blank) 40
ANDY 1
JACOB 1
ROB 1
JOE 1
我试图使工作的逻辑是.. 如果他们是推销员加 1,如果他们是推销员 2 然后加 0.5。另外,我在第二张桌子上放了一堆我不在乎的空酒。我不需要那些。
我需要的是让它返回这个
Salesman | SalesCount
BOB 8
ANDY 5.5
JOE 3.5
JACOB .5
ROB .5
我看过很多答案,但他们使用了带有 where 子句和字符串的 Join。他们也知道他们应该匹配什么价值。任何为我指明正确方向的帮助将不胜感激。提前致谢。
一种方法使用union all
:
SELECT SalesMan, SUM(cnt) AS SalesCount
FROM ((select c.SalesMan, 1 as cnt
FROM Customers c
WHERE SaleDate Between @BeginDate And @EndDate AND Status = 'SOLD'
) UNION ALL
(SELECT SalesMan2, 0.5 as cnt
FROM Customers c
WHERE SaleDate Between @BeginDate And @EndDate AND Status = 'SOLD'
)
) c
GROUP BY SalesMan;
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句