mysql - 仅在数据库查询时的名字和姓氏缩写

嗨，您能帮我解决我的问题吗？

我想创建一个配置文件名称，其名称只能像这样Sergio E.而不是这样Sergio Encabo

这是我的查询

SELECT p.id, p.title, COUNT(b.id) AS bids, p.slug, p.`description`, p.budget AS budget, p.`isFeatured`, p.`slug`, u.`profileLink`, u.`profilePhoto`, CONCAT_WS(' ', u.firstName , u.lastName ) AS fullName
FROM tbl_projects AS p
LEFT JOIN tbl_users AS u ON p.userId = u.userId
LEFT JOIN tbl_bids AS b ON p.`id` = b.`projectId`
WHERE p.`isActive` = 'y' AND u.`isActive` = 'y' AND p.`jobStatus` = 'open' AND p.userId=?
GROUP BY p.id
ORDER BY p.`id` DESC

最佳答案

您可以使用左侧的第一个字符作为姓氏

但您不应该在这种情况下将左联接表列用作内部联接
将这些条件添加到on子句

    $qry = "SELECT p.id
        , p.title
        , COUNT(b.id) AS bids
        , p.slug
        , p.`description`
        , p.budget AS budget
        , p.`isFeatured`
        , p.`slug`
        , u.`profileLink`
        , u.`profilePhoto`
        , CONCAT( u.firstName ,' ', left(u.lastName,1), '.' ) AS fullName
        FROM tbl_projects AS p
        LEFT JOIN tbl_users AS u ON p.userId = u.userId   AND u.`isActive` = 'y'
        LEFT JOIN tbl_bids AS b ON p.`id` = b.`projectId`
        WHERE p.`isActive` = 'y'
        AND p.`jobStatus` = 'open'
         AND p.userId=?
         GROUP BY p.id
         ORDER BY p.`id` DESC  ";

关于mysql - 仅在数据库查询时的名字和姓氏缩写，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/54449540/