这里给出我的sql和 部分数据 select a,b,
-> max(if(c=1,d,null)) as d11,
-> max(if(c=2,d,null)) as d21
-> from tx
-> group by a,b;
+------+------+-----------+----------+
| a | b | d11 | d21 |
+------+------+-----------+----------+
| 1 | 45 | china | American |
| 2 | 99 | German | German |
| 3 | 75 | Austialia | USA |
| 4 | 29 | Singapore | Korean |
+------+------+-----------+----------+
4 rows in set (0.02 sec)这里目前产生一个问题,d11 d21 是不能相等的(2个分别代表主 客场,不能表示同一个国家)
如果产生这样情况(如上面a=2 99 German German )
sql应该返回错误或者发邮件 指出这行记录的错误(应该给出a=2)。
这个能够在sql提出吗?
-> max(if(c=1,d,null)) as d11,
-> max(if(c=2,d,null)) as d21
-> from tx
-> group by a,b;
+------+------+-----------+----------+
| a | b | d11 | d21 |
+------+------+-----------+----------+
| 1 | 45 | china | American |
| 2 | 99 | German | German |
| 3 | 75 | Austialia | USA |
| 4 | 29 | Singapore | Korean |
+------+------+-----------+----------+
4 rows in set (0.02 sec)这里目前产生一个问题,d11 d21 是不能相等的(2个分别代表主 客场,不能表示同一个国家)
如果产生这样情况(如上面a=2 99 German German )
sql应该返回错误或者发邮件 指出这行记录的错误(应该给出a=2)。
这个能够在sql提出吗?
-> max(if(c=1,d,null)) as d11,
-> max(if(c=2,d,null)) as d21 ,max(if(d11=d12,'error',''))
-> from tx
-> group by a,b;应该在执行之前清理一下数据
执行通不过 提示表里没有这个字段
d11,d12 系统提示表没有这个
(
select a,b,
max(if(c=1,d,null)) as d11,
max(if(c=2,d,null)) as d21
from tx
group by a,b
) t
where d11=d21