php mySQL组concat和group by [英] php mySQL group concat and group by

问题描述

我有两个桌子

table 1: 
 row  | car_id | car_model | 
  1      1          CAR 1      
  2      2          CAR 2      
  3      3          CAR 3  
  4      4          CAR 4

table 2:

 row  | car_id |  car_features |
  1      1          Features 1
  2      2          Features 2
  3      2          Features 3

我要显示的内容:

 row  | car_id | car_model | car_features |
  1      1          CAR 1      Features 1
  2      2          CAR 2      Features 2,Features 3
  3      3          CAR 3      
  4      4          CAR 4      

如果我将group concat与group by一起使用

if I using group concat with group by

将显示第1,2行，而我想显示所有行...

there will display row 1,2 and I want to display all the rows...

     row  | car_id | car_model | car_features |
      1      1          CAR 1      Features 1
      2      2          CAR 2      Features 2,Features 3

请将sql语句发送给我.谢谢！

Please send me the sql statement..THANK!!

这是我的查询

$format = mysql_query("SELECT c.* , p.*, d.*,f.* ,GROUP_CONCAT(DISTINCT e.features_title SEPARATOR '<br>') AS 'car_features' FROM bsi_car_master c,bsi_car_type p, bsi_car_vendor d, bsi_selected_features f, bsi_car_features e WHERE c.car_type_id=p.id AND c.car_vendor_id=d.id AND c.car_id = f.car_id AND f.features_id = e.id GROUP BY c.car_id");

推荐答案

使用外部联接，即使在table2中不存在匹配记录的情况下也可以获取行.

Use an outer join, so as to get rows even when no matching record in table2 exists.

更新:现在您已经显示了查询:

UPDATE: Now that you have showed your query:

看到您的查询很奇怪，因为一方面您似乎是一个初学者，另一方面您使用的连接语法是上个世纪讲授的，并且早已过时.

It is very strange to see your query, because on one hand you seem to be a beginner, on the other you are using a join syntax that was taught last century and is long since out-dated.

因此您的查询最好如下所示:

So your query should better look like this:

SELECT 
  c.*, 
  p.*, 
  d.*,
  f.*,
  GROUP_CONCAT(DISTINCT e.features_title SEPARATOR '<br>') AS car_features 
FROM bsi_car_master c
INNER JOIN bsi_car_type p ON p.id = c.car_type_id
INNER JOIN bsi_car_vendor d ON d.id = c.car_vendor_id
LEFT JOIN bsi_selected_features f ON f.car_id = c.car_id
LEFT JOIN bsi_car_features e ON e.id = f.features_id
GROUP BY c.car_id

p和d是内部连接的，即p和d必须匹配，否则不会选择c记录.但是f是外部联接的，因此即使f中没有针对该c的记录(那么该记录中的所有f列都为空)，您也将得到c(以及p和d).至于选择e，我们还必须对此进行外部联接，因为同样，如果没有匹配项，我们仍然希望记录c具有p和d.

p and d are inner-joined, i.e. there must be a match in p and d otherwise the c record is not selected. But f is outer-joined, so you get c (and p and d) even when no record in f exists for that c (then all f columns are null in that record). As to selecting e, we must also outer-join this, because again, if we got no match, we still want the record c with p and d.

我将'car_features'更改为car_features.单引号用于字符串文字.双引号用于名称，但此处不需要双引号

I changed 'car_features' to car_features by the way. Single quotes are for string literals. Double quotes are for names, but they are not needed here

这篇关于php mySQL组concat和group by的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！