这是错误信息:
#42000The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay我是在查询:string cmdStr = "SELECT * FROM view_import_log_new " + operateTimeClause + idClause + operatorClause + " ORDER BY Log_Sn DESC"; MySqlCommand cmd = new MySqlCommand(cmdStr); if (operatorname != "") cmd.Parameters.Add("?operator", "%" + operatorname + "%"); DataSet dsImportLog = GetTable(cmd, "ImportLog");建的一个视图view_import_log_new,视图代码如下:SELECT
`i`.`Log_Sn` AS `Log_Sn`,
`i`.`File_ID` AS `File_ID`,
`i`.`Inpatient_ID` AS `Inpatient_ID`,
`i`.`U_ID` AS `U_ID`,
`i`.`MR_ID` AS `MR_ID`,
`i`.`Operator` AS `Operator_ID`,
(
CASE
WHEN isnull(`o`.`Operator_Name`)THEN
`i`.`Operator`
ELSE
`o`.`Operator_Name`
END
)AS `Operator_Name`,
`i`.`Operate_Time` AS `Operate_Time`,
`i`.`Action` AS `Action`,
`i`.`insertflag` AS `insertflag`,
`h`.`Pack_ID` AS `Pack_ID`,
`h`.`Barcode_ID` AS `Barcode_ID`,
`h`.`Block_ID` AS `Block_ID`,
`h`.`HR_ID` AS `HR_ID`,
`h`.`HR_Count` AS `HR_Count`,
`h`.`Hospitalize_ID` AS `Hospitalize_ID`,
`h`.`Hospitalize_Count` AS `Hospitalize_Count`,
(
CASE
WHEN(`h`.`Hospitalize_Count` = 0)THEN
_utf8 '-'
WHEN(
`h`.`Hospitalize_ID` = _utf8 ''
)THEN
_utf8 '-'
ELSE
`h`.`Hospitalize_Count`
END
)AS `Hospitalize_Count_Text`,
`h`.`Patient_ID` AS `Patient_ID`,
`h`.`Patient_Name` AS `Patient_Name`,
`h`.`Patient_Gender` AS `Patient_Gender`,
`h`.`In_Date` AS `In_Date`,
`h`.`In_Dept` AS `In_Dept_Code`,
(
CASE
WHEN isnull(`s1`.`Name`)THEN
`h`.`In_Dept`
ELSE
`s1`.`Name`
END
)AS `In_Dept_Name`,
`h`.`Out_Date` AS `Out_Date`,
`h`.`Out_Dept` AS `Out_Dept_Code`,
(
CASE
WHEN isnull(`s2`.`Name`)THEN
`h`.`Out_Dept`
ELSE
`s2`.`Name`
END
)AS `Out_Dept_Name`,
`h`.`Out_Status` AS `Out_Status`,
`h`.`Is_Dead` AS `Is_Dead`,
`h`.`Create_Time` AS `Create_Time`,
`h`.`Imp_Feature` AS `Imp_Feature`,
(
CASE
WHEN(`h`.`Is_Proof` = 0)THEN
_utf8 '0'
ELSE
_utf8 '1'
END
)AS `Is_Proof`,
(
CASE
WHEN(`h`.`Is_Code` = 0)THEN
_utf8 '0'
ELSE
_utf8 '1'
END
)AS `Is_Code`,
(
CASE
WHEN(`h`.`Is_QC` = 0)THEN
_utf8 '0'
ELSE
_utf8 '1'
END
)AS `Is_QC`,
(
CASE
WHEN(`h`.`Is_Burn` = 0)THEN
_utf8 '0'
ELSE
_utf8 '1'
END
)AS `Is_Burn`,
(
CASE
WHEN(`h`.`Is_Pack` = 0)THEN
_utf8 '0'
ELSE
_utf8 '1'
END
)AS `Is_Pack`,
(
CASE
WHEN(`h`.`Is_Seal` = 0)THEN
_utf8 '0'
ELSE
_utf8 '1'
END
)AS `Is_Seal`,
`h`.`Physical_Status` AS `Physical_Status`,
`h`.`Virtual_Status` AS `Virtual_Status`,
`h`.`Recover_Feature` AS `Recover_Feature_Code`,
(
CASE
WHEN isnull(`s3`.`Name`)THEN
`h`.`Recover_Feature`
ELSE
`s3`.`Name`
END
)AS `Recover_Feature_Name`,
(
CASE
WHEN(`h`.`Is_Valid` = 0)THEN
_utf8 '0'
ELSE
_utf8 '1'
END
)AS `Is_Valid`,
`h`.`source` AS `source`
FROM
(
(
(
(
(
`import_log` `i`
LEFT JOIN `operator` `o` ON(
(`i`.`Operator` = `o`.`RFID`)
)
)
LEFT JOIN `health_record` `h` ON((`i`.`U_ID` = `h`.`MR_ID`))
)
LEFT JOIN `sys_dict` `s1` ON(
(
(`h`.`In_Dept` = `s1`.`Code`)
AND(`s1`.`Type` = _utf8 '科室')
)
)
)
LEFT JOIN `sys_dict` `s2` ON(
(
(`h`.`Out_Dept` = `s2`.`Code`)
AND(`s2`.`Type` = _utf8 '科室')
)
)
)
LEFT JOIN `sys_dict` `s3` ON(
(
(
`h`.`Recover_Feature` = `s3`.`Code`
)
AND(
`s3`.`Type` = _utf8 '回收特征'
)
)
)
)
#42000The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay我是在查询:string cmdStr = "SELECT * FROM view_import_log_new " + operateTimeClause + idClause + operatorClause + " ORDER BY Log_Sn DESC"; MySqlCommand cmd = new MySqlCommand(cmdStr); if (operatorname != "") cmd.Parameters.Add("?operator", "%" + operatorname + "%"); DataSet dsImportLog = GetTable(cmd, "ImportLog");建的一个视图view_import_log_new,视图代码如下:SELECT
`i`.`Log_Sn` AS `Log_Sn`,
`i`.`File_ID` AS `File_ID`,
`i`.`Inpatient_ID` AS `Inpatient_ID`,
`i`.`U_ID` AS `U_ID`,
`i`.`MR_ID` AS `MR_ID`,
`i`.`Operator` AS `Operator_ID`,
(
CASE
WHEN isnull(`o`.`Operator_Name`)THEN
`i`.`Operator`
ELSE
`o`.`Operator_Name`
END
)AS `Operator_Name`,
`i`.`Operate_Time` AS `Operate_Time`,
`i`.`Action` AS `Action`,
`i`.`insertflag` AS `insertflag`,
`h`.`Pack_ID` AS `Pack_ID`,
`h`.`Barcode_ID` AS `Barcode_ID`,
`h`.`Block_ID` AS `Block_ID`,
`h`.`HR_ID` AS `HR_ID`,
`h`.`HR_Count` AS `HR_Count`,
`h`.`Hospitalize_ID` AS `Hospitalize_ID`,
`h`.`Hospitalize_Count` AS `Hospitalize_Count`,
(
CASE
WHEN(`h`.`Hospitalize_Count` = 0)THEN
_utf8 '-'
WHEN(
`h`.`Hospitalize_ID` = _utf8 ''
)THEN
_utf8 '-'
ELSE
`h`.`Hospitalize_Count`
END
)AS `Hospitalize_Count_Text`,
`h`.`Patient_ID` AS `Patient_ID`,
`h`.`Patient_Name` AS `Patient_Name`,
`h`.`Patient_Gender` AS `Patient_Gender`,
`h`.`In_Date` AS `In_Date`,
`h`.`In_Dept` AS `In_Dept_Code`,
(
CASE
WHEN isnull(`s1`.`Name`)THEN
`h`.`In_Dept`
ELSE
`s1`.`Name`
END
)AS `In_Dept_Name`,
`h`.`Out_Date` AS `Out_Date`,
`h`.`Out_Dept` AS `Out_Dept_Code`,
(
CASE
WHEN isnull(`s2`.`Name`)THEN
`h`.`Out_Dept`
ELSE
`s2`.`Name`
END
)AS `Out_Dept_Name`,
`h`.`Out_Status` AS `Out_Status`,
`h`.`Is_Dead` AS `Is_Dead`,
`h`.`Create_Time` AS `Create_Time`,
`h`.`Imp_Feature` AS `Imp_Feature`,
(
CASE
WHEN(`h`.`Is_Proof` = 0)THEN
_utf8 '0'
ELSE
_utf8 '1'
END
)AS `Is_Proof`,
(
CASE
WHEN(`h`.`Is_Code` = 0)THEN
_utf8 '0'
ELSE
_utf8 '1'
END
)AS `Is_Code`,
(
CASE
WHEN(`h`.`Is_QC` = 0)THEN
_utf8 '0'
ELSE
_utf8 '1'
END
)AS `Is_QC`,
(
CASE
WHEN(`h`.`Is_Burn` = 0)THEN
_utf8 '0'
ELSE
_utf8 '1'
END
)AS `Is_Burn`,
(
CASE
WHEN(`h`.`Is_Pack` = 0)THEN
_utf8 '0'
ELSE
_utf8 '1'
END
)AS `Is_Pack`,
(
CASE
WHEN(`h`.`Is_Seal` = 0)THEN
_utf8 '0'
ELSE
_utf8 '1'
END
)AS `Is_Seal`,
`h`.`Physical_Status` AS `Physical_Status`,
`h`.`Virtual_Status` AS `Virtual_Status`,
`h`.`Recover_Feature` AS `Recover_Feature_Code`,
(
CASE
WHEN isnull(`s3`.`Name`)THEN
`h`.`Recover_Feature`
ELSE
`s3`.`Name`
END
)AS `Recover_Feature_Name`,
(
CASE
WHEN(`h`.`Is_Valid` = 0)THEN
_utf8 '0'
ELSE
_utf8 '1'
END
)AS `Is_Valid`,
`h`.`source` AS `source`
FROM
(
(
(
(
(
`import_log` `i`
LEFT JOIN `operator` `o` ON(
(`i`.`Operator` = `o`.`RFID`)
)
)
LEFT JOIN `health_record` `h` ON((`i`.`U_ID` = `h`.`MR_ID`))
)
LEFT JOIN `sys_dict` `s1` ON(
(
(`h`.`In_Dept` = `s1`.`Code`)
AND(`s1`.`Type` = _utf8 '科室')
)
)
)
LEFT JOIN `sys_dict` `s2` ON(
(
(`h`.`Out_Dept` = `s2`.`Code`)
AND(`s2`.`Type` = _utf8 '科室')
)
)
)
LEFT JOIN `sys_dict` `s3` ON(
(
(
`h`.`Recover_Feature` = `s3`.`Code`
)
AND(
`s3`.`Type` = _utf8 '回收特征'
)
)
)
)
解决方案 »
- 服务器上的图片在本地程序显示的问题
- 在my.ini设置sql_mode=STRICT_TRANS_TABLES,在sqlyog为什么显示mode是空的
- 在线等,一个字符集的问题
- 插入数据 DELAYED INTO ON DUPLICATE KEY UPDATE
- 关于禁止atime对数据库性能提高的问题
- 刚装好mysql,发现不能像sql2000那样方便的建表,而是要用create语句,是这样的吗
- Mysql自动编号问题
- 这样逻辑的应用,用MYSQL的select要怎么写呢?里面有没有提供这样的函数呢?(没那么多分了,各位帮帮忙吧!)
- AUTO_INCREMENT 会不会超过最大值啊?
- 麻烦大佬给看下这两句sql能不能合并查询呢
- 真诚求教:往表随机插入一个"集合"中的某个字符串+MySQL
- 怎么备份都失败。求解决办法
这个我加在my.ini里了,还是同样的报错,还有什么办法吗?
建议优化查询减少连接
OR
生成临时表,来减少连接
MySQL重启过好几次了,那针对我这个语句怎样优化呢?还望指教啊