基于值和字段名从两个表中获取计数的SQL查询

Question

基于值和字段名从两个表中获取计数的SQL查询

sqlsql-serversql-server-2008-r2

5

我想根据地区计算候选人的警报数量。

以下是按地区分类的警报查询表

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

- Mr doubt

以Can001为例，我们可以使用哪些id字段在表之间进行连接？因为对于候选者而言，districtid不是唯一的。 - surpavan

6个回答

2

我成功地在不使用动态查询的情况下获得了预期的结果。不确定这是否是您要查找的内容：最初的回答。

 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

结果：

最初的回答：

- Pratik Bhavsar

请不要传递静态值 a.FieldName = 'AreYouMarried' 和 a.FieldName = 'Gender'。我有100个新字段需要管理。@Pratik Bhavsar - Mr doubt

1

我认为光标会对动态有所帮助？ - Mr doubt

@mohdmazharkhan 不确定，我对光标不是很熟悉。 - Pratik Bhavsar

2

我假设你有100个字段，其中包含值的组合。此外，我假设您可以始终按正确顺序选择列表。因此，"Original Answer"是最初的回答。

select candidateid,
       AreyouMarried || '|' || Gender all_responses_in_one_string
from ....

"最初的回答" 可能是可以的。因此，以上内容将返回。

candidateid  all_responses_in_one_string
can001       Yes|Female
can002       No|Female

现在你的警报可以是拼接字符串的正则表达式。而且你的警报基于你匹配了多少内容。

- Saad Ahmad

1

这是一种简单的做法：

在此处插入代码：

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 Fiddle演示。

- Steve Chambers

嗨，请不要传递静态值ami.FieldName ='AreYouMarried'和ami.FieldName ='Gender'。我有100个新字段需要管理，它应该是动态的。@Steve Chambers - Mr doubt

1

我不确定是否可以完全使用SQL完成这个任务。如果你使用一些后端技术，比如ADO.NET，那么你可以将结果存储在Datatables中。遍历列名并进行比较。

动态SQL可以用来使Table_LKP_AlertMastInfo看起来像Table_RegistrationInfo。这个脚本可以用在存储过程中，并且结果可以在Datatable中检索到。

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获取结果到另一个Datatable中，那么两者都可以用于比较。

- Nagashree Hs

0

没有测试过，但这应该可以解决问题：

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);

这应该会给你一个符合条件计数的 candidateId 列表

- user10223713

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- MarcinJ · Accepted Answer

如果数据被建模为键值对（Table_LKP_AlertMastInfo中的键值对和Table_RegistrationInfo中的列），那么如果没有动态SQL，这将是不可能的。因此，既然我们已经处理完了这个问题，那么让我们开始吧。提供确切结果的存储过程的完整代码将在文末附上，接下来我会解释它的作用。

因为警报是指定为键值对（字段名-字段值），所以我们首先需要以相同的格式获取候选数据。如果我们可以得到字段列表，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                                        匹配次数
-------------------------------------------------- -----------
Can001                                             2
Can002                                             1
Can003                                             1
(3 行受影响)

现在我们可以将所有内容拼合在一起，形成可重用的存储过程：

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

当然，你需要调整类型，并在此处添加许多其他内容，例如错误处理，但这应该可以让你朝着正确的方向开始。您将需要在警报表上设置覆盖索引，即使有很多记录，它也应该非常快。