结合两个表的结果
我有两张桌子
TABLE_A +-------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------------+------+-----+---------+-------+ | bid | int(10) unsigned | NO | PRI | 0 | | | uid | int(10) unsigned | NO | PRI | 0 | | +-------+------------------+------+-----+---------+-------+ 2 rows in set (0.00 sec)
和
TABLE_B +-------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------------+------+-----+---------+-------+ | bid | int(10) unsigned | NO | PRI | 0 | | | uid | int(10) unsigned | NO | PRI | 0 | | +-------+------------------+------+-----+---------+-------+
我想在uid = 123时从两个表中选择出价; 注意：每张表格都有大约15个结果，其中一些存在于两个表格中，我需要选择不同的表格。 所以我试过这个：
SELECT DISTINCT ta.bid,
tb.bid
FROM table_a AS ta
JOIN table_b AS tb using (uid)
WHERE uid = 123;
显然我得到了错误的答案。 为什么它获得了150+结果而不是30？
尝试这个
SELECT DISTINCT bid FROM TABLE_A WHERE uid = 123
UNION
SELECT DISTINCT bid FROM TABLE_B WHERE uid = 123
要么
SELECT DISTINCT bid
FROM (SELECT bid FROM TABLE_A WHERE uid = 123
UNION
SELECT bid FROM TABLE_B WHERE uid = 123
) AS A
SELECT ta.bid,
tb.bid
FROM table_a AS ta,
table_b AS tb
WHERE ta.uid = tb.uid
AND ta.uid = 123
GROUP BY ta.bid,
tb.bid
第二种方法是
SELECT ta.bid,
tb.bid
FROM table_a AS ta
INNER JOIN table_b AS tb
ON ( ta.uid = tb.uid )
AND ( ta.uid = 123 )
尝试这个
select tb1.bid, tb2.bid from TABLE_A AS tb1 , TABLE_B AS tb2
where tb1.bid = tb2.bid
AND tb1.bid = 123
group by tb1.bid