首页 \ 问答 \ 将字母前缀到整数字段(Prefixing Letters to an Integer Field)

将字母前缀到整数字段(Prefixing Letters to an Integer Field)

mysql

 在我的数据库的一个表中，我有一个表，其中包含每个客户的ID（整数）。  
 不同的客户来自不同的国家，我想在他们的'ID'前加两个字符。  
 假设我有一个ID为12345的客户。  
 如何将前缀“EN”添加到该客户编号？  
 我知道我可以在变量上使用CAST（），但对于通过查询获取的字段，此过程是否相同？ 

Within a table in my database I have a table which holds IDs (Integers) for each customer. 
Different customers come from different countries and i would like to prefix their 'ID' with two characters. 
Say I had a customer with an ID of 12345. 
How would I add the prefix 'EN' to that customer number? 
I'm aware I can CAST() on a variable, however is this process the same for a field acquired through a query?

原文：https://stackoverflow.com/questions/12003924

更新时间：2022-03-25 15:03

最满意答案

 好像  
SELECT uh.User_id, COUNT(uh.Hobby_id)
   FROM User_Location ul JOIN User_Hobby uh USING (User_id)
   WHERE ul.Location_id = 1
     AND uh.Hobby_id in (1, 2, 3, 4, 5)
     GROUP BY (uh.User_id)
 
 应该给出你正在寻找的结果。 

Looks like 
SELECT uh.User_id, COUNT(uh.Hobby_id)
   FROM User_Location ul JOIN User_Hobby uh USING (User_id)
   WHERE ul.Location_id = 1
     AND uh.Hobby_id in (1, 2, 3, 4, 5)
     GROUP BY (uh.User_id)
 
should give the results you're looking for.

相关问答

有什么数据库专业书籍介绍？[2022-05-19]

mysql
在不同的表中使用具有多个条件的MySQL Count（）(Using MySQL Count() with multiple conditions in different table)[2023-06-01]

好像 SELECT uh.User_id, COUNT(uh.Hobby_id) FROM User_Location ul JOIN User_Hobby uh USING (User_id) WHERE ul.Location_id = 1 AND uh.Hobby_id in (1, 2, 3, 4, 5) GROUP BY (uh.User_id) 应该给出你正在寻找的结果。 Looks like SELECT uh.User_id, COUNT(uh.Hobby_ ...
MySQL连接和COUNT（）多个表(MySQL join and COUNT() multiple tables)[2022-06-25]

您将需要使用DISTINCT ，但您还需要计算ID，而不是外键： SELECT table1.name, COUNT(DISTINCT table2.id) AS table2_count, COUNT(DISTINCT table3.id) AS table3_count, COUNT(DISTINCT table4.id) AS table4_count, SUM(table4.size) AS table4_size FROM table1 LEFT JOIN ...
用三个表选择COUNT - mysql(select COUNT with three table - mysql)[2022-01-18]

通过使用Sub-Query OR UNION可以从多个表中获取COUNT 。试试这个查询： SELECT (SELECT count(*) FROM `car_model` WHERE user_id=3 ) AS car_model_count, (SELECT COUNT(*) FROM `list_item` WHERE user_id=3) AS list_item_count, (SELECT count(*) FROM `part_item` WHERE user_i ...
MySQL - 在一个连接表中计算不同条件下的不同字段(MySQL - count different fields on different conditions in one joined table)[2022-11-23]

SELECT some_column, (SELECT COUNT(1) FROM Table1 WHERE mt.id = joinable_key AND condition1 = 'something' ) AS first_condition, (SELECT COUNT(1) FROM Table1 WHERE mt.id = joinable_key ...
平均数和条件 - mysql(Average and count with conditions - mysql)[2022-12-03]

您可以使用GROUP BY查询平均值： SELECT `date`, `location`, AVG(`TV`) AS `avgtv` FROM `data` WHERE `fp` = 1 GROUP BY `date`, `location` 要用您的计算平均值更新另一个表（我强烈建议不要），可以使用UPDATE...JOIN作为子查询： UPDATE ratings r JOIN ( /* paste above query here */ ) t ON t.date = r.da ...
php mysql根据多个条件获取最新记录并计算它们(php mysql Get the latest records based on multiple conditions and count them)[2022-10-16]

select staff_id, max(date_of_appointment) as date_of_appointment from employment_history group by staff_id 此查询将返回每个staff_id的最新员工记录。将其转换为子查询并加入就业历史表 Select grade, count(1) from (select staff_id, max(date_of_appointment) as date_of_appointment from employ ...
MySQL查询使用多个表上的count(MySQL query using count on multiple tables)[2023-10-16]

SELECT r.rid, fname, lname FROM realtor AS r INNER JOIN contract AS c ON r.rid=c.buyrid INNER JOIN property AS p on r.rid=p.sellrid GROUP BY r.rid ORDER BY COUNT(*) DESC LIMIT 1 SELECT r.rid, fname, lname FROM realtor ...
来自单个表的多个Count（）(Multiple Count() from a single table)[2023-09-16]

是的，你可以结合SUM和CASE： SELECT SUM(CASE WHEN condition1 THEN 1 ELSE 0 END) count1, SUM(CASE WHEN condition2 THEN 1 ELSE 0 END) count2 FROM yourtable 所以对于你的具体例子： SELECT SUM(CASE WHEN days < 15 THEN 1 ELSE 0 END) count1, SUM(CASE WHEN days BETWEEN ...
Mysql查询结果来自多个条件(Mysql query result from multiple conditions)[2021-10-09]

foreach($filter_food->filterFood() as $filter_food) 这将永远不会有效，因为你正在使用数组$ filter_food，然后你正在使每个结果$ filter_food导致一个backloop，因此它将无法工作。您可以将$ filter_food更改为$ filtered_food或其他任何内容以使其正常工作。另外，我经常写这样的查询：任何列或表我使用``引号，任何变量我使用“...”这可以解决你的查询属性问题。特别是引用表，因为它可能会将其中一个表视为 ...