时间戳

首页 > 解决方案 > 使用联合将不同的查询与不同的组连接起来

sql - 使用联合将不同的查询与不同的组连接起来

问题描述

我有不同的查询（3），它们有不同的条件来获得不同的计数器。

在其中 2 个中，分组是相同的，但在其中一个中没有。

计数线拣选

select 
carusrrsppck as 'users', 
COUNT(carusrrsppck) AS 'lines picking', 
cardepid as 'deposit'  
from order_products
where DATEADD(dd, 0, DATEDIFF(dd, 0, carfchcar)) 
between '20190220' and '20190220' 
and cardepid in ('D32', 'DA', 'DA1', 'DB', 'DC', 'DD', 'DE', 'DR')
and carusrrsppck<>''
and ordprdcnt<>0
group by carusrrsppck, cardepid
order by cardepid, COUNT(*) DESC

拒绝计数行

select carusrrsppck as 'users', 
COUNT(carusrrsppck) as 'lines denied',
cardepid as 'deposit'
from order_products
where DATEADD(dd, 0, DATEDIFF(dd, 0, carfchcar)) 
between '20190220' and '20190220' 
and ordprdcnt=0
and cardepid in ('D32', 'DA', 'DA1', 'DB', 'DC', 'DD', 'DE', 'DR')
and carusrrsppck<>''
group by carusrrsppck, cardepid
order by cardepid, COUNT(*) DESC

确认计数线

select 
carusrcar as 'user', 
COUNT(carusrcar) as 'lines confirmed',
cardepid as 'deposit' 
from order_products 
where DATEADD(dd, 0, DATEDIFF(dd, 0, carfchcar)) 
between '20190220' and '20190220' 
and cardepid in ('D32', 'DA', 'DA1', 'DB', 'DC', 'DD', 'DE', 'DR')
group by carusrcar, cardepid
order by cardepid, COUNT(*) DESC

是否可以加入这 3 个查询，即使它们的分组方式不同，或者我只能加入那些具有相同“分组依据”的查询？

谢谢。

标签： sqlsql-server

解决方案

Count lines picking和查询使用相同的Count lines denied分组列，并且仅在条件上有所不同，因此可以将它们简化为：

SELECT 
  carusrrsppck as 'users', 
  SUM(CASE WHEN carusrrsppck <> '' and ordprdcnt <> 0 THEN 1 ELSE 0 END) AS 'lines picking', 
  SUM(CASE WHEN carusrrsppck <> '' THEN 1 ELSE 0 END) AS 'lines denied',
  cardepid AS 'deposit'  
FROM order_products
WHERE 
  DATEADD(dd, 0, DATEDIFF(dd, 0, carfchcar)) BETWEEN '20190220' and '20190220' 
  AND 
  cardepid IN ('D32', 'DA', 'DA1', 'DB', 'DC', 'DD', 'DE', 'DR')
GROUP BY carusrrsppck, cardepid
ORDER BY cardepid, COUNT(*) DESC

查询是不同的Count lines confirmed情况，不能与上述情况相关联。

推荐阅读