把一下oracle的查询语句转化为MySQL的查询语句（SELECT c.name, b.diagnose, SUBSTR(TO_CHAR(TRUNC(SYSDATE(), 'yy'), 'yyyymmdd'), 1, 4) - SUBSTR(TO_CHAR(TRUNC(c.birthday, 'yy'), 'yyyymmdd'), 1, 4) AS 年龄, c.home_address FROM wjj_yy_yldsj_mzghxx a, wjj_yy_yldsj_mzgzrz b, wjj_yy_yldsj_grjbxxb c WHERE a.org_reg_id = b.register_id AND a.reg_class = 2 AND a.org_code = b.org_code AND a.personid = c.personid;）

以下是将该Oracle查询语句转化为MySQL查询语句：

SELECT c.name, b.diagnose, SUBSTR(DATE_FORMAT(DATE_TRUNC(NOW(), 'YEAR'), '%Y%m%d'), 1, 4) - SUBSTR(DATE_FORMAT(DATE_TRUNC(c.birthday, 'YEAR'), '%Y%m%d'), 1, 4) AS 年龄, c.home_address
FROM wjj_yy_yldsj_mzghxx a, wjj_yy_yldsj_mzgzrz b, wjj_yy_yldsj_grjbxxb c
WHERE a.org_reg_id = b.register_id
AND a.reg_class = 2
AND a.org_code = b.org_code
AND a.personid = c.personid;

相关问题

把这个sql语句转化为MySQL的sql语句（SELECT c."name", b."diagnose", substrb ( to_char( trunc( SYSDATE, 'yy' ), 'yyyymmdd' ), 0, 4 ) - substrb ( to_char( trunc( c."birthday", 'yy' ), 'yyyymmdd' ), 0, 4 ) "年龄", c."home_address" FROM "wjj_yy_yldsj_mzghxx" a, "wjj_yy_yldsj_mzgzrz" b, "wjj_yy_yldsj_grjbxxb" c WHERE a."org_reg_id" = b."register_id" AND a."reg_class" = 2 AND a."org_code" = b."org_code" AND a."personid" = c."personid";）

以下是将该SQL语句转化为MySQL的语句：

SELECT c.`name`, b.`diagnose`, SUBSTR(TO_CHAR(TRUNC(SYSDATE(), 'yy'), 'yyyymmdd'), 1, 4) - SUBSTR(TO_CHAR(TRUNC(c.`birthday`, 'yy'), 'yyyymmdd'), 1, 4) AS `年龄`, c.`home_address`
FROM `wjj_yy_yldsj_mzghxx` a, `wjj_yy_yldsj_mzgzrz` b, `wjj_yy_yldsj_grjbxxb` c
WHERE a.`org_reg_id` = b.`register_id`
AND a.`reg_class` = 2
AND a.`org_code` = b.`org_code`
AND a.`personid` = c.`personid`;

sql中的错误（SELECT c.name, b.diagnose, substrb ( to_char( trunc( SYSDATE, 'yy' ), 'yyyymmdd' ), 0, 4 ) - substrb ( to_char( trunc( c.birthday, 'yy' ), 'yyyymmdd' ), 0, 4 ) "年龄", c.HOME_ADDRESS FROM "wjj_yy_yldsj_mzghxx" a, "wjj_yy_yldsj_mzgzrz" b, "wjj_yy_yldsj_grjbxxb" c WHERE a.ORG_REG_ID = b.register_id AND a.reg_class = 2 AND a.org_code = b.org_code AND a.personid = c.personid）

这段 SQL 查询语句存在语法错误，具体如下：

1. 在 substrb 函数中，第一个参数应该是要截取的字符串，而不是日期类型的值。因此，在 substrb 函数中应该使用 to_char 函数将日期类型的值转换为字符串类型的值，例如：

substrb( to_char( trunc( SYSDATE, 'yy' ), 'yyyymmdd' ), 1, 4 )

2. 在 substrb 函数中，第二个参数应该表示截取的起始位置，而不是要截取的长度。因此，在此处应该使用 1，而不是 0，例如：

substrb( to_char( trunc( SYSDATE, 'yy' ), 'yyyymmdd' ), 1, 4 )

3. 在 WHERE 子句中，多个条件之间应该使用逻辑运算符连接，例如使用 AND 连接，而不是使用逗号连接。

修改后的 SQL 查询语句如下：

SELECT 
	c.name, 
	b.diagnose, 
	substrb( to_char( trunc( SYSDATE, 'yy' ), 'yyyymmdd' ), 1, 4 ) - substrb( to_char( trunc( c.birthday, 'yy' ), 'yyyymmdd' ), 1, 4 ) "年龄", 
	c.HOME_ADDRESS  
FROM 
	"wjj_yy_yldsj_mzghxx" a, 
	"wjj_yy_yldsj_mzgzrz" b, 
	"wjj_yy_yldsj_grjbxxb" c 
WHERE 
	a.ORG_REG_ID = b.register_id  
	AND a.reg_class = 2  
	AND a.org_code = b.org_code  
	AND a.personid = c.personid;