SQL-запрос для получения количества из двух таблиц на основе значений и имен полей

Я хочу подсчитать оповещения кандидатов по округам.

Ниже приведена таблица поиска предупреждений по районам.

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 

Возьмем пример Can001 — какие поля идентификаторов мы можем использовать для объединения таблиц? потому что Districtid не уникален для кандидатов.

surpavan 30.03.2019 23:26
ReactJs | Supabase | Добавление данных в базу данных
ReactJs | Supabase | Добавление данных в базу данных
Это и есть ваш редактор таблиц в supabase.👇
Понимание Python и переход к SQL
Понимание Python и переход к SQL
Перед нами лабораторная работа по BloodOath:
5
1
393
6
Перейти к ответу Данный вопрос помечен как решенный

Ответы 6

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

 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 новых полей для менеджера. @Пратик Бхавсар

mohd mazhar khan 27.03.2019 14:39

Я думаю, курсор поможет для динамики?

mohd mazhar khan 28.03.2019 08:24

@mohdmazharkhan Не уверен, я плохо знаком с курсорами

Pratik Bhavsar 28.03.2019 14:23

Вот один простой способ сделать это:

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;

См. Демонстрация скрипта SQL.

Привет! Пожалуйста, не передавайте статическое значение ami.FieldName = 'AreYouMarried' и ami.FieldName = 'Gender' . У меня есть 100 новых полей для управления, они должны быть динамическими. @Стив Чемберс

mohd mazhar khan 28.03.2019 15:44

Я предполагаю, что со 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);

Это должно дать вам список с идентификатором кандидата, соответствующим количеству условий.

Другие вопросы по теме