Table A:
ID Name banji_id//banji_id is the ID of table B
1 Zhang Long 1
2 Zhang Long 2
3 Li 41
3 Li 45
5 Wang 53
6 Zhao Hu 2
7 Zhao Hu 1
8 Zhaohu 4
Table B:
ID km SJ1 SJ2
1 Abacus Mental Arithmetic 2015 spring
2 Eloquence 2015 Spring
3 Composition 2015 Spring
4 Mathematics 2015 Spring
5 English 2014 Autumn
Zhang Long was in the spring of 2015 learned the Abacus mental arithmetic and eloquence;
Zhaohu was in the spring of 2015 to learn abacus and eloquence and mathematics;
Li Four is in the fall of 2014 to learn English, 2015 spring to learn the abacus mental arithmetic;
I want to do a button, click on the search out Zhang Long, Zhaohu (that is, in the same period of multi-branch students);
While John Doe is a different subject, but John Doe because it is different semester, so not shown in the column
Reply to discussion (solution)
Select name from (select A.*, b.km, B.sj1, B.SJ2, COUNT (*) as cou from a join B in a.banji_id = b.ID Group by name, Sj1, S J2) as C where Cou >= 2;
I don't know if there's any easier way.
GROUP by Xy.name,bj.shijian_1,bj.shijian_2 have COUNT (*) >1
GROUP by Xy.name,bj.kemu_1 have COUNT (*) <=1
How to use these two sentences together??
Select a.ID, name from a, b where A.banji_id=b.id GROUP by NAME,SJ1,SJ2 have count (*) > 1
Select a.ID, name from a, b where A.banji_id=b.id GROUP by NAME,SJ1,SJ2 have count (*) > 1
That's fine with that, but there are two more questions:
1. Displayed incorrectly when paging,
function Get_xueyuan_count ($duoke .... Omitted
{
Global $fdyu, $db;
...... Omitted
if ($duoke!=0)
{
$sql _where. = "and xy.cur_banji_id=bj.banji_id Group by Xy.name,bj.shijian_1,bj.shijian_2 have count (*) > 1";
}
$sql = "Select COUNT (Distinct xy.xy_id) from". $fdyu the->table (' Oa_xueyuan '). "As XY left join
". $xfsql.
$fdyu->table (' Oa_banji '). "As BJ on bj.banji_id=xy.cur_banji_id left join".
$fdyu->table (' Oa_banji '). "As Bj_1 on bj_1.banji_id=xy.pre_banji_id
". $xiashu.
$sql _where;
$count = $db->getone ($sql);
return $count;
}
function Get_xueyuan_list ($duoke .... Omitted
{
Global $fdyu, $db;
...... Omitted
if ($duoke!=0)
{
$sql _where. = "and xy.cur_banji_id=bj.banji_id Group by Xy.name,bj.shijian_1,bj.shijian_2 have count (*) > 1";
}
$sql = "Select COUNT (Distinct xy.xy_id) from". $fdyu the->table (' Oa_xueyuan '). "As XY left join
". $xfsql.
$fdyu->table (' Oa_banji '). "As BJ on bj.banji_id=xy.cur_banji_id left join".
$fdyu->table (' Oa_banji '). "As Bj_1 on bj_1.banji_id=xy.pre_banji_id
". $xiashu.
$sql _where;
$res = $db->selectlimit ($sql, $size, ($page-1) * $size);
$arr = Array ();
if ($res)
{
while ($row = $db->fetchrow ($res))
{
...... Omitted
}
}
}
2. I would like to show the list of xy.cur_banji_id=bj.banji_id, this sentence and the group by Xy.name,bj.shijian_1,bj.shijian_2 have count (*) > 1 How should I change it?
Long Zhang
Long Zhang
Zhaohu
Zhaohu
Zhaohu