如何在一个结果中连接两个sql查询

l0oc07j2 于 2021-06-21 发布在 Mysql

关注(0)|答案(2)|浏览(338)

我有两个sql查询
第一：

SELECT ticketing_ticket.id, ticketing_ticket.email,
CONCAT(
TIMESTAMPDIFF(day,date_create,date_close) , ' jours ',
MOD( TIMESTAMPDIFF(hour,date_create,date_close), 24), ' heures ',
MOD( TIMESTAMPDIFF(minute,date_create,date_close), 60), ' miinutes '
) AS 'temps de traitement '
FROM ticketing_ticket WHERE DATE(date_close) = CURDATE();

输出：

|  email       |       Time
   |test@test.com | 15 jours 23 heures 7 miinutes 
   |test@test.com | 15 jours 22 heures 54 miinutes

第二：

SELECT GROUP_CONCAT(T2.label SEPARATOR ';') AS 'Domaines'
FROM (
    SELECT
        @r AS _id,
        (SELECT @r := id_parent FROM ticketing_category WHERE id_category = _id) AS parent_id,
        @l := @l + 1 AS lvl
    FROM
        (SELECT @r := 38, @l := 0) vars,
        ticketing_category m
    WHERE @r <> 0) T1
JOIN ticketing_category T2
ON T1._id = T2.id_category
ORDER BY T1.lvl DESC

输出：

|          Domaines           | 
 |Elections;Aides et démarches |

如何将sql查询组合成这样一个结果

|  email       |       Time                        | Domaines           
|test@test.com |    15 jours 23 heures 7 miinutes  | Elections;Aides et démarches 
|test@test.com |    15 jours 22 heures 54 miinutes | Elections;Aides et démarches

更新：
我在票务和票务类别之间的连接是id类别

mysql

来源：https://stackoverflow.com/questions/49819635/how-concat-two-sql-query-in-a-single-result

2条答案

按热度按时间

aiqt4smr1#

因为您似乎对如何连接记录没有任何要求，所以可以简单地进行交叉连接。

SELECT * FROM (
SELECT ticketing_ticket.email,
CONCAT(
TIMESTAMPDIFF(day,date_create,date_close) , ' jours ',
MOD( TIMESTAMPDIFF(hour,date_create,date_close), 24), ' heures ',
MOD( TIMESTAMPDIFF(minute,date_create,date_close), 60), ' miinutes '
) AS 'temps de traitement '
FROM ticketing_ticket WHERE DATE(date_close) = CURDATE()
) a,
(
SELECT GROUP_CONCAT(T2.label SEPARATOR ';') AS 'Domaines'
FROM (
    SELECT
        @r AS _id,
        (SELECT @r := id_parent FROM ticketing_category WHERE id_category = _id) AS parent_id,
        @l := @l + 1 AS lvl
    FROM
        (SELECT @r := 38, @l := 0) vars,
        ticketing_category m
    WHERE @r <> 0) T1
JOIN ticketing_category T2
ON T1._id = T2.id_category
ORDER BY T1.lvl DESC
) b;

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

wgeznvg72#

试试看：

DECLARE @Tickets TABLE (
    TicketId INT,
    Email VARCHAR(50)
)

DECLARE @Domaines TABLE (
    DomaineId INT,
    DomainName VARCHAR(50)
)

DECLARE @TicketDomaine Table(
    TicketId INT,
    DomaineId VARCHAR(15)
)

INSERT INTO @Tickets
    SELECT 1, 'test@gmail.com'
    UNION SELECT 2, 'test2@gmail.com'

INSERT INTO @Domaines
    SELECT 1, 'Élections'
    UNION SELECT 2, 'Aides et démarches'

INSERT INTO @TicketDomaine
    SELECT 1, 1
    UNION SELECT 1, 2
    UNION SELECT 2, 1

SELECT      
 T.TicketId
 , ISNULL((SELECT D.DomainName + ',' AS [data()]    
    FROM @TicketDomaine TD  
    INNER JOIN @Domaines D ON TD.DomaineId = D.DomaineId
    WHERE  TD.TicketId = T.TicketId
    FOR XML PATH('')), '') 'Mots-clés'  
FROM @Tickets T
    --WHERE T.TicketId = 2

输出：

1   Élections, Aides et démarches,
2   Élections,

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

我来回答

如何在一个结果中连接两个sql查询

2条答案

相关问题

热门标签

最新问答