我正在尝试选择不在另一个表中的行。在这里我使用了left join with is null,但没有得到预期的结果。它只对一个表有效,但对另一个表无效。
主要是我不能管理四个表之间的关系。 fee
-包括费用类别)和 fee_tm
-包括支付费用的月份列表), cls_fee
-各等级的收费标准)和 invoice
-包含有关已付费用的信息)。
因此,我试图显示学生名单谁还没有支付,或那些学生的记录,这是不在 invoice
table。
mysql数据库
SELECT
fee_tm.id AS ftm_d,
fee.id AS f_id,
fee_tm.en_ttl AS f_tm,
fee.en_ttl AS fee,
cls_fee.fee AS f_mnt
FROM
fee
LEFT JOIN
fee_tm ON fee_tm.year = fee.year
LEFT JOIN
cls_fee ON cls_fee.fee_id = fee.id
LEFT JOIN
student ON student.cls = cls_fee.c_id AND student.sec = cls_fee.s_id
LEFT JOIN
invoice ON invoice.stu_id = student.id AND invoice.fee_id = fee.id AND invoice.ftm_id = fee_tm.id
WHERE
student.id =1 AND invoice.ftm_id is NULL AND invoice.fee_id is NULL
当前结果
ftm_d | f_id | f_tm | fee | f_mnt
=====================================================
2 | 1 | Feb | Annual | 1000
2 | 2 | Feb | Monthly | 560
预期结果
ftm_d | f_id | f_tm | fee | f_mnt
=====================================================
2 | 2 | Feb | Monthly | 560
我的结果只是检查 f_tm
列,所以在我当前的结果月份 Jan
已过滤,但必须检查 fee
列和 Annual
必须筛选行。
所以,我们可以知道那些没有交学费的学生。如果在中找到记录 invoice
表中,则应在结果中筛选此费用类别。
数据库结构
学生
id | en_ttl | cls | sec | year
========================================
1 | John | 1 | 1 | 1
cls公司
id | en_ttl | year
========================
1 | One | 1
秒
id | en_ttl | year
========================
1 | A | 1
费用
id | en_ttl | year
========================
1 | Annual | 1
2 | Monthly | 1
3 | Library | 1
费用
id | en_ttl | year
========================
1 | Jan | 1
2 | Feb | 1
cls\U费用
id | c_id | s_id | fee_id | fee
===============================================
1 | 1 | 1 | 1 | 1000
2 | 1 | 1 | 2 | 560
发票
id | stu_id | fee_id | ftm_id
======================================
1 | 1 | 1 | 1
表的ddl语句
CREATE TABLE `student` (
`id` int(4) NOT NULL AUTO_INCREMENT,
`en_ttl` varchar(100) NOT NULL,
`cls` int(2) NOT NULL,
`sec` int(2) NOT NULL,
`year` int(1) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
CREATE TABLE `cls` (
`id` int(2) NOT NULL AUTO_INCREMENT,
`en_ttl` varchar(50) NOT NULL,
`year` int(2) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
CREATE TABLE `sec` (
`id` int(2) NOT NULL AUTO_INCREMENT,
`en_ttl` varchar(50) NOT NULL,
`year` int(2) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
CREATE TABLE `fee` (
`id` int(2) NOT NULL AUTO_INCREMENT,
`en_ttl` varchar(50) NOT NULL,
`year` int(2) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
CREATE TABLE `fee_tm` (
`id` int(2) NOT NULL AUTO_INCREMENT,
`en_ttl` varchar(50) NOT NULL,
`year` int(2) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
CREATE TABLE `cls_fee` (
`id` int(2) NOT NULL AUTO_INCREMENT,
`c_id` int(2) NOT NULL,
`s_id` int(2) NOT NULL,
`fee_id` int(2) NOT NULL,
`fee` int(6) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `fee` (`c_id`,`s_id`,`fee_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;
CREATE TABLE `invoice` (
`id` int(4) NOT NULL AUTO_INCREMENT,
`stu_id` int(4) NOT NULL,
`fee_id` int(11) NOT NULL,
`ftm_id` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
1条答案
按热度按时间lmyy7pcs1#
在cluase中使用studentid=1