我在为自己解释问题时都遇到了问题,因此我创建了一张图片,因为它更易于理解。
表结构:
如果红色字段为空而绿色字段为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');
其背后的想法是,每个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] 删除。
我来说两句