代码之家 › 专栏 › 技术社区 › Utku Dalmaz

可代替mysql_num_行的mysql查询

mysql php

0

Utku Dalmaz · 技术社区 · 15 年前

我想知道如何编写一个MySQL查询来检查其他表中有多少记录?

table 1 : names

id name
-- ----
4  john
5  mike
6  jenny

table 2 : cars

id name_id car
-- ------- ---
1   4      bmw
2   4      wv
3   6      honda

query = "SELECT * FROM names WHERE id = '4'" <-在该查询中,我还想检查cars表中有多少记录,或者是否有记录?

3 回复 | 直到 15 年前

1

3

JonVD 15 年前

应该这样做:

SELECT names.id, name, COUNT(car) as 'cars'
FROM names LEFT JOIN cars ON names.id = cars.nameid 
WHERE names.id = '4'
GROUP BY names.id, name

2

1

Nico Huysamen 15 年前

SELECT n.*, COUNT(c.*) FROM names n, cars c WHERE n.id = 4

您还可以为该列命名:

SELECT n.*, COUNT(c.*) AS `num_cars` FROM names n, cars c WHERE n.id = 4

3

1

Lieven Keersmaekers 15 年前

把每个人的车加在一起 LEFT OUTER JOIN

SELECT  n.*, c.cnt
FROM    names n
        LEFT OUTER JOIN (
          SELECT  name_id, cnt = COUNT(*)
          FROM    cars
          GROUP BY
                  name_id
        ) c ON c.name_id = n.id
WHERE   ID = 4

如果性能是一个问题,那么您可以以牺牲可维护性为代价将条件添加到左联接中。

SELECT  n.*, c.cnt
FROM    names n
        LEFT OUTER JOIN (
          SELECT  name_id, cnt = COUNT(*)
          FROM    cars
          WHERE   name_id = 4 -- Condition needs to be updated in two places
          GROUP BY
                  name_id
        ) c ON c.name_id = n.id
WHERE   ID = 4