代码之家 › 专栏 › 技术社区 › Rich

SQL中的条件计数返回

conditional sql

Rich · 技术社区 · 8 年前

我已经看过了这些示例,我认为应该使用连接,但语法不正确,只会出错。我要退一张桌子显示基于标准的计数。

我的程序是这样的

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。另外,我在第二张桌子上得到了一堆我不在乎的空酒杯。我不需要这些。

我需要的是让它归还这个

Salesman | SalesCount

BOB       8
ANDY      5.5
JOE       3.5
JACOB     .5
ROB       .5

我已经看了很多答案,但它们使用连接where子句和字符串。他们也知道自己应该达到什么价值。如果能为我指明正确的方向,我将不胜感激。提前谢谢。

3 回复 | 直到 8 年前

Gordon Linoff 8 年前

一种方法使用 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;

Tom H zenazn 8 年前

数据库未规范化,这将使此查询比需要的更困难。尽管如此,我还是从这里开始:

SELECT
    COALESCE(NULLIF(SalesMan, ''), NULLIF(SalesMan2, '')),
    SUM(CASE WHEN COALESCE(SalesMan, '') <> '' THEN 1.0 ELSE 0.0 END) +
        SUM(CASE WHEN COALESCE(SalesMan2, '') <> '' THEN 0.5 ELSE 0.0 END) AS SalesCount
FROM Customers C1
FULL OUTER JOIN Customers C2 ON
    C2.SalesMan = C1.SalesMan2 AND
    C2.Status = 'SOLD' AND
    C2.SaleDate BETWEEN @BeginDate AND @EndDate
WHERE
    C1.Status = 'SOLD' AND
    C1.SaleDate BETWEEN @BeginDate AND @EndDate
GROUP BY
    COALESCE(NULLIF(SalesMan, ''), NULLIF(SalesMan2, ''))

由于您没有包含设置测试场景的代码,因此这是未经测试的,我对此一无所知。如果没有给出正确的结果,请告诉我。

Rich 8 年前

我真的很感谢你的帮助。我尝试了两种解决方案,Gordon的解决方案是现成的。

工作程序如下

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' AND 
   SalesMan2 <> ''
  )
 ) c
GROUP BY SalesMan ORDER BY SalesCount DESC ;

我添加了和销售人员2<>“”还有按顺序排列的条款,这样做很好。

我也在使用MSSQL。以后我会记得贴出来的。