时间戳

首页 > 解决方案 > 名字和姓氏的首字母仅在数据库查询中

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

标签： mysqlsql

解决方案

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

但是您不应该在 where 条件中使用左连接表列作为内部连接
将这些条件添加到 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  ";

推荐阅读