mysql - 从多值文本字段中选择值，选择条件位于同一字段中

Question

我有一个表，其中存储了 html 表单提交的内容。

表单的元素和值均位于文本格式的列中。

示例数据:

SELECT * FROM ftbe2_rsform_submission_values;
+-----------+--------+--------+--------------+------------+
| SubmValId | FormId | SubmId | FieldName    | FieldValue |
+-----------+--------+--------+--------------+------------+
|         1 |     32 |    460 | date         | 2017-04-17 |
|         2 |     32 |    460 | amount       | 62000      |
|         3 |     32 |    460 | billno       | BE-4587    |
|         4 |     32 |    460 | dispopurchno | 420        |
|         5 |     32 |    460 | price        | 28500      |
|         6 |     32 |    461 | date         |            |
|         7 |     32 |    461 | amount       |            |
|         8 |     32 |    461 | billno       |            |
|         9 |     32 |    461 | dispopurchno | 420        |
|        10 |     32 |    461 | price        | 32000      |
|        11 |     32 |    462 | date         | 2017-04-18 |
|        12 |     32 |    462 | amount       | 62000      |
|        13 |     32 |    462 | billno       |            |
|        14 |     32 |    462 | dispopurchno | 420        |
|        15 |     32 |    462 | price        | 28500      |
|        16 |     32 |    463 | date         | 1995       |
|        17 |     32 |    463 | amount       | 74500      |
|        18 |     32 |    463 | billno       | 42-FOO     |
|        19 |     32 |    463 | dispopurchno | 422        |
|        20 |     32 |    463 | price        | 12000      |
|        21 |     32 |    464 | date         | 2017-04-18 |
|        22 |     32 |    464 | amount       | 510000     |
|        23 |     32 |    464 | billno       |            |
|        24 |     32 |    464 | dispopurchno | 422        |
|        25 |     32 |    464 | price        | 22000      |
|        26 |     32 |    465 | date         |            |
|        27 |     32 |    465 | amount       |            |
|        28 |     32 |    465 | billno       |            |
|        29 |     32 |    465 | dispopurchno | 422        |
|        30 |     32 |    465 | price        | 12000      |
|        31 |     32 |    466 | date         | 2017-04-18 |
|        32 |     32 |    466 | amount       | 31000      |
|        33 |     32 |    466 | billno       |            |
|        34 |     32 |    466 | dispopurchno | 420        |
|        35 |     32 |    466 | price        | 56580      |
+-----------+--------+--------+--------------+------------+

我想根据同一提交中的行值来选择价格值。所需的列是:

FormId, FieldName, FieldValue

所需的行:

FormId | FieldName    | FieldValue
-------|--------------|-----------
32     | date         | not empty
32     | amount       | not empty
32     | billno       | empty
32     | dispoburchno | '420' (for example, given by php script)
32     | price        | 

期望返回:

   +------------+
   | FieldValue |
   +------------+
   | 28500      | (SubmId 462)
   | 56580      | (SubmId 466)
   +------------+

在php中使用查询:

$total_price = 0;
$purchno = (from another query)
if ($form->FormId === 32) {
    foreach ($form->submissions as $submission) {
        (execute sql:
            get FieldValue of FieldName[price] from ftbe2_rsform_submission_values where
            SubmissionId = $submisson->SubmissionId
            FieldValue of FieldName[dispopurchno] = $purchno
            FieldValue of FieldName[date] = not empty
            FieldValue of FieldName[amount] = not empty
            FieldValue of FieldName[billno] = empty)
    $totalprice += price;
    }
}

我尝试使用AND EXISTS:请参阅SQL Fiddle对于INNER JOIN:请参阅 SQL Fiddle

我想念什么？西蒙

Answer 1

FIDDLE

SELECT fieldValue
FROM ftbe2_rsform_submission_values A
WHERE exists (SELECT SubMID, count(Distinct FieldName)
              FROM ftbe2_rsform_submission_values B
              WHERE ((fieldName = 'date' and fieldValue <> '')
                 OR (fieldName = 'amount' and fieldValue <> '')
                 OR (fieldName = 'BillNo' and fieldValue = '')
                 OR (fieldName = 'dispopurchno' and fieldValue ='420'))
                AND B.FormID = A.FormID
                AND B.SubmID = A.SubmID
        GROUP BY SUBMID
        having count(distinct fieldName) = 4)
  AND FieldName = 'Price'

子查询确保我们提交的内容包含所有必要的配对条件，然后我们按 submid 进行分组，确保所有 4 个条件都存在。然后，我们使用存在与外部查询关联，以获取这些提交的价格条目。

我希望你有一个关于 formID、SubmID、fieldname、fieldValue 的索引!

当您添加额外的“配对值映射”限制时，您只需增加 4，或者如果您减少配对映射，您只需减少 4；因为看起来您总是知道存在多少个配对值映射。

我最大的担忧是“值”字段上可能存在 '' 与 NULL 值，但如果我们总是知道它将是空集 '' 与 Null，那么上面的方法应该可以工作。