MySQL 查询不返回此查询的所有行

Question

我有以下查询:

SELECT 
  IF(COUNT(*) > 0, COUNT(*), '0') AS Found, 
  IF(NWLat, NWLat, '0')           AS NWLat, 
  IF(NWLon, NWLon, '0')           AS NWLon, 
  IF(SELat, SELat, '0')           AS SELat, 
  IF(SELon, SELon, '0')           AS SELon 
FROM s 
WHERE s.ci = '4' 
  AND s.snf = 'Ch'
GROUP BY s.sid 

现在它返回匹配查询的行。但是，当没有任何匹配时，它什么也不返回。如果没有找到，我如何让它返回“Found: 0, NWLat: 0, NWLon: 0, SELat: 0, SELon: 0”？

Answer 1

最好不要在 MySQL 中这样做。 SQL与此无关。从您的前端应用程序，如果您的查询没有返回任何记录，那么您可以轻松地打印 0。

但是，如果您需要以任何方式执行此操作，您可以执行以下操作:

SELECT 
  IF(COUNT(*) > 0, COUNT(*), '0') AS Found, 
  IF(NWLat, NWLat, '0')           AS NWLat, 
  IF(NWLon, NWLon, '0')           AS NWLon, 
  IF(SELat, SELat, '0')           AS SELat, 
  IF(SELon, SELon, '0')           AS SELon 
FROM s 
WHERE s.ci = '4' 
  AND s.snf = 'Ch'
GROUP BY s.sid 
UNION ALL
SELECT
  '0' AS Found, 
  '0' AS NWLat, 
  '0' AS NWLon, 
  '0' AS SELat, 
  '0' AS SELon 
FROM s 
WHERE NOT EXISTS(
    SELECT 
      IF(COUNT(*) > 0, COUNT(*), '0') AS Found, 
      IF(NWLat, NWLat, '0')           AS NWLat, 
      IF(NWLon, NWLon, '0')           AS NWLon, 
      IF(SELat, SELat, '0')           AS SELat, 
      IF(SELon, SELon, '0')           AS SELon 
    FROM s 
    WHERE s.ci = '4' 
    AND s.snf = 'Ch'
    GROUP BY s.sid
);

演示:

• If there is records .

• IF there is no records returned ，这会给你:

  | FOUND | NWLAT | NWLON | SELAT | SELON |
  -----------------------------------------
  |     0 |     0 |     0 |     0 |     0 |