mysql - 具体mysql复杂查询连接

Question

关闭。此题需要 details or clarity 。目前不接受答案。

---

想要改进这个问题吗？通过 editing this post 添加详细信息并澄清问题.

已关闭 9 年前。

Improve this question

我有一个 WordPress 网站，我必须通过外部脚本将信息提取到外部应用程序中。

我只是在一次 mysql 调用上遇到了一点问题

我在 wp_posts 表中列出了折扣，然后在 wp_postmeta 表中列出了开始日期和结束日期。

但是，我可以在一个 mysql 查询中选择所有有效的折扣(即开始日期和结束日期之间)

谁能告诉我该怎么做。

下面我有一个包含 4 个示例的转储应返回 2 (104745,104744)1 是旧折扣 (104743)1 尚未上线 (104666)

非常感谢

DROP TABLE IF EXISTS `wp_postmeta`;
CREATE TABLE `wp_postmeta` (
  `meta_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `post_id` bigint(20) unsigned NOT NULL DEFAULT '0',
  `meta_key` varchar(255) DEFAULT NULL,
  `meta_value` longtext,
  PRIMARY KEY (`meta_id`),
  KEY `post_id` (`post_id`),
  KEY `meta_key` (`meta_key`)
) ENGINE=InnoDB AUTO_INCREMENT=459770 DEFAULT CHARSET=utf8;


DROP TABLE IF EXISTS `wp_posts`;
CREATE TABLE `wp_posts` (
  `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `post_author` bigint(20) unsigned NOT NULL DEFAULT '0',
  `post_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_date_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_content` longtext NOT NULL,
  `post_title` text NOT NULL,
  `post_excerpt` text NOT NULL,
  `post_status` varchar(20) NOT NULL DEFAULT 'publish',
  `comment_status` varchar(20) NOT NULL DEFAULT 'open',
  `ping_status` varchar(20) NOT NULL DEFAULT 'open',
  `post_password` varchar(20) NOT NULL DEFAULT '',
  `post_name` varchar(200) NOT NULL DEFAULT '',
  `to_ping` text NOT NULL,
  `pinged` text NOT NULL,
  `post_modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_modified_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_content_filtered` longtext NOT NULL,
  `post_parent` bigint(20) unsigned NOT NULL DEFAULT '0',
  `guid` varchar(255) NOT NULL DEFAULT '',
  `menu_order` int(11) NOT NULL DEFAULT '0',
  `post_type` varchar(20) NOT NULL DEFAULT 'post',
  `post_mime_type` varchar(100) NOT NULL DEFAULT '',
  `comment_count` bigint(20) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  KEY `post_name` (`post_name`),
  KEY `type_status_date` (`post_type`,`post_status`,`post_date`,`ID`),
  KEY `post_parent` (`post_parent`),
  KEY `post_author` (`post_author`)
) ENGINE=InnoDB AUTO_INCREMENT=104855 DEFAULT CHARSET=utf8;

