案例/何时以及如何在cte中使用完全相同的查询 [英] Case/when and how to use exactly the query in cte

问题描述

- 有两张桌子



- 从请求中选择*

--There are two tables

--select * from request

hub_id	totalrequest
9	45
20	55
136	75

select * from route_header
<pre>
action1 routid  hub_id  occ
p        1       9       4
p        2       9       3
p        3       9       2
d        4       9       4
d        5       9       1
p        6       20      3
p        7       20      3
p        8       20      4
p        9       20      4
d       10       20      5
p       11      136      2
p       12      136      1
d       13      136      5
d       14      136      3
d       15      136      4
</pre>

--------------------- o / p我想要的---------------- -----------

---------------------o/p which i want ---------------------------

hub  totalrequest    totalroutes totalemployee Realoccupancy  pickcount  dropcount
9	   45	         5	      14	      2          3           2
20	   55	         5	      19	      3          4           1
136	   75	         5	      15	      3          2           3

-------------------- --------------------------------------------



- 我想在不使用光标/ while循环的情况下以基于集合的方式进行。



- 我有什么到目前为止尝试了

----------------------------------------------------------------

-- I want to do it in a set based way without using cursor/while loop.

--what i have tried so far

;with cte(hub,totalrequest,totalroutes,totalemployee) as
(
select
rd.hub_id,
r.totalrequest,
count(rd.routid) as Totalroutes
,sum(occ) as totalemployee
from request r
join route_header rd on r.hub_id=rd.hub_id
group by
rd.hub_id,
r.totalrequest
),
cte1 as
(
select hub,
totalrequest,
totalroutes,
totalemployee,
floor(totalemployee/totalroutes) as Realoccupancy from  cte
)
select * from cte1

-----------------------------------------------------------------------------
--where i stuck is (till realoccupancy everything is fine) 
-->>but pickcount and dropcount i am unable to do it

--i was trying to use case when ...but failed to do it.
--just after this statement in cte
,sum(occ) as totalemployee

i have to write the logic to get the pickcount and dropcount in cte...and then i will fetch in cte1.

--tell me the way

-------------------------------------------------------------------------------
if you have other solution also help me.

推荐答案

Christian Graus的Solution1非常好。除此之外，我想提供另一种方式 - 使用子查询。看看例子：

Solution1 by Christian Graus is very good. In addition to it i'd like to provide another way - using subqueries. Have a look at example:

CREATE TABLE #request (hub_id  INT, totalrequest INT)

INSERT INTO #request (hub_id, totalrequest)
VALUES(9, 45),
(20, 55),
(136, 75)

CREATE TABLE #route_header (action1 VARCHAR(30), routid INT,  hub_id  INT, occ INT)

INSERT INTO #route_header (action1, routid , hub_id,  occ)
VALUES('p', 1, 9, 4),
('p', 2, 9, 3),
('p', 3, 9, 2),
('d', 4, 9, 4),
('d', 5, 9, 1),
('p', 6, 20, 3),
('p', 7, 20, 3),
('p', 8, 20, 4),
('p', 9, 20, 4),
('d', 10, 20, 5),
('p', 11, 136, 2),
('p', 12, 136, 1),
('d', 13, 136, 5),
('d', 14, 136, 3),
('d', 15, 136, 4)

SELECT t1.hub_id, t1.totalrequest , t1.Totalroutes, t1.TotalEmployee, FLOOR(t1.TotalEmployee / t1.Totalroutes) AS RealOccupancy,
    (SELECT COUNT(action1) FROM #route_header WHERE action1 = 'p' AND hub_id = t1.hub_id) AS PickCount,
    (SELECT COUNT(action1) FROM #route_header WHERE action1 = 'd' AND hub_id = t1.hub_id) AS DropCount
FROM (
    SELECT r.hub_id, r.totalrequest, COUNT(rh.routid) AS Totalroutes, SUM(rh.occ) AS TotalEmployee
    FROM #request AS r INNER JOIN  #route_header AS rh ON r.hub_id = rh.hub_id
    GROUP BY r.hub_id, r.totalrequest
    ) AS t1


DROP TABLE #request
DROP TABLE #route_header

结果（按预期）：

Result (as expected):

hub_id  to.req. to.rou. to.emp. Re.Occ. PickCo. DropCo.
9	45	5	14	2	3	2
20	55	5	19	3	4	1
136	75	5	15	3	2	3

为您想要的每个组创建一个CTE。创建一个试图分组三件事的选择太难了。我还会创建不互相引用的CTE，如果可以的话，只需在最终的SQL中加入结果表。它更具可读性。创建SQL以创建标量值列表，使用SQL创建三个计算值。然后在最后的SQL语句中加入所有四个表来获得结果。





因为你给了我们你的数据，我能够创建表和解决方案：

Create a CTE for each group you want. Creating one select that tries to group on three things is too hard. I would also create CTEs that don't reference each other, and just join the resultant tables in my final SQL, if I could. It's just more readable that way. Create SQL to create your list of scalar values, and SQL to get your three calculated values. Then join all four tables in your final SQL statement to get your result.


Because you gave us your data, I was able to create your tables and a solution:

;with root(hubid,totalrequest,totalroutes,totalemployee) as
(
select
rd.hub_id,
r.totalrequest,
count(rd.routid) as Totalroutes
,sum(occ) as totalemployee
from request r
join route_header rd on r.hub_id=rd.hub_id
group by
rd.hub_id,
r.totalrequest
),
totals(hubId, pickCount,dropCount) as
(
select hub_id,
sum(CASE WHEN action1 = 'p' THEN 1 ELSE 0 end) AS pickcount,
sum(CASE WHEN action1 = 'd' THEN 1 ELSE 0 end) AS dropcount
FROM route_header
GROUP BY hub_id
)

select r.*, floor(totalemployee/totalroutes) as Realoccupancy, pickCount, dropCount from root r INNER JOIN totals t ON r.hubid = t.hubid

请注意，我让每个CTE对解决方案的一部分负责，我移动了一些计算到最终的SQL。我的目标是让每个部分都有一个明确的任务，并且整体易于维护。

Note that I made each CTE responsible for one part of the solution and I moved some of the calculation to the final SQL. My goal was for each part to have a clear task, and for the whole to be easily maintained.

这篇关于案例/何时以及如何在cte中使用完全相同的查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！