表结构：
表A：字段有：ID，FieldName，L，W
//ID 序号，FieldName 表B中的字段名，L 长度， W 宽度 ID和FieldName 组合为主键
表B：字段有：ID，AREA1，AREA2，AREA3，AREA4，AREA5 ……
//ID 其数据和表A中ID相同，AREA1 面积1，Area2 面积2 ID为主键
假设有如下数据
表A：
-------------------------------------------------------
ID | FieldName | L | W
1 | AREA1 | 10 | 10
1 | AREA2 | 11 | 11
2 | AREA1 | 8 | 11
3 | AREA3 | 10 | 9
. | . | . | .
. | . | . | .
. | . | . | .
-------------------------------------------------------
表B：
--------------------------------------------------------------------------------
ID | AREA1 | AREA2 | AREA3 | AREA4 | AREA5 |……
1 | 100 | 100 | NULL | NULL | NULL |……
2 | 100 | NULL | NULL | NULL | NULL |……
3 | NULL | NULL | 90 | NULL | NULL |……
--------------------------------------------------------------------------------
问题： 如何删除表A中 L*W 不等于 其ID和FieldName 对应表B中的数据 的记录
如：表A中 ID = 1 ，FieldName = 'AREA2' 的一条数据，其L*W =121,而表B中ID= 1的AREA2 = 100
121 <> 100 所以删除 表A中ID = 1 ，FieldName = 'AREA2' 的记录
------解决方案--------------------------------------------------------
B表设计有问题 ：
ID FieldName JE
1 AREA1 100
1 AREA2 200
1 AREA3 NULL
......
delete A FROM A INNER JOIN B ON A.ID=B.ID AND A.FieldName=B.FieldName AND A.L*A.W<>B.JE
------解决方案--------------------------------------------------------
用你的结构：
delete A FROM A INNER JOIN B ON A.ID=B.ID AND A.FieldName='AREA1' AND A.L*A.W <>B.AREA1
delete A FROM A INNER JOIN B ON A.ID=B.ID AND A.FieldName='AREA2' AND A.L*A.W <>B.AREA2
delete A FROM A INNER JOIN B ON A.ID=B.ID AND A.FieldName='AREA3' AND A.L*A.W <>B.AREA3
........
------解决方案--------------------------------------------------------
------解决方案--------------------------------------------------------
呵呵，用你的结构，删除就复杂了，自己权衡一下吧
------解决方案--------------------------------------------------------
仅用SQL语句实现如下。
- SQL code
mysql> select * from t_a;+------+-----------+------+------+| ID | FieldName | L | W |+------+-----------+------+------+| 1 | AREA1 | 10 | 10 || 1 | AREA2 | 11 | 11 || 2 | AREA1 | 8 | 11 || 3 | AREA3 | 10 | 9 |+------+-----------+------+------+4 rows in set (0.00 sec)mysql> select * from t_b;+------+-------+-------+-------+-------+-------+| ID | AREA1 | AREA2 | AREA3 | AREA4 | AREA5 |+------+-------+-------+-------+-------+-------+| 1 | 100 | 100 | NULL | NULL | NULL || 2 | 100 | NULL | NULL | NULL | NULL || 3 | NULL | NULL | 90 | NULL | NULL |+------+-------+-------+-------+-------+-------+3 rows in set (0.00 sec)mysql>mysql> select * from t_a -> where (ID,FieldName,L*W) not in ( -> select id,'AREA1' as FieldName, AREA1 from t_b where AREA1 is not null -> union all -> select id,'AREA2' as FieldName, AREA2 from t_b where AREA2 is not null -> union all -> select id,'AREA3' as FieldName, AREA3 from t_b where AREA3 is not null -> union all -> select id,'AREA4' as FieldName, AREA4 from t_b where AREA4 is not null -> union all -> select id,'AREA5' as FieldName, AREA5 from t_b where AREA5 is not null -> );+------+-----------+------+------+| ID | FieldName | L | W |+------+-----------+------+------+| 1 | AREA2 | 11 | 11 || 2 | AREA1 | 8 | 11 |+------+-----------+------+------+2 rows in set (0.00 sec)mysql> delete from t_a -> where (ID,FieldName,L*W) not in ( -> select id,'AREA1' as FieldName, AREA1 from t_b where AREA1 is not null -> union all -> select id,'AREA2' as FieldName, AREA2 from t_b where AREA2 is not null -> union all -> select id,'AREA3' as FieldName, AREA3 from t_b where AREA3 is not null -> union all -> select id,'AREA4' as FieldName, AREA4 from t_b where AREA4 is not null -> union all -> select id,'AREA5' as FieldName, AREA5 from t_b where AREA5 is not null -> );Query OK, 2 rows affected (0.05 sec)mysql> select * from t_a;+------+-----------+------+------+| ID | FieldName | L | W |+------+-----------+------+------+| 1 | AREA1 | 10 | 10 || 3 | AREA3 | 10 | 9 |+------+-----------+------+------+2 rows in set (0.00 sec)mysql>