Я хочу подсчитать оповещения кандидатов по округам.
Ниже приведена таблица поиска предупреждений по районам.
Table_LKP_AlertMastInfo
DistrictID FieldName AlertOptionValue
71 AreYouMarried Yes
71 Gender Female
72 AreYouMarried Yes
Приведенное выше имя поля Table_LKP_AlertMastInfo должно сравниваться с полями table_RegistrationInfo, чтобы проверить значение AlertOptionValue для получения подсчета.
Ниже представлена таблица сведений о кандидате:
Table_RegistrationInfo
CandidateId DistrictID AreYouMarried Gender
Can001 71 Yes Female
Can002 71 No Female
Can003 72 Yes Man
Can004 72 No Man
Я хочу вывести, как показано ниже:
Can001 2
Can002 1
Can003 1
Объяснение приведенных выше выходных значений:
Can001 have selected AreYouMarried:Yes and Gender:Female then count value 2
Can002 have selected Gender:Female then count value 1
Can003 have selected AreYouMarried:Yes then count value 1
Can004 have not alerts
Мне удалось получить ожидаемый результат без использования динамических запросов. Не уверен, что это то, что вы ищете:
SELECT DISTINCT
c.CandidateId, SUM(a.AreYouMarriedAlert + a.GenderAlter) AS AlterCount
FROM
Table_RegistrationInfo c
OUTER APPLY
(
SELECT
CASE
WHEN a.FieldName = 'AreYouMarried' AND c.AreYouMarried = a.AlertOptionValue THEN 1
ELSE 0
END AS AreYouMarriedAlert,
CASE
WHEN a.FieldName = 'Gender' AND c.Gender = a.AlertOptionValue THEN 1
ELSE 0
END AS GenderAlter
FROM
Table_LKP_AlertMastInfo a
WHERE
a.DistrictID = c.DistrictID
) a
GROUP BY c.CandidateId
HAVING SUM(a.AreYouMarriedAlert + a.GenderAlter) > 0
Результаты:
Пожалуйста, не передавайте статическое значение a.FieldName = 'AreYouMarried' и a.FieldName = 'Gender' . У меня есть 100 новых полей для менеджера. @Пратик Бхавсар
Я думаю, курсор поможет для динамики?
@mohdmazharkhan Не уверен, я плохо знаком с курсорами
Вот один простой способ сделать это:
SELECT subq.*
FROM
(SELECT CandidateId,
(SELECT COUNT(*)
FROM Table_LKP_AlertMastInfo ami
WHERE ami.DistrictID = ri.DistrictID
AND ami.FieldName ='AreYouMarried'
AND ami.AlertOptionValue = ri.AreYouMarried) +
(SELECT COUNT(*)
FROM Table_LKP_AlertMastInfo ami
WHERE ami.DistrictID = ri.DistrictID
AND ami.FieldName ='Gender'
AND ami.AlertOptionValue = ri.Gender) AS [count]
FROM Table_RegistrationInfo ri) subq
WHERE subq.[count] > 0;
Привет! Пожалуйста, не передавайте статическое значение ami.FieldName = 'AreYouMarried' и ami.FieldName = 'Gender' . У меня есть 100 новых полей для управления, они должны быть динамическими. @Стив Чемберс
Я предполагаю, что со 100 полями у вас есть набор предупреждений, которые представляют собой комбинацию значений. Кроме того, я предполагаю, что вы можете постоянно иметь список выбора в правильном порядке. Так
select candidateid,
AreyouMarried || '|' || Gender all_responses_in_one_string
from ....
возможно. Итак, выше вернется
candidateid all_responses_in_one_string
can001 Yes|Female
can002 No|Female
Итак, теперь ваше предупреждение может быть регулярным выражением для объединенной строки. И ваше оповещение основано на том, сколько вы совпали.
Я не уверен, что это можно полностью сделать с помощью SQL. Если вы используете какую-либо внутреннюю технологию, такую как ADO.NET, вы можете сохранить результаты в таблицах данных. Прокрутите имена столбцов и выполните сравнение.
Можно использовать динамический SQL, чтобы таблица Table_LKP_AlertMastInfo выглядела как Table_RegistrationInfo. Этот сценарий можно использовать в хранимой процедуре, а результаты можно получить в таблице данных.
DECLARE @SQL NVARCHAR(MAX)
DECLARE @PivotFieldNameList nvarchar(MAX)
SET @SQL = ''
SET @PivotFieldNameList = ''
SELECT @PivotFieldNameList = @PivotFieldNameList + FieldName + ', '
FROM (SELECT DISTINCT FieldName FROM Table_LKP_AlertMastInfo) S
SET @PivotFieldNameList = SUBSTRING(@PivotFieldNameList, 1, LEN(@PivotFieldNameList) - 1)
--SELECT @PivotFieldNameList
SET @SQL = ' SELECT DistrictId, ' + @PivotFieldNameList + ' FROM
Table_LKP_AlertMastInfo
PIVOT
( MAX(AlertOptionValue)
FOR FieldName IN (' + @PivotFieldNameList + '
) ) AS p '
PRINT @SQL
EXEC(@SQL)
Выше результаты запроса, как показано ниже
DistrictId AreYouMarried Gender
71 Yes Female
72 Yes NULL
Если вы получаете результаты из Table_RegistrationInfo в другую таблицу данных, то обе можно использовать для сравнения.
Это невозможно без динамического SQL, если ваши данные смоделированы так, как есть, то есть пары ключ-значение в Table_LKP_AlertMastInfo
и столбцы в Table_RegistrationInfo
. Так что с этим из нашего пути, давайте сделаем это. Полный код хранимой процедуры, предоставляющий точные результаты, которые вам нужны, находится в конце, я последую с объяснением того, что она делает.
Поскольку оповещения указываются в виде пар ключ-значение (имя поля — значение поля), нам сначала нужно получить данные-кандидаты в том же формате. UNPIVOT
может исправить это сразу, если мы сможем получить список полей. Если бы у нас были только два поля, которые вы упомянули в вопросе, это было бы довольно просто, например:
SELECT CandidateId, DistrictID
, FieldName
, FieldValue
FROM Table_RegistrationInfo t
UNPIVOT (FieldValue FOR FieldName IN (AreYouMarried, Gender)) upvt
Конечно, это не так, поэтому нам нужно будет динамически выбирать список интересующих нас полей и предоставлять его. Поскольку вы используете версию 2008 R2, STRING_AGG пока недоступен, поэтому мы добавим используйте трюк XML, чтобы объединить все поля в одну строку и предоставим его в запросе выше.
DECLARE @sql NVARCHAR(MAX)
SELECT @sql = CONCAT('SELECT CandidateId, DistrictID
, FieldName
, FieldValue
FROM Table_RegistrationInfo t
UNPIVOT (FieldValue FOR FieldName IN (',
STUFF((
SELECT DISTINCT ',' + ami.FieldName
FROM Table_LKP_AlertMastInfo ami
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, ''), ')) upvt')
PRINT @sql
Это дает почти точный результат, как и запрос, который я написал. Далее нам нужно где-то хранить эти данные. Временные таблицы в помощь. Давайте создадим его и вставим в него, используя этот динамический SQL.
CREATE TABLE #candidateFields
(
CandidateID VARCHAR(50),
DistrictID INT,
FieldName NVARCHAR(200),
FieldValue NVARCHAR(1000)
);
INSERT INTO #candidateFields
EXEC sp_executesql @sql
-- (8 rows affected)
-- We could index this for good measure
CREATE UNIQUE CLUSTERED INDEX uxc#candidateFields on #candidateFields
(
CandidateId, DistrictId, FieldName, FieldValue
);
Отлично, теперь у нас есть оба набора данных — оповещения и данные-кандидаты — в одном и том же формате. Это вопрос присоединения, чтобы найти совпадения между обоими:
SELECT cf.CandidateID, COUNT(*) AS matches
FROM #candidateFields cf
INNER
JOIN Table_LKP_AlertMastInfo alerts
ON alerts.DistrictID = cf.DistrictID
AND alerts.FieldName = cf.FieldName
AND alerts.AlertOptionValue = cf.FieldValue
GROUP BY cf.CandidateID
Обеспечивает желаемый результат для демонстрационных данных:
CandidateID matches -------------------------------------------------- ----------- Can001 2 Can002 1 Can003 1 (3 rows affected)
Итак, теперь мы можем соединить все это вместе, чтобы сформировать многоразовую хранимую процедуру:
CREATE PROCEDURE dbo.findMatches
AS
BEGIN
SET NOCOUNT ON;
DECLARE @sql NVARCHAR(MAX)
SELECT @sql = CONCAT('SELECT CandidateId, DistrictID
, FieldName
, FieldValue
FROM Table_RegistrationInfo t
UNPIVOT (FieldValue FOR FieldName IN (',
STUFF((
SELECT DISTINCT ',' + ami.FieldName
FROM Table_LKP_AlertMastInfo ami
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, ''), ')) upvt')
CREATE TABLE #candidateFields
(
CandidateID VARCHAR(50),
DistrictID INT,
FieldName NVARCHAR(200),
FieldValue NVARCHAR(1000)
);
INSERT INTO #candidateFields
EXEC sp_executesql @sql
CREATE UNIQUE CLUSTERED INDEX uxc#candidateFields on #candidateFields
(
CandidateId, DistrictId, FieldName
);
SELECT cf.CandidateID, COUNT(*) AS matches
FROM #candidateFields cf
JOIN Table_LKP_AlertMastInfo alerts
ON alerts.DistrictID = cf.DistrictID
AND alerts.FieldName = cf.FieldName
AND alerts.AlertOptionValue = cf.FieldValue
GROUP BY cf.CandidateID
END;
Выполнить с
EXEC dbo.findMatches
Вам, конечно, нужно настроить типы и, возможно, добавить сюда кучу других вещей, таких как обработка ошибок, но это должно помочь вам начать с правильного пути. Вам понадобится покрывающий индекс для этой таблицы предупреждений, и он должен быть довольно быстрым даже с большим количеством записей.
Не проверено, но это должно помочь:
SELECT CandidateId,
( CASE
WHEN AreYouMarried = "Yes" AND Gender = 'Female' THEN 2
WHEN Gender = 'Female' THEN 1
WHEN AreYouMarried = "Yes" THEN 1
ELSE 0 END
) as CandidateValue
FROM
(SELECT * FROM Table_LKP_AlertMastInfo) as Alert
LEFT JOIN
(SELECT * FROM Table_RegistrationInfo) as Registration
ON (Alert.DistrictID = Registration.DistrictID);
Это должно дать вам список с идентификатором кандидата, соответствующим количеству условий.
Возьмем пример Can001 — какие поля идентификаторов мы можем использовать для объединения таблиц? потому что Districtid не уникален для кандидатов.