如何在mysql union all中识别表

d7v8vwbk 于 2021-06-21 发布在 Mysql

关注(0)|答案(3)|浏览(301)

我有如下疑问

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

它从两个tabel中获取数据，并按datetime的顺序显示它。现在我在前面显示这个数据。但现在我想知道哪些数据来自哪个表。
因为我调用一个modal来显示modal中的一个注解

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

但是 $row[0] 给了我一个id，但我如何才能确定这个id来自哪个表？

sql mysql php mysqli

来源：https://stackoverflow.com/questions/49828925/how-can-identify-table-in-mysql-union-all

3条答案

按热度按时间

l5tcr1uw1#

您可以向两个表中的每个表添加一列，并用自定义字符串填充它。例如，第一个子查询将以新列“table\u name”开始，如下所示。。。从注解中选择*，“注解”表名称。。。

赞(0）回复(0）举报 2021-06-21

fhity93d2#

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

赞(0）回复(0）举报 2021-06-21

cczfrluj3#

我会把你的问题改写成 tablename 你想要的

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

我想如果你有 user_id , product_id 如果是数字类型，则应仅使用不带单引号的值。

where user_id = 66 AND product_id = 3

赞(0）回复(0）举报 2021-06-21

我来回答

如何在mysql union all中识别表

3条答案

相关问题

热门标签

最新问答