SQL查询在SQL Server 2008中以名义方式显示结果 [英] Sql query to display result namewise in SQL server 2008
问题描述
您好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
推荐答案
首先要注意的是，当我针对您提供的数据运行查询时，我没有得到结果声称得到。你也不是。
其次，你应该将image_count，docs_count，fieldscount和duration存储为数字列类型，因此不需要使用CONVERT（INT，...
你有一些相当复杂的自联接。通过使用任何一个都可以简化整个批次子查询或公用表表达式。
考虑这个子查询（我使用'sharo'作为示例，因为有3种类型的work_areas用于该资源
First point to note, when I run your query against the data you have provided I do not get the results you claim to get. Nor do you.
Secondly, you should be storing image_count, docs_count, fieldscount and duration as numeric column types, so there is no need to useCONVERT(INT,...
You have some fairly complex self-joins there. The whole lot could be simplified by using either a sub-query or a Common Table Expression.
Consider this sub-query (I'm using 'sharo' as the example as there are 3 types of work_areas for that resource
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
您的具体要求是什么？请详细说明。 GROUP BY可以做到这一点，而不是这样复杂的查询。但是，您的详细说明可能有助于响应。请举例说明所需输出的外观。
What is your exact requirement? Kindly elaborate. GROUP BY can do the trick instead of such a complex query. However, your elaboration may help for the response. Kindly give example of how the desired output should look like.
这篇关于SQL查询在SQL Server 2008中以名义方式显示结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
