表t1
+----+----------+--------------+
| id | goods_id | goods_number |
+----+----------+--------------+
| 1 | 1 | 1 |
| 2 | 2 | 1 |
+----+----------+--------------+
表t2
+----+----------+--------------+
| id | goods_id | goods_number |
+----+----------+--------------+
| 1 | 2 | 1 |
| 2 | 2 | 1 |
+----+----------+--------------+
查询 t1 和t2 表中 相同goods_id 的 各表 商品 总数。
[如果不能给出sql 能把这条改的简单点也可以“
select goods_id,sum(ff) ,sum(ll) from ( select goods_id,goods_number as ff,0 as ll,1 as k1,0 as k2 from t1 union all select goods_id,0,goods_number as ll, 0 as k1,1 as k2 from t2 ) a group by goods_id having sum(k1)>0 and sum(k2)>0”]mysql
+----+----------+--------------+
| id | goods_id | goods_number |
+----+----------+--------------+
| 1 | 1 | 1 |
| 2 | 2 | 1 |
+----+----------+--------------+
表t2
+----+----------+--------------+
| id | goods_id | goods_number |
+----+----------+--------------+
| 1 | 2 | 1 |
| 2 | 2 | 1 |
+----+----------+--------------+
查询 t1 和t2 表中 相同goods_id 的 各表 商品 总数。
[如果不能给出sql 能把这条改的简单点也可以“
select goods_id,sum(ff) ,sum(ll) from ( select goods_id,goods_number as ff,0 as ll,1 as k1,0 as k2 from t1 union all select goods_id,0,goods_number as ll, 0 as k1,1 as k2 from t2 ) a group by goods_id having sum(k1)>0 and sum(k2)>0”]mysql
, sum(if(bz=2, goods_number,0))
from (
select 1 as bz,* from t1
union all
select 2 as bz,* from t2) a
group by goods_id
sum(goods_number)
from (
select 1 as bz,* from ta
union all
select 2 as bz,* from tb) a
group by goods_id,bz
s ff,0 as ll,1 as k1,0 as k2 from t1 union all select goods_id,0,goods_number
as ll, 0 as k1,1 as k2 from t2 ) a group by goods_id having sum(k1)>0 and sum(
k2)>0;
+----------+---------+---------+
| goods_id | sum(ff) | sum(ll) |
+----------+---------+---------+
| 2 | 1 | 2 |
+----------+---------+---------+
1 row in set (0.00 sec)
select goods_id, sum(iif(bz=1, goods_number,0))
, sum(iif(bz=2, goods_number,0))
from (
select 1 as bz,* from ta
union all
select 2 as bz,* from tb) a
group by goods_id
having sum(iif(bz=1, goods_number,0))>0 and sum(iif(bz=2, goods_number,0))>0
tb WHERE a.goods_id=goods_id) AS a2 FROM ta a WHERE EXISTS(SELECT 1 FROM tb WHE
RE a.goods_id=goods_id)
-> GROUP BY a.goods_id;
+----------+------+------+
| goods_id | a1 | a2 |
+----------+------+------+
| 2 | 1 | 2 |
+----------+------+------+
1 row in set (0.00 sec)mysql>
umber,0)) AS A2
-> FROM (SELECT *,1 AS bz FROM ta UNION ALL SELECT *,2 AS bz FROM tb) a
GROUP BY goods_id
-> HAVING SUM(IF(bz=1, goods_number,0))>0 AND SUM(IF(bz=2, goods_number,0
))>0;
+----------+------+------+
| goods_id | A1 | A2 |
+----------+------+------+
| 2 | 1 | 2 |
+----------+------+------+
1 row in set (0.00 sec)mysql>
t2 WHERE a.goods_id=goods_id) AS a2 FROM t1 a WHERE EXISTS(SELECT 1 FROM t2 WHE
RE a.goods_id=goods_id) GROUP BY a.goods_id
怎么还抱 这个错#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'RE a.goods_id=goods_id) GROUP BY a.goods_id LIMIT 0, 30' at line 3
哪里不对 求指正 求讲解
FROM t2 WHERE a.goods_id=goods_id) AS a2 FROM t1 a
WHERE EXISTS(SELECT 1 FROM t2 WHERE a.goods_id=goods_id) GROUP BY a.goods_id