顾客登录名

DROP TABLE IF EXISTS `Customers`; CREATE TABLE IF NOT EXISTS `Customers`( cust_id VARCHAR(255) NOT NULL COMMENT '客户id', cust_name VARCHAR(255) NOT NULL COMMENT '客户姓名', cust_contact VARCHAR(255) NOT NULL COMMENT '客户联系人', cust_city VARCHAR(255) NOT NULL COMMENT '客户城市' ); INSERT `Customers` VALUES ('a1','Andy Li','Andy Li','Oak Park'),('a2','Ben Liu','Ben Liu','Oak Park'),('a3','Tony Dai','Tony Dai','Oak Park'),('a4','Tom Chen','Tom Chen','Oak Park'),('a5','An Li','An Li','Oak Park'),('a6','Lee Chen','Lee Chen','Oak Park'),('a7','Hex Liu','Hex Liu','Oak Park');

Mysql 解法, 执行用时: 38ms, 内存消耗: 6628KB, 提交时间: 2022-03-05

select cust_id, cust_name, 
       upper(concat(substring(cust_contact, 1, 2), substring(cust_city, 1, 3))) as user_login
from Customers

Mysql 解法, 执行用时: 39ms, 内存消耗: 6432KB, 提交时间: 2022-03-06

SELECT cust_id,cust_name,
UPPER(CONCAT(SUBSTR(cust_contact,1,2),SUBSTR(cust_city,1,3))) AS user_login
FROM Customers

Mysql 解法, 执行用时: 39ms, 内存消耗: 6468KB, 提交时间: 2022-03-06

SELECT cust_id,
    cust_name,
    UPPER(CONCAT(SUBSTRING(cust_contact, 1, 2), SUBSTRING(cust_city, 1, 3))) user_login
FROM
    Customers;

Mysql 解法, 执行用时: 39ms, 内存消耗: 6500KB, 提交时间: 2022-03-06

SELECT cust_id,cust_name,Upper(Concat(Left(cust_contact,2),Left(cust_city,3))) user_login
FROM Customers;

Mysql 解法, 执行用时: 39ms, 内存消耗: 6516KB, 提交时间: 2022-03-07

select cust_id,cust_name,upper(concat(left(cust_name,2),left(cust_city,3))) as user_login 
from Customers

cust_id	cust_name	cust_contact	cust_city
a1	Andy Li	Andy Li	Oak Park
a2	Ben Liu	Ben Liu	Oak Park
a3	Tony Dai	Tony Dai	Oak Park
a4	Tom Chen	Tom Chen	Oak Park
a5	An Li	An Li	Oak Park
a6	Lee Chen	Lee Chen	Oak Park
a7	Hex Liu	Hex Liu	Oak Park

cust_id	cust_name	user_login
a1	Andy Li	ANOAK
a2	Ben Liu	BEOAK
a3	Tony Dai	TOOAK
a4	Tom Chen	TOOAK
a5	An Li	ANOAK
a6	Lee Chen	LEOAK
a7	Hex Liu	HEOAK

详情