mysql - 如何建立两个查询中的一个？

当我将它们粘贴到phpmyadmin的sql控制台中时，我有两个查询表现良好：

第一个查询：

SET @count=0,@msisdn=00385913586990,@delivered=0;

第二个查询：

SELECT tmp.msisdn,max(count) FROM (
        SELECT
        sent_messages.msisdn,
        @count := if(delivered = 0 and sent_messages.msisdn = @msisdn, @count+1, 0) as count,
        @msisdn := 00385913586990,
        @delivered := delivered
        FROM  `sent_messages`
        WHERE  `msisdn` LIKE  '00385913586990'
        AND  `type` =  'PAID' ) as tmp
        group by tmp.msisdn
        having max(count) >= 14

当我尝试在我的php代码中执行这些查询时，得到的结果为空。

这是我尝试将它们绑定在一起的方式：

SELECT tmp.msisdn,max(count) FROM (
            SELECT
            sent_messages.msisdn,@count := 0,@msisdn=00385913586990,@delivered=0,
            @count := if(delivered = 0 and sent_messages.msisdn = @msisdn, @count+1, 0) as count,
            @msisdn := 00385913586990,
            @delivered := delivered
            FROM  `sent_messages`
            WHERE  `msisdn` LIKE  '00385913586990'
            AND  `type` =  'PAID' ) as tmp
            group by tmp.msisdn
            having max(count) >= 14

但这是行不通的。

最佳答案

在您只是交叉联接的子查询中初始化变量。这样，它们将被初始化一次。当您在SELECT子句中执行此操作时，每次读取行时它们都会被撤消。

SELECT tmp.msisdn,max(count) FROM (
        SELECT
        sent_messages.msisdn,
        @count := if(delivered = 0 and sent_messages.msisdn = @msisdn, @count+1, 0) as count,
        @msisdn := 00385913586990,
        @delivered := delivered
        FROM  `sent_messages`
        , (SELECT @count := 0, @msisdn := '00385913586990', @delivered := 0) var_init_subquery_alias
        WHERE  `msisdn` LIKE  '00385913586990'
        AND  `type` =  'PAID' ) as tmp
        group by tmp.msisdn
        having max(count) >= 14

关于mysql - 如何建立两个查询中的一个？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/44128321/