如何获取MySQL中的结果数量（行）？

问题描述：

SELECT 
    `s`.`FIRMA_UNVANI` AS `FIRMA_UNVANI`, 
    `s`.`RECNO` AS `RECNO`, 
    `s`.`BOLGE` AS `BOLGE`, 
    `s`.`BOLGE_NO` AS `BOLGE_NO`, 
    `s`.`DURUM` AS `DURUM`, 
    l.ILCE, 
    IL.SEHIR, 
    count(i.recno) AS NUMBER_OF_WORKS 
FROM 
    `SERVISLER` `s` 
LEFT JOIN KULLANICI k ON (s.BOLGE = k.KULLANICI) 
LEFT JOIN kullanici_cihaz kc ON (k.RECNO = kc.KUL_RECNO) 
LEFT JOIN servisler_ilceler c ON (s.RECNO = c.SER_RECNO) 
INNER JOIN ILCE l ON (l.RECNO = c.ILCE_RECNO) 
INNER JOIN IL ON (IL.ID = l.ILID) 
LEFT JOIN ISEMRI i ON (
    i.bolge = s.bolge_no 
    AND i.`SERVIS_DURUMU` = 1 
) 
WHERE 
    1 = 1 
GROUP BY 
    s.BOLGE 
ORDER BY 
    IS_SAYISI 
LIMIT 0, 
15

我得到的结果只有一个

+----------------+-------+------+---------+------+-----+----------+-----------------+ 
|FIRMA_UNVANI |RECNO |BOLGE |BOLGE_NO |DURUM |ILCE | SEHIR | NUMBER_OF_WORKS | 
+----------------+-------+------+---------+------+-----+----------+-----------------+ 
|Pirana   |2501 |Tekkt |58  |-1 |NT |Istanbul |1428    | 
+----------------+-------+------+---------+------+-----+----------+-----------------+

这里的关键是RECNO。

我想重要的结果：

SELECT 
    count(0) AS _count 
FROM 
    `SERVISLER` `s` 
LEFT JOIN KULLANICI k ON (s.BOLGE = k.KULLANICI) 
LEFT JOIN kullanici_cihaz kc ON (k.RECNO = kc.KUL_RECNO) 
LEFT JOIN servisler_ilceler c ON (s.RECNO = c.SER_RECNO) 
INNER JOIN ILCE l ON (l.RECNO = c.ILCE_RECNO) 
INNER JOIN IL ON (IL.ID = l.ILID) 
LEFT JOIN ISEMRI i ON (
    i.bolge = s.bolge_no 
    AND i.`SERVIS_DURUMU` = 1 
) 
WHERE 
    1 = 1 
GROUP BY 
    s.BOLGE

而且我得到这个结果有线：

它应该是1。不是吗？

答

结果是完全正常的，因为你只是在每一行，而不是i.recno计数为0，因此，这两个导致1428

要计算结果的数量，你可以换你的整个这样的查询到获得resultcount：

SELECT count(*) AS resultcount FROM (
    SELECT 
     `s`.`FIRMA_UNVANI` AS `FIRMA_UNVANI`, 
     `s`.`RECNO` AS `RECNO`, 
     `s`.`BOLGE` AS `BOLGE`, 
     `s`.`BOLGE_NO` AS `BOLGE_NO`, 
     `s`.`DURUM` AS `DURUM`, 
     l.ILCE, 
     IL.SEHIR, 
     count(i.recno) AS NUMBER_OF_WORKS 
    FROM 
     `SERVISLER` `s` 
    LEFT JOIN KULLANICI k ON (s.BOLGE = k.KULLANICI) 
    LEFT JOIN kullanici_cihaz kc ON (k.RECNO = kc.KUL_RECNO) 
    LEFT JOIN servisler_ilceler c ON (s.RECNO = c.SER_RECNO) 
    INNER JOIN ILCE l ON (l.RECNO = c.ILCE_RECNO) 
    INNER JOIN IL ON (IL.ID = l.ILID) 
    LEFT JOIN ISEMRI i ON (
     i.bolge = s.bolge_no 
     AND i.`SERVIS_DURUMU` = 1 
    ) 
    GROUP BY 
     s.BOLGE 
    ORDER BY 
     IS_SAYISI 
    LIMIT 0, 15) AS temp

还要注意，WHERE 1=1是没有必要的。

它最终只需要'temp'。 – ilhan 2014-10-11 10:32:59

答

你已经在你的查询中使用GROUP BY所以你会得到的结果各组计数，例如，如果有3组，然后你会得到3计数结果..

如果你需要得到结果的行数，然后请使用下面的子查询

SELECT count(0) AS _count FROM(
    SELECT 
    * 
    FROM 
    `SERVISLER` `s` 
    LEFT JOIN KULLANICI k ON (s.BOLGE = k.KULLANICI) 
    LEFT JOIN kullanici_cihaz kc ON (k.RECNO = kc.KUL_RECNO) 
    LEFT JOIN servisler_ilceler c ON (s.RECNO = c.SER_RECNO) 
    INNER JOIN ILCE l ON (l.RECNO = c.ILCE_RECNO) 
    INNER JOIN IL ON (IL.ID = l.ILID) 
    LEFT JOIN ISEMRI i ON (
    i.bolge = s.bolge_no 
    AND i.`SERVIS_DURUMU` = 1 
    ) 
    WHERE 
    1 = 1 
    GROUP BY 
    s.BOLGE 
) AS Temp;

如何获取MySQL中的结果数量（行）？

相关推荐