group_concat 多次？

发布于 2024-12-04 17:19:20 字数 557 浏览 0 评论 0原文

我正在尝试创建一个 MySQL 查询，该查询使用不同的 group_concat 和来自相同 LEFT JOIN 的数据。示例表可以是：

汽车表卡里德| License_plate

轮胎表（表示二手轮胎）卡里德|型号|宽度

从这些表中，我想获取按宽度分类的轮胎型号列表，其想法是只有两种不同的可能宽度，并且我从汽车卡页面加载它。

 SELECT name, 
 if(width=205,group_concat(model ORDER BY model ASC),'') as width_205,
 if(width=225,group_concat(model ORDER BY model ASC),'') as width_225,
 FROM cars
 LEFT  JOIN tires ON cars.carid=tires.carid
 WHERE carid='10'

我希望我的解释是清楚的。这句话不能正常工作，我不知道是因为语法错误还是我根本无法以这种方式使用 group_concat 。

好吧，感谢您的阅读，我会等待您的答案。提前致谢！

原文

I'm trying to make a MySQL query that uses different group_concat with data from the same LEFT JOIN. An example tables could be:

Cars table
carid | license_plate

Tires table (means used tires)
carid | model | width

From these tables i want to obtain a list of tire models classified by their width, with the idea that there are only two different possible widths and I'm loading it from a car card page.

 SELECT name, 
 if(width=205,group_concat(model ORDER BY model ASC),'') as width_205,
 if(width=225,group_concat(model ORDER BY model ASC),'') as width_225,
 FROM cars
 LEFT  JOIN tires ON cars.carid=tires.carid
 WHERE carid='10'

I hope that my explanation is clear. This sentence doesn't work properly, and I don't know if it's because of a bad syntax or that I simply can't use group_concat this way.

Well, thanks for reading and I'll be waiting for your answers. Thanks in advance!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

神仙妹妹 2024-12-11 17:19:20

努力

SELECT name, 
  GROUP_CONCAT(CASE width WHEN 205 THEN model ELSE NULL END CASE AS model ORDER BY model) as width_205,
  GROUP_CONCAT(CASE width WHEN 225 THEN model ELSE NULL END CASE AS model ORDER BY model) as width_225,
FROM cars
LEFT  JOIN tires ON cars.carid=tires.carid
WHERE carid='10'
GROUP BY name

得到你想要的。我没有方便测试MySQL。您可能不得不欺骗（甚至放弃）内部 ORDER BY。

或者，您可以使用以下命令取回两行（如果添加了第三个大小，则不必更改 SQL）：

SELECT name, width, GROUP_CONCAT(model ORDER BY model) AS Models
FROM cars
LEFT  JOIN tires ON cars.carid=tires.carid
WHERE carid='10'
GROUP BY name, width

Try

SELECT name, 
  GROUP_CONCAT(CASE width WHEN 205 THEN model ELSE NULL END CASE AS model ORDER BY model) as width_205,
  GROUP_CONCAT(CASE width WHEN 225 THEN model ELSE NULL END CASE AS model ORDER BY model) as width_225,
FROM cars
LEFT  JOIN tires ON cars.carid=tires.carid
WHERE carid='10'
GROUP BY name

to get what you want. I don't have MySQL handy to test. You may have to fool with (or even give up) the internal ORDER BY.

Alternatively, you can get back two rows (and not have to change your SQL if a third size is ever added) with:

SELECT name, width, GROUP_CONCAT(model ORDER BY model) AS Models
FROM cars
LEFT  JOIN tires ON cars.carid=tires.carid
WHERE carid='10'
GROUP BY name, width

回复收藏 0 原文

想你的星星会说话 2024-12-11 17:19:20

两种解决方案都很棒！也许第二个更优雅，最后我将使用它，但我也会注意到第一个，因为它更具体，并且在某些情况下可能更好。

对于第一种方法，我必须在“END”之后删除“CASE AS model”以防止 MySQL 错误：

SELECT name,
GROUP_CONCAT(CASE width WHEN 205 THEN model ELSE NULL END ORDER BY model) as width_205,
GROUP_CONCAT(CASE width WHEN 225 THEN model ELSE NULL END ORDER BY model) as width_225,
从汽车
左连接轮胎上 cars.carid=tires.carid
其中 carid='10'
GROUP BY name

我还在 group_concat 内的 CASE 之前添加了 DISTINCT，以避免重复。

对于第二种方法，我只在句末添加了“ORDER BY width ASC”。对于可能需要此方法的其他人，不要忘记 mysql_fetch_(whatever) 已经获取包含 group_concat 第一个案例的第一个结果。

多谢！ CU