如何避免结果集中的重复值？

Question

创建表student(s_idint(10)不为空AUTO_INCREMENT,s_roll_noint(30),s_namevarchar(30),s_gendervarchar(4)不为空,classint(2)不为空,主键(s_id))；

创建表attendance_date(date_todayvarchar(10)，主键(date_today))；

创建表attendance_today(s_idint(10),s_roll_noint(30),s_namevarchar(30),s_gendervarchar(4),classint(2), varchar(10), attendance_statusvarchar(2)not null默认'p')；

分隔符$$

为每行在attendance_date上插入后创建触发器after_inserthtml" target="_blank">ion_into_attendance_date

begin 

insert into attendance_today(s_id, s_roll_no, s_name, s_gender, class, date_today)
select * from student cross join attendance_date order by date_today, s_id;

end$$

定界符；

在student中插入值（1,1,“Mridul Kumar”,“M”,1）,（2,2,“Harish Paul”,“M”,1）,（3,3,“Imtiaz Hossain”,“M”,1）；

插入attendance_date值('1日')、('2日')；

现在，选择*from attendance_today；每次插入后都给出重复，有没有办法在触发器内部避免重复？我不是在寻找select distinct*from attendance_today；在触发器被激活后。

Answer 1

这将防止插入重复的值

insert into attendance_today(s_id, s_roll_no, s_name, s_gender, class, date_today)
select * 
from student cross join attendance_date ad
WHERE NOT EXISTS ( 
    SELECT null FROM attendance_today at
    WHERE ad.s_id = at.s_id and ad.date_today = at.date_today
)
order by date_today, s_id;

```

Answer 2

试试看：

begin 

insert into attendance_today(s_id, s_roll_no, s_name, s_gender, class, date_today)
select * from student cross join attendance_date group by s_id order by date_today, s_id;

end$$