以下是我写的语句: SELECT wage_infor.employee_ID, wage_infor.employee_Name,employee_infor.sex, wage_infor.Basic_Wage, wage_infor.Allowance, wage_infor.OverTime_Wage, wage_infor.Bounty , wage_infor.deduct, wage_infor.Total_Wage, wage_infor.beizhu FROM wage_infor INNER JOIN employee_infor ON wage_infor.employee_ID = employee_infor.employee_ID where Total_Wage in (select (Basic_Wage+Allowance+OverTime_Wage+Bounty-deduct) from wage_infor ) wage_infor表中有Total_Wage值为空的记录,结果集中把这条记录过滤掉了没有显示出来,但是在上面的语句后加上查询条件时还是可以显示: SELECT wage_infor.employee_ID, wage_infor.employee_Name,employee_infor.sex, wage_infor.Basic_Wage, wage_infor.Allowance, wage_infor.OverTime_Wage, wage_infor.Bounty , wage_infor.deduct, wage_infor.Total_Wage, wage_infor.beizhu FROM wage_infor INNER JOIN employee_infor ON wage_infor.employee_ID = employee_infor.employee_ID where Total_Wage in (select (Basic_Wage+Allowance+OverTime_Wage+Bounty-deduct) from wage_infor ) and wage_infor.employee_ID=1 or wage_infor.employee_Name=111 其中employee_ID=1及employee_Name=111是wage_infor表中有Total_Wage值为空所在记录的员工编号及员工姓名字段
不知道,MySQL中有没有isnull()函数。
SELECT wage_infor.employee_ID, wage_infor.employee_Name,employee_infor.sex, wage_infor.Basic_Wage, wage_infor.Allowance, wage_infor.OverTime_Wage, wage_infor.Bounty , wage_infor.deduct, wage_infor.Total_Wage, wage_infor.beizhu FROM wage_infor INNER JOIN employee_infor ON wage_infor.employee_ID = employee_infor.employee_ID where Total_Wage in (select (Basic_Wage+Allowance+OverTime_Wage+Bounty-deduct) from wage_infor ) and (wage_infor.employee_ID=1 or wage_infor.employee_Name=111)注意这对括号(),否则所有wage_infor.employee_Name=111 都会符合要求。当您的问题得到解答后请及时结贴. http://topic.csdn.net/u/20090501/15/7548d251-aec2-4975-a9bf-ca09a5551ba5.html
空与NULL是不同的,可以用IS NULL来判断 and (wage_infor.employee_ID=1 or wage_infor.employee_Name=111)你的SQL语句可以优化
把你的SQL语句贴上来看看
SELECT wage_infor.employee_ID,
wage_infor.employee_Name,employee_infor.sex, wage_infor.Basic_Wage,
wage_infor.Allowance, wage_infor.OverTime_Wage,
wage_infor.Bounty , wage_infor.deduct,
wage_infor.Total_Wage,
wage_infor.beizhu FROM wage_infor INNER JOIN employee_infor ON
wage_infor.employee_ID = employee_infor.employee_ID where Total_Wage in
(select (Basic_Wage+Allowance+OverTime_Wage+Bounty-deduct) from wage_infor )
wage_infor表中有Total_Wage值为空的记录,结果集中把这条记录过滤掉了没有显示出来,但是在上面的语句后加上查询条件时还是可以显示:
SELECT wage_infor.employee_ID,
wage_infor.employee_Name,employee_infor.sex, wage_infor.Basic_Wage,
wage_infor.Allowance, wage_infor.OverTime_Wage,
wage_infor.Bounty , wage_infor.deduct,
wage_infor.Total_Wage,
wage_infor.beizhu FROM wage_infor INNER JOIN employee_infor ON
wage_infor.employee_ID = employee_infor.employee_ID where Total_Wage in
(select (Basic_Wage+Allowance+OverTime_Wage+Bounty-deduct) from wage_infor )
and wage_infor.employee_ID=1 or wage_infor.employee_Name=111
其中employee_ID=1及employee_Name=111是wage_infor表中有Total_Wage值为空所在记录的员工编号及员工姓名字段
wage_infor.employee_Name,employee_infor.sex, wage_infor.Basic_Wage,
wage_infor.Allowance, wage_infor.OverTime_Wage,
wage_infor.Bounty , wage_infor.deduct,
wage_infor.Total_Wage,
wage_infor.beizhu
FROM wage_infor INNER JOIN employee_infor ON wage_infor.employee_ID = employee_infor.employee_ID
where Total_Wage in
(select (Basic_Wage+Allowance+OverTime_Wage+Bounty-deduct) from wage_infor )
and (wage_infor.employee_ID=1 or wage_infor.employee_Name=111)注意这对括号(),否则所有wage_infor.employee_Name=111 都会符合要求。当您的问题得到解答后请及时结贴.
http://topic.csdn.net/u/20090501/15/7548d251-aec2-4975-a9bf-ca09a5551ba5.html
and (wage_infor.employee_ID=1 or wage_infor.employee_Name=111)你的SQL语句可以优化