问题描述
您好b $ b
我写了以下查询以获取特定用户的总数来制作生产报告
可以有人请帮帮我。
我尝试过：
我的Sql查询是：
Hi
I wrote the below query to get total count of particular user to produce the production report
Can someone please help me on this.
What I have tried:
My Sql Query is:
COALESCE(SUM(CONVERT(INT, b.image_count)), 0) AS Coding,
(COALESCE(SUM(CONVERT(INT, e.image_count)), 0)/4) AS QC,
(COALESCE(SUM(CONVERT(INT, q.duration*5.0)), 0)) AS QA,
(COALESCE(SUM(CONVERT(INT, b.image_count)), 0)) +(COALESCE(SUM(CONVERT(INT, e.image_count)), 0)/4) + (COALESCE(SUM(CONVERT(INT, q.duration*5.0)), 0))
AS Total
FROM
test a
LEFT JOIN test b ON a.resources = b.resources and a.testid_PK=b.testid_PK AND (b.Work_area='Coding') and YEAR(b.dat_e) = '2017' AND MONTH(b.dat_e) = '04' and b.resources='Sam'
LEFT JOIN test e ON a.resources = e.resources and a.testid_PK=e.testid_PK AND (e.Work_area='QC') and YEAR(e.dat_e) = '2017' AND MONTH(e.dat_e) = '04' and e.resources='Sam'
LEFT JOIN test q ON a.resources = q.resources and a.testid_PK=q.tesstid_PK AND (q.Work_area='QA') and YEAR(q.dat_e) ='2017' AND MONTH(q.dat_e) = '04' and q.resources='Sam'
我得到的结果如下：
and I am getting results like below:
resources Coding QC QA Total
Sam 50 10 15 75
Rita 0 0 0 0
Mary 0 0 0 0
sharo 0 0 0 0
我希望输出如下
and I want the output like below
resources Coding QC QA Total
Sam 50 10 15 75
Mt表数据如下所示
Mt table data is like below
testid_PK dat_e resources work_area image_count doc_count status duration fieldscount
10926 4/1/2017 Sam Coding 0 14 Completed 0 8
10927 4/1/2017 Mary Coding 0 28 Completed 0 8
10928 4/1/2017 Sam Coding 0 46 Completed 0 8
10929 4/1/2017 Rita Coding 0 82 Completed 0 8
10930 4/2/2017 Sam Coding 0 16 Completed 0 8
10931 4/2/2017 Mary Coding 0 22 Completed 0 8
10932 4/2/2017 Sam Coding 0 66 Completed 0 8
10933 4/2/2017 Mary Coding 0 46 Completed 0 8
10934 4/2/2017 sharo QC 0 160 Completed 0 8
10935 4/2/2017 Rita QC 0 25 Completed 0 8
10936 4/3/2017 Rita QC 0 125 Completed 0 8
11284 4/4/2017 sharo Coding 500 0 Completed 0 0
11285 4/4/2017 Sam Coding 200 1 Completed 0 0
11286 4/4/2017 Rita Coding 101 0 Completed 0 0
11287 4/4/2017 sharo QA 801 0 Completed 10 0
11288 4/4/2017 Sam Coding 0 69 Completed 0 12
11289 4/4/2017 sharo Coding 0 70 Completed 0 12推荐答案
declare @res nvarchar(120) = 'sharo'
select resources, work_area, sum(image_count) as imgs, sum(doc_count) as docs, sum(duration) as dur
from test
WHERE YEAR(dat_e) = 2017 AND MONTH(dat_e) = 4 and resources = @res
group by resources, work_area这将给出结果
That will give the results
resources work_area imgs docs dur
sharo Coding 500 70 0
sharo QA 801 0 10
sharo QC 0 160 0（我没有做任何除法4或乘以5不仅仅是因为我老实说你不理解你的算法，而是因为我们还不想这样做。
很多人现在建议使用PIVOT来获取数据从行到列，但考虑到公用表表达式可以像表一样对待，所以我们可以做一个自己加入每个work_area的CTE
(I haven't done any of the division by 4 or multiplication by 5 not just because I honestly do not understand your algorithm but because we don't want to do that yet.
Many people would now suggest using a PIVOT to get the data from rows into columns, but consider that Common Table Expressions can be treated like a table, so we can do a self join to that CTE for each of the work_area
declare @res nvarchar(120) = 'sharo'
;WITH CTE AS
(
select resources, work_area, sum(image_count) as imgs, sum(doc_count) as docs, sum(duration) as dur
from test
WHERE YEAR(dat_e) = 2017 AND MONTH(dat_e) = 4 and resources = @res
group by resources, work_area
)
SELECT C1.resources,
ISNULL(C1.imgs,0) AS CodingImages, ISNULL(C1.docs,0) AS CodingDocs, ISNULL(C1.dur,0) AS CodingDur,
ISNULL(C2.imgs,0) AS QCImages, ISNULL(C2.docs,0) AS QCDocs, ISNULL(C2.dur,0) AS QCDur,
ISNULL(C3.imgs,0) AS QAImages, ISNULL(C3.docs,0) AS QADocs, ISNULL(C3.dur,0) AS QADur
FROM CTE C1
LEFT JOIN CTE C2 ON C1.resources = C2.resources AND C2.work_area = 'QC'
LEFT JOIN CTE C3 ON C2.resources = C3.resources AND C3.work_area = 'QA'
WHERE C1.work_area = 'Coding'其中显示了结果
sharo 500 70 0 0 160 0 801 0 10注意我使用过 ISNULL 而不是 COALESCE 。当只涉及两个可能的值时，后者的性能稍差。
关于CTE的另一个好处是你可以在同一个查询中拥有多个CTE，如
Note I've used ISNULL rather than COALESCE. The latter is slightly less performant when there are only two possible values involved.
The other nice thing about CTE's is that you can have multiple CTEs within the same query like this
declare @res nvarchar(120) = 'Sam'
;WITH CTE AS
(
select resources, work_area, sum(image_count) as imgs, sum(doc_count) as docs, sum(duration) as dur
from test
WHERE YEAR(dat_e) = 2017 AND MONTH(dat_e) = 4 and resources = @res
group by resources, work_area
), CTE2 AS
(
SELECT C1.resources,
ISNULL(C1.imgs,0) AS CodingImages, ISNULL(C1.docs,0) AS CodingDocs, ISNULL(C1.dur,0) AS CodingDur,
ISNULL(C2.imgs,0) AS QCImages, ISNULL(C2.docs,0) AS QCDocs, ISNULL(C2.dur,0) AS QCDur,
ISNULL(C3.imgs,0) AS QAImages, ISNULL(C3.docs,0) AS QADocs, ISNULL(C3.dur,0) AS QADur
FROM CTE C1
LEFT JOIN CTE C2 ON C1.resources = C2.resources AND C2.work_area = 'QC'
LEFT JOIN CTE C3 ON C2.resources = C3.resources AND C3.work_area = 'QA'
WHERE C1.work_area = 'Coding'
)
SELECT resources, CodingImages AS Coding,QCImages/4 AS QC, QADur*5.0 AS QA,
CodingImages + QCImages/4 + QADur*5.0 AS Total
FROM CTE2 该查询返回与原始查询相同的结果（虽然我省略了WHERE子句 - 您必须将其重新放入年份和月份）。再次注意，您声称期望的结果与您提供的数据不符。
我回到此处。随着CTE解决方案的工作，我试图找到一种方法来简化它，并提出了相反的想法：
That query returns the same results as your original (although I have omitted the WHERE clause - you will have to put it back in for Year and Month). Again note that the results you claim to be expecting do not match the data you provided.
I came back to this. With the CTE solution working I tried to find a way to simplify it all and came up with this instead:
declare @res nvarchar(120) = 'Sam'
SELECT resources,
SUM(CASE WHEN work_area = 'Coding' THEN ISNULL(image_count,0) ELSE 0 END) AS CodingImages,
SUM(CASE WHEN work_area = 'QC' THEN ISNULL(image_count,0) ELSE 0 END) / 4 AS QCImages,
SUM(CASE WHEN work_area = 'QA' THEN ISNULL(duration,0) ELSE 0 END) * 5.0 AS QADur
FROM test
WHERE YEAR(dat_e) = 2017 AND MONTH(dat_e) = 4 and resources = @res
GROUP BY resources请注意，我没有将此测试测试到我测试的相同级别我之前的解决方案
Be aware that I haven't tested this to the same level I tested my earlier solution
这篇关于SQL查询在SQL Server 2008中以名义方式显示结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！