有以下3个table
Book(Book_no,Title,Subject,Author,Publisher,ISBN)
Customer(User_id,Name,Date_of_birth)
Loan(User_id,Book_no,Date_due,Date_ret,Fine,Paid)题目要求:
set fines, for loans that are late and where no fine is currently set, by calculating the fine to be 20% of the elapsed time (if no return date, use the current date from the system)我刚学MySQL,写了以下代码,我就知道肯定是错的....
UPDATE Loan
(SET Fine=(Date_ret-Date_due)*0.20
WHERE Fine is NULL AND Date_ret>Date_due)
AND
(SET Fine=(SYSDATE()-Date_due)*0.20
WHERE Fine is NULL AND Date_ret is NULL);
这种类似if-else的嵌套应该怎么写呢???
各位前辈教教我啊!!!!!!
Book(Book_no,Title,Subject,Author,Publisher,ISBN)
Customer(User_id,Name,Date_of_birth)
Loan(User_id,Book_no,Date_due,Date_ret,Fine,Paid)题目要求:
set fines, for loans that are late and where no fine is currently set, by calculating the fine to be 20% of the elapsed time (if no return date, use the current date from the system)我刚学MySQL,写了以下代码,我就知道肯定是错的....
UPDATE Loan
(SET Fine=(Date_ret-Date_due)*0.20
WHERE Fine is NULL AND Date_ret>Date_due)
AND
(SET Fine=(SYSDATE()-Date_due)*0.20
WHERE Fine is NULL AND Date_ret is NULL);
这种类似if-else的嵌套应该怎么写呢???
各位前辈教教我啊!!!!!!
解决方案 »
- 怎么设计站内消息?
- MYSQL 整站搬迁,可否直接复制 c:/mysql/data/数据表名?
- innod索引排序基础问题
- 还是字符集问题
- PgManager连接postgresql 提示: cannot load dynamic library libpq.dll
- 紧急问 Can't connect to MySQL server on '192.168.110.18' (10061)
- 怎么设置让Mysql 支持FULLTEXT 最小英文单词字母为3
- sphinx 配置出错:ERROR: index 'test1': column number 1 has no name.
- 如何使用属性数固定的若干个表存放任意多个任意结构的二维表?
- mysql 服务器无法启动
- mysql如何为下面的单表添加索引,提高查询效率
- mysqldump不锁表备份,怎么设置为好?
大家帮帮忙~~~~~~~
set Fine=Datediff(IFNULL(Date_ret,SYSDATE()),Date_due)*0.2
where IFNULL(Date_ret,SYSDATE())>Date_due
And Fine Is null
有没有简单点的形式解决这个问题啊?非常感谢!
CREATE DATABASE
STOP DATABASE
DROP DATABASE
SHOW DATABASECREATE TABLEALTER TABLE
ADDDROP TABLEINSERT INTO
VALUESSELECT
FROMDISTINCT
WHERELIKE
DETWEEN
IS NULL
IS NOT NULL
IN
AND
OR
SYSDATEMAX
MIN
AVG
SUM
COUNTORDER BY
DESCGROUP BY
HAVINGUPDATE
SETDELETE FROM基本就是这些了,非常的有限。如果有超出范围的,也不要太难。
请前辈指点啊。拜托了!
UPDATE Loan
SET Fine=(Date_ret-Date_due)*0.20
WHERE Fine is NULL AND Date_ret is NOT NULL AND Date_ret>Date_due;UPDATE Loan
SET Fine=(SYSDATE()-Date_due)*0.20
WHERE Fine is NULL AND Date_ret is NULL AND SYSDATE()>Date_due;前辈您看,直接写成这样行不行?
版主给出了语句,你不懂,老师没教,不会自己翻手册看啊?你只会A,B,C,要你打出GOOD,怎么样也不行,如果你再把G,O,D学会了,那么GOOD很容易就出来了。你这样的问问题的方式让我很无语。
哎~前辈们教训的是~~
正如您说的(N次的SB提问才能成为NB)啊~呵呵~~
我刚接触SQL两天,大家多多包涵啊~~~