SukirtiShetty Ответов: 2

Sql-запрос для отображения результата namewise в SQL server 2008

Привет
Я написал следующий запрос, чтобы получить общее количество конкретных пользователей для создания производственного отчета

Может кто-нибудь, пожалуйста, помочь мне в этом.

Что я уже пробовал:

Мой SQL-запрос:
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'

и я получаю результаты, как показано ниже:
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

и я хочу получить результат, как показано ниже
resources    Coding QC QA Total
Sam	     50      10  15   75

Данные таблицы Mt приведены ниже
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

vivvicks

можно ли вставить входные данные или табличные данные, в которые вы пытаетесь выполнить запрос

SukirtiShetty

Я обновил табличные данные в своей панели вопросов.

vivvicks

данные U-тест вставить, таблица.

vivvicks

являются ли "тест" и" работа " двумя разными таблицами? как вы уже упоминали в запросе..если да, то будет ли u вставлять данные рабочей таблицы pls?

SukirtiShetty

да, это тестовая таблица, только я обновил ее в вопросе. Я использую здесь одну таблицу

2 Ответов

Рейтинг:
14

CHill60

Во-первых, когда я выполняю ваш запрос по данным, которые вы предоставили, я не получаю результатов, которые вы утверждаете. И ты тоже.

Во-вторых, вы должны хранить image_count, docs_count, fieldscount и duration как числовые типы столбцов, поэтому нет необходимости использовать CONVERT(INT,...

У вас там есть довольно сложные самосоединения. Все это можно упростить, используя либо подзапрос, либо общее табличное выражение.

Рассмотрим этот подзапрос (я использую 'sharo' в качестве примера, так как для этого ресурса существует 3 типа work_areas

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
Это даст результаты 
resources work_area     imgs    docs    dur
sharo	  Coding	500	70	0
sharo	  QA	        801	0	10
sharo	  QC	        0	160	0
(Я не сделал ни деления на 4, ни умножения на 5 не только потому, что, честно говоря, не понимаю вашего алгоритма, но и потому, что мы пока не хотим этого делать.

Многие люди теперь предложили бы использовать PIVOT для получения данных из строк в столбцы, но учтите, что общие табличные выражения можно рассматривать как таблицу, поэтому мы можем сделать самостоятельное соединение с этим CTE для каждого из 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 в одном и том же запросе, как это
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 я попытался найти способ упростить все это и вместо этого придумал вот что:
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
Имейте в виду, что я не тестировал это на том же уровне, на котором тестировал свое предыдущее решение

Рейтинг:
1

Debarshi Chakraborty

Каковы ваши требования? Будьте добры, уточните. GROUP BY может сделать трюк вместо такого сложного запроса. Тем не менее, ваша разработка может помочь для ответа. Пожалуйста, приведите пример того, как должен выглядеть желаемый результат.

vivvicks

именно так

CHill60

Пожалуйста, не размещайте вопросы или комментарии в качестве решений. Он удаляет сообщение из списка неотвеченных вопросов, а это означает, что меньше людей увидят этот вопрос.
Пожалуйста, удалите это решение