如果条件为真，则选择数据

Williamsson 发表于 Dev

威廉姆森

我在为自己解释问题时都遇到了问题，因此我创建了一张图片，因为它更易于理解。

表结构：在此处输入图片说明

如果红色字段为空而绿色字段为true，我想从蓝色字段中选择数据。我该怎么做呢？我已经为此花了很长时间了。

由于它是从wordpress contactform7到数据库插件的，因此无法更改表结构。

我将非常感谢您的帮助:)

只要选择一封电子邮件就可以，

SELECT field_value FROM wp_cf7dbplugin_submits WHERE field_name = 'your-email'

现在，我只想在field_value =''WHERE field_name ='has-wp-user'时选择电子邮件。

所以我想要的就是这样；

SELECT field_value FROM wp_cf7dbplugin_submits WHERE field_name = 'your-email' IF has-wp-user = 'false'

伊什

这样有可能吗？

表结构：

CREATE TABLE IF NOT EXISTS `wp_cf7dbplugin_submits` (
  `submit_time` decimal(16,4) NOT NULL,
  `form_name` varchar(127) CHARACTER SET utf8 DEFAULT NULL,
  `field_name` varchar(127) CHARACTER SET utf8 DEFAULT NULL,
  `field_value` longtext CHARACTER SET utf8,
  `field_order` int(11) DEFAULT NULL,
  `file` longblob,
  KEY `submit_time_idx` (`submit_time`),
  KEY `form_name_idx` (`form_name`),
  KEY `field_name_idx` (`field_name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumpning av Data i tabell `wp_cf7dbplugin_submits`
--

INSERT INTO `wp_cf7dbplugin_submits` (`submit_time`, `form_name`, `field_name`, `field_value`, `field_order`, `file`) VALUES
('1387497601.8300', 'Bli medlem', 'has-wp-user', '', 0, NULL),
('1387497601.8300', 'Bli medlem', 'paid-membership-fee', 'true', 1, NULL),
('1387497601.8300', 'Bli medlem', 'your-name', 'Firstname Lastname ', 2, NULL),
('1387497601.8300', 'Bli medlem', 'your-email', '[email protected]', 3, NULL),
('1387497601.8300', 'Bli medlem', 'your-adress', 'theroad 12', 4, NULL),
('1387497601.8300', 'Bli medlem', 'your-zip', '76045', 5, NULL),
('1387497601.8300', 'Bli medlem', 'your-postal-area', 'postalarea', 6, NULL),
('1387497601.8300', 'Bli medlem', 'your-phonenumber', '0123456789', 7, NULL),
('1387497602.8300', 'Bli medlem', 'has-wp-user', '', 0, NULL),
('1387497602.8300', 'Bli medlem', 'paid-membership-fee', 'true', 1, NULL),
('1387497602.8300', 'Bli medlem', 'your-name', 'Firstname lastname', 2, NULL),
('1387497602.8300', 'Bli medlem', 'your-email', '[email protected]', 3, NULL),
('1387497602.8300', 'Bli medlem', 'your-adress', 'singövägen 156', 4, NULL),
('1387497602.8300', 'Bli medlem', 'your-zip', '76045', 5, NULL),
('1387497602.8300', 'Bli medlem', 'your-postal-area', 'grisslehamn', 6, NULL),
('1387497602.8300', 'Bli medlem', 'your-phonenumber', '0123456789', 7, NULL),
('1387584003.8300', 'Bli medlem', 'has-wp-user', '', 0, NULL),
('1387584003.8300', 'Bli medlem', 'paid-membership-fee', 'true', 1, NULL),
('1387584003.8300', 'Bli medlem', 'your-name', 'Firstname Lastname', 2, NULL),
('1387584003.8300', 'Bli medlem', 'your-email', '[email protected]', 3, NULL),
('1387584003.8300', 'Bli medlem', 'your-adress', 'gryta hagväg 6', 4, NULL),
('1387584003.8300', 'Bli medlem', 'your-zip', '76175', 5, NULL),
('1387584003.8300', 'Bli medlem', 'your-postal-area', 'norrtälje', 6, NULL),
('1387584003.8300', 'Bli medlem', 'your-phonenumber', '0123456789', 7, NULL),
('1387584004.8300', 'Bli medlem', 'has-wp-user', 'true', 0, NULL),
('1387584004.8300', 'Bli medlem', 'paid-membership-fee', '', 1, NULL),
('1387584004.8300', 'Bli medlem', 'your-name', 'Firstname Lastname', 2, NULL),
('1387584004.8300', 'Bli medlem', 'your-email', '[email protected]', 3, NULL),
('1387584004.8300', 'Bli medlem', 'your-adress', 'bergstigen 7', 4, NULL),
('1387584004.8300', 'Bli medlem', 'your-zip', '76192', 5, NULL),
('1387584004.8300', 'Bli medlem', 'your-postal-area', 'norrtälje', 6, NULL),
('1387584004.8300', 'Bli medlem', 'your-phonenumber', '0123456789', 7, NULL),
('1387584005.8300', 'Bli medlem', 'has-wp-user', '', 0, NULL),
('1387584005.8300', 'Bli medlem', 'paid-membership-fee', 'true', 1, NULL),
('1387584005.8300', 'Bli medlem', 'your-name', 'Firstname Lastname', 2, NULL),
('1387584005.8300', 'Bli medlem', 'your-email', '[email protected]', 3, NULL),
('1387584005.8300', 'Bli medlem', 'your-adress', 'robertsväg10 ', 4, NULL),
('1387584005.8300', 'Bli medlem', 'your-zip', '76045', 5, NULL),
('1387584005.8300', 'Bli medlem', 'your-postal-area', 'grisslehamn', 6, NULL),
('1387584005.8300', 'Bli medlem', 'your-phonenumber', '0123456789', 7, NULL);

花哨的裤子

select
*
from
my_table m inner join (
  select
  m.*
  from
  my_table m
  group by form_name, submit_time
  having sum(
    (field_name = 'has-wp-user' and field_value = '') 
    or 
    (field_name = 'paid-membership-fee' and field_value = 'true')
  ) = 2
) sq on m.form_name = sq.form_name and m.submit_time = sq.submit_time
where m.field_name in ('your-name', 'your-email');

看到它实时运行sqlfiddle（已更新为新数据）

其背后的想法是，每个field_name都属于一个组。我在这里假设，这是form_name和Submit_time列的组合。有了这个子查询

  select
  m.*
  from
  my_table m
  group by form_name, submit_time
  having sum(
    (field_name = 'has-wp-user' and field_value is null) 
    or 
    (field_name = 'paid-membership-fee' and field_value = 'true')
  ) = 2

您只需选择那些具有field_name行has-wp-user和pay-membership-fee且值分别为null和true的form_names。现在，您可以再次连接到原始表，该表仅过滤出允许的form_names并方便地选择所需的行。

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。