2008中以名义方式显示结果

2008中以名义方式显示结果

本文介绍了SQL查询在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

推荐答案

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中以名义方式显示结果的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-13 21:16