English 中文(简体)
MMSQL 查询比较 2 个表格值
原标题:MYSQL query compare 2 table values

目前我身处深水之中, 被强调为截止日期的原因 和对缺乏知识的沮丧。

所以,我会要求 绝望的帮助。

但既然我目前和WP的WW病情 一起工作,

I hade posts with post type of "chatt" where each post has its meta that is cruicial. One of the Metas is a timestamp and another is a id for chats.

我需要用给定的 ID (来自元值) 和时间戳值比给定值高的地方获得所有职位 。

目前为止,我得到了这个,但我很难 使这个工作:S

   SELECT wp_postmeta.*, 
          wp_posts.* 
     FROM wp_postmeta
LEFT JOIN wp_posts 
       ON wp_posts.ID = wp_postmeta.post_id 
          AND wp_posts.post_type =  chatt 
LEFT JOIN wp_postmeta t2 
       ON t2.meta_key =  aw_chat_id  
          AND t2.meta_value =  test-chat-av-chattuser 
    WHERE wp_postmeta.post_id = wp_posts.ID 
      AND t2.meta_key =  aw_chat_timestamp  
      AND t2.meta_value > 2

有人知道怎么解决吗?

(基本上,在后型=聊天和元值=测试聊天和聊天用户和元值 & gt;2)的所有职位中,获得所有后型=聊天和元值=测试聊天和聊天用户和元值和gt;2)

EDIT:结构

在Wp_posmeta中,

INSERT INTO `wp_postmeta` (`meta_id`, `post_id`, `meta_key`, `meta_value`)
VALUES
    (2212, 1458,  aw_chat_id ,  test-chat-av-chattuser ),
    (2213, 1458,  aw_chat_timestamp ,  6 ),
    (2209, 1457,  aw_chat_id ,  test-chatt ),
    (2210, 1457,  aw_chat_timestamp ,  5 ),
    (2207, 1456,  aw_chat_id ,  test-chatt ),
    (2208, 1456,  aw_chat_timestamp ,  4 ),
    (2205, 1455,  aw_chat_id ,  test-chatt ),
    (2206, 1455,  aw_chat_timestamp ,  3 ),
    (2202, 1454,  aw_chat_id ,  test-chatt ),
    (2203, 1454,  aw_chat_timestamp ,  2 ),
    (2204, 1454,  aw_chat_answer ,  adfgsdhfgsdfhsfdh ),
    (2199, 1453,  aw_chat_id ,  test-chatt ),
    (2200, 1453,  aw_chat_timestamp ,  1 ),
    (2201, 1453,  aw_chat_answer ,  asdfasdasdf );



meta_id post_id meta_key    meta_value
2212    1458    aw_chat_id  test-chat-av-chattuser
2213    1458    aw_chat_timestamp   6
2209    1457    aw_chat_id  test-chatt
2210    1457    aw_chat_timestamp   5
2207    1456    aw_chat_id  test-chatt
2208    1456    aw_chat_timestamp   4
2205    1455    aw_chat_id  test-chatt
2206    1455    aw_chat_timestamp   3
2202    1454    aw_chat_id  test-chatt
2203    1454    aw_chat_timestamp   2
2204    1454    aw_chat_answer  adfgsdhfgsdfhsfdh
2199    1453    aw_chat_id  test-chatt
2200    1453    aw_chat_timestamp   1
2201    1453    aw_chat_answer  asdfasdasdf