INSERT INTO `wp_posts` (`ID`, `post_author`, `post_date`, `post_date_gmt`, `post_content`, `post_title`, `post_excerpt`, `post_status`, `comment_status`, `ping_status`, `post_password`, `post_name`, `to_ping`, `pinged`, `post_modified`, `post_modified_gmt`, `post_content_filtered`, `post_parent`, `guid`, `menu_order`, `post_type`, `post_mime_type`, `comment_count`) VALUES
(104745,    0,  '2014-02-15 00:00:00',  '2014-02-15 00:00:00',  '<div class=\"code-details\">\r\n<p class=\"code-description\"><span class=\"merchant-name\">Interflora</span> &pound;4 Off Orders over &pound;40 at Interflora T&C: Minimum product value of &pound;40 applies  offer excludes overseas, giftcard and Interflora Gold purchases.</p>\r\n</div>',   '&pound;4 Off Orders over &pound;40 at Interflora T&C: Minimum product value of &pound;40 applies  offer excludes overseas, giftcard and Interflora Gold purchases.',   '', 'publish',  'closed',   'closed',   '', '4-off-orders-over-40-at-interflora-t40-applies-%c2%96-offer-excludes-overseas-giftcard-and-interflora-gold-purchases', '', '', '2014-02-17 16:01:36',  '2014-02-17 16:01:36',  '', 0,  'http://www.site.com/discount-codes/4-off-orders-over-40-at-interflora-t40-applies-%c2%96-offer-excludes-overseas-giftcard-and-interflora-gold-purchases/', 0,  'discount_codes',   '', 0),
(104744,    0,  '2014-02-17 00:00:00',  '2014-02-17 00:00:00',  '<div class=\"code-details\">\r\n<p class=\"code-description\"><span class=\"merchant-name\">Interflora</span> 10% Off All Hampers over &pound;25 at Interflora T&C: Minimum product value of &pound;24.99 applies  offer excludes overseas, giftcard and Interflora Gold purchases.</p>\r\n</div>',    '10% Off All Hampers over &pound;25 at Interflora T&C: Minimum product value of &pound;24.99 applies  offer excludes overseas, giftcard and Interflora Gold purchases.',    '', 'publish',  'closed',   'closed',   '', '10-off-all-hampers-over-25-at-interflora-t24-99-applies-%c2%96-offer-excludes-overseas-giftcard-and-interflora-gold-purchases',    '', '', '2014-02-17 16:01:34',  '2014-02-17 16:01:34',  '', 0,  'http://www.site.com/discount-codes/10-off-all-hampers-over-25-at-interflora-t24-99-applies-%c2%96-offer-excludes-overseas-giftcard-and-interflora-gold-purchases/',    0,  'discount_codes',   '', 0),
(104743,    0,  '2014-02-15 00:00:00',  '2014-02-15 00:00:00',  '<div class=\"code-details\">\r\n<p class=\"code-description\"><span class=\"merchant-name\">Interflora</span> 10% Off the Spring Flowers Collection at Interflora (Min Spend &pound;30) T&C: Minimum product value of &pound;30.00 applies  offer excludes overseas, giftcard and Interflora Gold purchases.</p>\r\n</div>',   '10% Off the Spring Flowers Collection at Interflora (Min Spend &pound;30) T&C: Minimum product value of &pound;30.00 applies  offer excludes overseas, giftcard and Interflora Gold purchases.',   '', 'publish',  'closed',   'closed',   '', '10-off-the-spring-flowers-collection-at-interflora-min-spend-30-t30-00-applies-%c2%96-offer-excludes-overseas-giftcard-and-interflora-gold-purchases', '', '', '2014-02-17 16:01:33',  '2014-02-17 16:01:33',  '', 0,  'http://www.site.com/discount-codes/10-off-the-spring-flowers-collection-at-interflora-min-spend-30-t30-00-applies-%c2%96-offer-excludes-overseas-giftcard-and-interflora-gold-purchases/', 0,  'discount_codes',   '', 0),
(104666,    0,  '2014-02-17 00:00:00',  '2014-02-17 00:00:00',  '<div class=\"code-details\">\r\n<p class=\"code-description\"><span class=\"merchant-name\">Marks and Spencer</span> 20% off Outlet (does not include the BOGOHP offers)</p>\r\n</div>',   '20% off Outlet (does not include the BOGOHP offers)',  '', 'publish',  'closed',   'closed',   '', '20-off-outlet-does-not-include-the-bogohp-offers', '', '', '2014-02-17 16:01:50',  '2014-02-17 16:01:50',  '', 0,  'http://www.site.com/discount-codes/20-off-outlet-does-not-include-the-bogohp-offers/', 0,  'discount_codes',   




INSERT INTO `wp_postmeta` (`meta_id`, `post_id`, `meta_key`, `meta_value`) VALUES
(459207,    104743, 'discount_code_merchant_name',  'Interflora'),
(459208,    104743, 'discount_code',    'SPINGFLO'),
(459209,    104743, 'discount_code_url',    'd.php?v=1969&amp;t=141617&amp;p=http://www.interflora.co.uk/category/spring-flowers'),
(459210,    104743, 'discount_code_start_date', '2014-02-05 00:00:00'),
(459211,    104743, 'discount_code_end_date',   '2014-02-08 23:59:59'),
(459212,    104743, '_wp_page_template',    'default’),


(459213,    104744, 'discount_code_merchant_name',  'Interflora'),
(459214,    104744, 'discount_code',    'HAMPER'),
(459215,    104744, 'discount_code_url',    'd.php?v=1969&amp;t=141617&amp;p=http://www.interflora.co.uk/category/hampers-gifts/'),
(459216,    104744, 'discount_code_start_date', '2014-02-17 00:00:00'),
(459217,    104744, 'discount_code_end_date',   '2014-02-23 23:59:59'),
(459218,    104744, '_wp_page_template',    'default'),
(459225,    104745, 'discount_code_merchant_name',  'Interflora'),
(459226,    104745, 'discount_code',    'SPRING4IT'),
(459227,    104745, 'discount_code_url',    'd.php?v=1969&amp;t=141617&amp;p=http://www.interflora.co.uk/'),
(459228,    104745, 'discount_code_start_date', '2014-02-15 00:00:00'),
(459229,    104745, 'discount_code_end_date',   '2014-02-28 23:59:59'),
(459230,    104745, '_wp_page_template',    'default’),


(459285,    104666, 'discount_code_merchant_name',  'Marks and Spencer'),
(459286,    104666, 'discount_code',    'N/A'),
(459287,    104666, 'discount_code_url',    'd.php?v=1402&amp;t=141617&amp;p=http://outlet.marksandspencer.com/'),
(459288,    104666, 'discount_code_start_date', '2014-03-17 00:00:00'),
(459289,    104666, 'discount_code_end_date',   '2014-03-18 23:59:59'),
(459290,    104666, '_wp_page_template',    'default’);

Answer 1

您让我们进行了一些逆向工程!

我认为，在pseudosql中你需要的是

 SELECT id
   FROM discount_codes
  WHERE discount_code_start_date < NOW()
    AND discount_code_end_date > NOW()

这将回滚已开始但未结束的折扣代码的帖子 ID 列表。

因此，诀窍是以某种方式强制 WordPress 架构为您提供此结果。像往常一样，弄清楚这一点是一件很有趣的事情。

这就是你要做的。您需要获取作为有效折扣代码的 wp_posts 条目 id 值。这很简单。

SELECT id
  FROM wp_posts
 WHERE post_type = 'discount_codes'
   AND post_status = 'publish

接下来您需要获取有用的 post_meta 项目。这些需要子查询才能正确完成。

SELECT post_id AS id, 
       CAST(meta_value AS DATETIME) AS discount_code_start_date
  FROM post_meta
 WHERE meta_key = 'discount_code_start_date'

同样，您需要此作为结束日期。

SELECT post_id AS id, 
       CAST(meta_value AS DATETIME) AS discount_code_end_date
  FROM post_meta
 WHERE meta_key = 'discount_code_end_date'

然后您需要将所有这些内容连接在一起并应用正确的 WHERE 子句。

SELECT p.id
  FROM wp_posts AS p
  JOIN (
       SELECT post_id AS id, 
              CAST(meta_value AS DATETIME) AS discount_code_start_date
         FROM post_meta
        WHERE meta_key = 'discount_code_start_date'
       ) AS s ON (p.id = s.id)
  JOIN (
      SELECT post_id AS id, 
             CAST(meta_value AS DATETIME) AS discount_code_end_date
        FROM post_meta
       WHERE meta_key = 'discount_code_end_date'
       ) AS e ON (p.id = e.id)
 WHERE p.post_type = 'discount_codes'
   AND p.post_status = 'publish'
   AND s.discount_code_start_date < NOW()
   AND e.discount_code_end_date > NOW()

正确使用 post_meta 表的秘诀在于使用适当的 meta_key 值提取行的子查询。