douju2014
2018-04-14 07:21 阅读 148
已采纳

如何在Mysql Union中识别表

I Have Query As the following

SELECT * FROM ( 
    (
        SELECT * FROM comments 
        WHERE user_id = '66' AND product_id = '3' 
        AND status = 1 
    ) 
    UNION ALL 
    (
        SELECT * FROM comments_reply 
        WHERE user_id = '66' AND product_id = '3'
        AND status = 1 ) 
    ) results 
ORDER BY datetime DESC

Which Gets Data from two tabel and shows it in order to datetime.now frontside i am showing this data. but now i wants to know that which data is came from which table.

because I am calling a modal to show a comment in modal by this

<span class="showcomment text-danger"
     data-cid = <?=$row[0]?>>view Comment</span>

But $row[0] Gives me an id But How can i identify that this id is came from which Table ?

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享

3条回答 默认 最新

  • 已采纳
    duanlan8763 duanlan8763 2018-04-14 07:26

    I would rewrite your query as with tablename that you want

    SELECT 'comments' as table_name, col1, col2,... coln FROM comments c
    WHERE user_id = '66' AND product_id = '3' 
    AND status = 1  UNION ALL 
    SELECT 'comments_reply' as table_name, col1, col2,... coln FROM comments_reply cr
    WHERE user_id = '66' AND product_id = '3'
    ORDER BY col DESC
    

    And, i suspect if you have user_id, product_id has numeric type then you should use only values without single quote.

    where user_id = 66 AND product_id = 3
    
    点赞 评论 复制链接分享
  • duanri1985 duanri1985 2018-04-14 07:38

    You can add a column to each of your two tables and populate it with a custom string. For example, the first sub query would start like the following with the new column “table_name”... select *, ‘comments’ table_name from comments...

    点赞 评论 复制链接分享
  • dpq39825 dpq39825 2018-04-14 07:41
    SELECT * FROM ( 
        (
            SELECT "comments" as tablename,* FROM comments 
            WHERE user_id = '66' AND product_id = '3' 
            AND status = 1 
        ) 
        UNION ALL 
        (
            SELECT "comments_reply" as tablename,* FROM comments_reply 
            WHERE user_id = '66' AND product_id = '3'
            AND status = 1 ) 
        ) results 
    ORDER BY datetime DESC
    
    点赞 评论 复制链接分享

相关推荐