sql中的完美排列

yb3bgrhw  于 2021-07-26  发布在  Java
关注(0)|答案(3)|浏览(401)

有关客户的信息存储在customer表中。编写一个查询以打印姓名长度总和(即姓名和姓氏的长度之和)小于12的客户的id、姓名和姓氏。ID和名称应按组合名称长度的升序打印。如果两个或多个客户具有相同的组合名称长度,则按全名的词典顺序对结果排序,忽略大小写。如果两个或多个客户具有相同的全名,请按id升序对结果进行排序。
我尝试的是:

SELECT customer_id, CONCAT(FIRST_NAME, “ “, LAST_NAME) AS full_name
 , LENGTH(CONCAT(FIRST_NAME, LAST_NAME)) AS length_name
FROM customer 
WHERE len(CUSTOMER.FIRST_NAME)+len(CUSTOMER.LAST_NAME) < 12 ,
ORDER BY  CUSTOMER.FIRST_NAME, CUSTOMER.LAST_NAME, CUSTOMER.ID ASC;

我试着得到结果,但没有成功。我知道查询中有问题,但不知道如何解决。请找人帮忙。

ezykj2lf

ezykj2lf1#

据我所知你的分类要求是-
ID和名称应按组合名称长度的升序打印。
如果两个或多个客户具有相同的组合名称长度,则按全名的词典顺序对结果排序,忽略大小写。
如果两个或多个客户具有相同的全名,请按id升序对结果进行排序。
两个建议是:(1)使用trim(),因为您在比较中处理文本;(2)使用upper(),在按字典顺序排序时忽略大小写。

SELECT customer_id, CONCAT(TRIM(FIRST_NAME), “ “, TRIM(LAST_NAME)) AS full_name
 , LENGTH(CONCAT(TRIM(FIRST_NAME), TRIM(LAST_NAME))) AS length_name
FROM customer 
WHERE LENGTH(CONCAT(TRIM(FIRST_NAME), TRIM(LAST_NAME)))  < 12 ,
ORDER BY  LENGTH(CONCAT(TRIM(FIRST_NAME), TRIM(LAST_NAME)))  ASC, 
      CONCAT(UPPER(TRIM(FIRST_NAME)), “ “, UPPER(TRIM(LAST_NAME))) ASC,
      CUSTOMER.ID ASC;
niknxzdl

niknxzdl2#

在order by之前删除逗号也会导致错误。

SELECT id, CONCAT(TRIM(FIRST_NAME), “ “, TRIM(LAST_NAME)) AS full_name
   FROM customer 
   WHERE LENGTH(CONCAT(TRIM(FIRST_NAME), TRIM(LAST_NAME)))  < 12 
   ORDER BY  LENGTH(CONCAT(TRIM(FIRST_NAME), TRIM(LAST_NAME)))  ASC, 
   CONCAT(UPPER(TRIM(FIRST_NAME)), “ “, UPPER(TRIM(LAST_NAME))) ASC,
   ID ASC;
y1aodyip

y1aodyip3#

你可以试试,我希望这能帮助你:

Select ID, CONCAT(FIRST_NAME, ' ', LAST_NAME) from CUSTOMER WHERE
LENGTH(CONCAT(FIRST_NAME,LAST_NAME)) < 12 ORDER BY
LENGTH(CONCAT(FIRST_NAME,LAST_NAME)), CONCAT(FIRST_NAME,LAST_NAME), ID;

相关问题