mysql - 如何在 MySQL 中获取结果(行)数？

Question

通常下面的查询只给我一个结果(行)。

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

我得到了这个有线结果:

1428

它应该是 1。不是吗？

Answer 1

结果非常好，因为您只是为每一行计算 0 而不是 i.recno，因此两者的结果都是 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 不是必需的。