这就是 wp_pos 看起来像:

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
    (1458, 0,  2012-05-22 11:15:59 ,  0000-00-00 00:00:00 ,  Chatuser chat !? ,  qwedq ,   ,  draft ,  open ,  closed ,   ,   ,   ,   ,  2012-05-22 11:15:59 ,  0000-00-00 00:00:00 ,   , 0,  http://localhost/?post_type=chatt&p=1458 , 0,  chatt ,   , 0),
    (1457, 18,  2012-05-22 10:51:27 ,  2012-05-22 09:51:27 ,  asdgasdgfsdfg ,  asdgfasdg ,   ,  publish ,  open ,  closed ,   ,  asdgfasdg ,   ,   ,  2012-05-22 10:51:27 ,  2012-05-22 09:51:27 ,   , 0,  http://localhost/?post_type=chatt&p=1457 , 0,  chatt ,   , 0),
    (1456, 1,  2012-05-22 10:48:13 ,  0000-00-00 00:00:00 ,  sadfasdfasdfasdf ,  asdfasdfasdf ,   ,  draft ,  open ,  closed ,   ,   ,   ,   ,  2012-05-22 10:48:13 ,  0000-00-00 00:00:00 ,   , 0,  http://localhost/?post_type=chatt&p=1456 , 0,  chatt ,   , 0),
    (1455, 1,  2012-05-22 10:38:47 ,  2012-05-22 09:38:47 ,  ghkjhlkhkjhkj ,  jhgfjhfjh ,   ,  trash ,  open ,  closed ,   ,  jhgfjhfjh ,   ,   ,  2012-05-22 10:38:47 ,  2012-05-22 09:38:47 ,   , 0,  http://localhost/?post_type=chatt&p=1455 , 0,  chatt ,   , 0),
    (1454, 1,  2012-05-22 10:37:04 ,  2012-05-22 09:37:04 ,  random text ,  awegfw ,   ,  publish ,  open ,  closed ,   ,  asdasd ,   ,   ,  2012-05-22 10:37:04 ,  2012-05-22 09:37:04 ,   , 0,  http://localhost/?post_type=chatt&p=1454 , 0,  chatt ,   , 0),
    (1453, 1,  2012-05-22 01:27:02 ,  2012-05-22 00:27:02 ,  asdfasdfasdf ,  asdfasdf ,   ,  publish ,  open ,  closed ,   ,  asdfasdf ,   ,   ,  2012-05-22 01:27:02 ,  2012-05-22 00:27:02 ,   , 0,  http://localhost/?post_type=chatt&p=1453 , 0,  chatt ,   , 0);



    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
1458    0   2012-05-22 11:15:59 0000-00-00 00:00:00 Chatuser chat !?    askwlfoef askwlfoef     draft   open    closed                  2012-05-22 11:15:59 0000-00-00 00:00:00     0   http://localhost/?post_type=chatt&p=1458    0   chatt       0
1457    18  2012-05-22 10:51:27 2012-05-22 09:51:27 asdgasdgfsdfg   asdgfasdg       publish open    closed      asdgfasdg           2012-05-22 10:51:27 2012-05-22 09:51:27     0   http://localhost/?post_type=chatt&p=1457   0   chatt       0
1456    1   2012-05-22 10:48:13 0000-00-00 00:00:00 sadfasdfasdfasdf    asdfasdfasdf        draft   open    closed                  2012-05-22 10:48:13 0000-00-00 00:00:00     0   http://localhost/?post_type=chatt&p=1456    0   chatt       0
1455    1   2012-05-22 10:38:47 2012-05-22 09:38:47 ghkjhlkhkjhkj   jhgfjhfjh       trash   open    closed      jhgfjhfjh           2012-05-22 10:38:47 2012-05-22 09:38:47     0   http://localhost/?post_type=chatt&p=1455   0   chatt       0
1454    1   2012-05-22 10:37:04 2012-05-22 09:37:04 asd2ewfefasdf   askwlfoef askwlfoef     publish open    closed      askwlfoef-askwlfoef         2012-05-22 10:37:04 2012-05-22 09:37:04     0   http://localhost/?post_type=chatt&p=1454   0   chatt       0
1453    1   2012-05-22 01:27:02 2012-05-22 00:27:02 asdfasdfasdf    asdfasdf        publish open    closed      asdfasdf            2012-05-22 01:27:02 2012-05-22 00:27:02     0   http://localhost/?post_type=chatt&p=1453   0   chatt       0
最佳回答

除非需要包含 NULL 值, LEFT JOIN 是不必要的。

根据你的最后一句和评论:

    SELECT chat.*, 
           timestamp.*,
           wp_posts.* 
      FROM wp_posts
INNER JOIN wp_postmeta chat
        ON wp_posts.ID = chat.post_id 
           AND chat.meta_key =  aw_chat_id 
INNER JOIN wp_postmeta timestamp
        ON wp_posts.ID = timestamp.post_id 
           AND timestamp.meta_key =  aw_chat_timestamp 
     WHERE wp_posts.post_type =  chatt 
       AND timestamp.meta_value > 2
       AND chat.meta_value =  test-chat-av-chattuser 
问题回答

暂无回答




相关问题
SQL SubQuery getting particular column

I noticed that there were some threads with similar questions, and I did look through them but did not really get a convincing answer. Here s my question: The subquery below returns a Table with 3 ...

please can anyone check this while loop and if condition

<?php $con=mysql_connect("localhost","mts","mts"); if(!con) { die( unable to connect . mysql_error()); } mysql_select_db("mts",$con); /* date_default_timezone_set ("Asia/Calcutta"); $date = ...

php return a specific row from query

Is it possible in php to return a specific row of data from a mysql query? None of the fetch statements that I ve found return a 2 dimensional array to access specific rows. I want to be able to ...

Character Encodings in PHP and MySQL

Our website was developed with a meta tag set to... <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" /> This works fine for M-dashes and special quotes, etc. However, I ...

Pagination Strategies for Complex (slow) Datasets

What are some of the strategies being used for pagination of data sets that involve complex queries? count(*) takes ~1.5 sec so we don t want to hit the DB for every page view. Currently there are ~...

Averaging a total in mySQL

My table looks like person_id | car_id | miles ------------------------------ 1 | 1 | 100 1 | 2 | 200 2 | 3 | 1000 2 | 4 | 500 I need to ...

热门标签