从MYSQL Pivot表中筛选记录

I have this query, http://sqlfiddle.com/#!9/1a579/3

我有这个查询,http://sqlfiddle.com/#!9/1a579 / 3

I have built this using dynamic query. Need to know how can i filter records from it. i need to consider the results of this query as a table. lets say CustomUser table. so i need to query against this table like explained below.

我使用动态查询构建了这个。需要知道如何从中过滤记录。我需要将此查询的结果视为一个表。让我们说CustomUser表。所以我需要查询此表,如下所述。

SET @Colvalues = NULL;
SET @sql = NULL;

SELECT
  GROUP_CONCAT(DISTINCT CONCAT('MAX(IF(f.fieldName = ''',
      f.fieldName, ''', COALESCE(v.fieldValue, f.defaultValue) , NULL)) AS ', '''', f.fieldName , '''')
  ) INTO @Colvalues
FROM customField AS f
INNER JOIN Customvalue AS v ON f.Id = v.customFieldId;


SET @sql = CONCAT('SELECT 
    u.*, v.relatedId, v.CreatedAt, ', @Colvalues , '
FROM customField AS f
INNER JOIN Customvalue AS v ON f.Id = v.customFieldId RIGHT JOIN User u on u.id = v.relatedId
GROUP BY   v.relatedId, v.CreatedAt;');

PREPARE stmt 
FROM @sql;

EXECUTE stmt;

This results has the following data,

这个结果有以下数据,

Now Considering this as CustomUser table, i need to run a query like

现在将其视为CustomUser表,我需要运行一个类似的查询

select * from CustomUser where HOMEEMAIL like '%ab%' and JOINDATE like '%2010%' and SCHOOLING? like '%1%'

select * from CustomUser where HOMEEMAIL like '%ab%' and JOINDATE like '%2010%' and SCHOOLING? like '%1%' and LANDPHONENO? like '%0112%'

1 个解决方案

#1

You can wrap it with subquery and do any filtering you need:

您可以使用子查询包装它并执行您需要的任何过滤:

SET @Colvalues = NULL;
SET @sql = NULL;

SELECT
  GROUP_CONCAT(DISTINCT CONCAT('MAX(IF(f.fieldName = ''',
      f.fieldName, ''', COALESCE(v.fieldValue, f.defaultValue) , NULL)) AS ', '''', f.fieldName , '''')
  ) INTO @Colvalues
FROM customField AS f
INNER JOIN Customvalue AS v ON f.Id = v.customFieldId;


SET @sql = CONCAT('SELECT * FROM (SELECT 
    u.*, v.relatedId, v.CreatedAt, ', @Colvalues , '
FROM customField AS f
INNER JOIN Customvalue AS v ON f.Id = v.customFieldId RIGHT JOIN User u on u.id = v.relatedId
GROUP BY   v.relatedId, v.CreatedAt)
AS s WHERE s.Homeemail = 456;');   -- here

PREPARE stmt 
FROM @sql;

EXECUTE stmt;

SqlFiddleDemo

You can also extract condition to second variable and just concatenate:

您还可以将条件提取到第二个变量并且只是连接:

SET @conditions = NULL;
SET @conditions = CASE WHEN @conditions IS NULL THEN '1 = 1' ELSE @conditions END

SET @sql = CONCAT('SELECT * FROM (SELECT 
        u.*, v.relatedId, v.CreatedAt, ', @Colvalues , '
    FROM customField AS f
    INNER JOIN Customvalue AS v ON f.Id = v.customFieldId RIGHT JOIN User u on u.id = v.relatedId
    GROUP BY   v.relatedId, v.CreatedAt)
    AS s WHERE ',  @conditions);   -- here

#1