数据结构:USER_ID 语文 数学 年度1 1 0 20101 0 1 20112 0 1 20102 1 0 20113 1 1 20114 0 1 20104 0 1 20115 1 0 20105 1 0 2011只想要 1,2,3这3种情况写了2个SQL,执行成本都3000+,有什么好办法?
SELECT j.USER_ID FROM
(select USER_ID,语文,数学,年度 FROM 成绩 WHERE YEAR_ = 2011) j left join
(select USER_ID,语文,数学,年度 FROM 成绩 WHERE YEAR_ = 2010) q on q.USER_ID=j.USER_ID
WHERE (q.语文=1 and j.数学=1) or (q.数学=1 and j.语文=1) or (j.语文=1 and j.数学=1)这样的结果
SELECT j.USER_ID FROM
(select USER_ID,语文,数学,年度 FROM 成绩 WHERE YEAR_ = 2011) j left join
(select USER_ID,语文,数学,年度 FROM 成绩 WHERE YEAR_ = 2010) q on q.USER_ID=j.USER_ID
WHERE (q.语文=1 and q.数学=1) or (q.数学=1 and j.语文=1) or (j.语文=1 and j.数学=1)
or (q.语文=1 and j.数学=1)
from table_name t
group by t.user_id
having sum(t.语文) >= 1 and sum(t.数学) >= 1
select * from table_name
where user_id in
(select user_id from table_name group by user_id having sum(语文)+sum(数学)=2)
如果加上你刚说的两年是1,1和两年相同的不考虑,那么应该如下
select * from table_name
where user_id in
(select user_id from table_name group by user_id having sum(语文)=1 and sum(数学)=1)