如果只是不同数据库,则你可以在表中前加上数据库名即可。select * from user.register u inner join product.sell s using(uid) where not exists (select 1 from sell where uid=s.uid and price>s.price);这个看不懂你想实现得到底是什么。 (不要高估你的汉语表达能力或者我的汉语理解能力) 建议你列出你的表结构,并提供测试数据以及基于这些测试数据的所对应正确结果。 参考一下这个贴子的提问方式http://topic.csdn.net/u/20091130/20/8343ee6a-417c-4c2d-9415-fa46604a00cf.html
from user.register u inner join product.sell s using(uid)
where not exists (select 1 from sell where uid=s.uid and price>s.price);这个看不懂你想实现得到底是什么。 (不要高估你的汉语表达能力或者我的汉语理解能力)
建议你列出你的表结构,并提供测试数据以及基于这些测试数据的所对应正确结果。
参考一下这个贴子的提问方式http://topic.csdn.net/u/20091130/20/8343ee6a-417c-4c2d-9415-fa46604a00cf.html
1. 你的 create table xxx .. 语句
2. 你的 insert into xxx ... 语句
3. 结果是什么样,(并给以简单的算法描述)
4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL)
这样想帮你的人可以直接搭建和你相同的环境,并在给出方案前进行测试,避免文字描述理解上的误差。
[code]
CREATE DATABASE IF NOT EXISTS user;
USE user;
CREATE TABLE IF NOT EXISTS register
(
uid INT(10) UNSIGNED,
name VARCHAR(20),
phone VARCHAR(20)
);
INSERT INTO register SET uid=1,name="zhang",phone="13123456789";
INSERT INTO register SET uid=2,name="li",phone="13234567890";
INSERT INTO register SET uid=3,name="wang",phone="13345678901";
INSERT INTO register SET uid=4,name="ma",phone="13456789012";
CREATE DATABASE IF NOT EXISTS product;
USE product;
CREATE TABLE IF NOT EXISTS sell
(
uid INT(10) UNSIGNED,
barcode VARCHAR(20),
price FLOAT UNSIGNED
);
INSERT INTO sell SET uid=4,barcode="092636248246",price="200.00";
INSERT INTO sell SET uid=3,barcode="6921316905541",price="5.50";
INSERT INTO sell SET uid=3,barcode="6911989251236",price="3.50";
INSERT INTO sell SET uid=1,barcode="9787810776462",price="33.00";
INSERT INTO sell SET uid=4,barcode="6911352003370",price="13.80";
mysql> SELECT * FROM product.sell;
+------+---------------+-------+
| uid | barcode | price |
+------+---------------+-------+
| 4 | 092636248246 | 200 |
| 3 | 6921316905541 | 5.5 |
| 3 | 6911989251236 | 3.5 |
| 1 | 9787810776462 | 33 |
| 4 | 6911352003370 | 13.8 |
+------+---------------+-------+
5 rows in set (0.00 sec)mysql> select *
-> from user.register u inner join product.sell s using(uid)
-> where not exists (select 1 from sell where uid=s.uid and price>s.price);
+------+-------+-------------+---------------+-------+
| uid | name | phone | barcode | price |
+------+-------+-------------+---------------+-------+
| 4 | ma | 13456789012 | 092636248246 | 200 |
| 3 | wang | 13345678901 | 6921316905541 | 5.5 |
| 1 | zhang | 13123456789 | 9787810776462 | 33 |
+------+-------+-------------+---------------+-------+
3 rows in set (0.00 sec)mysql> SELECT * FROM user.register;
+------+-------+-------------+
| uid | name | phone |
+------+-------+-------------+
| 1 | zhang | 13123456789 |
| 2 | li | 13234567890 |
| 3 | wang | 13345678901 |
| 4 | ma | 13456789012 |
+------+-------+-------------+
4 rows in set (0.00 sec)mysql> SELECT * FROM product.sell;
+------+---------------+-------+
| uid | barcode | price |
+------+---------------+-------+
| 4 | 092636248246 | 200 |
| 3 | 6921316905541 | 5.5 |
| 3 | 6911989251236 | 3.5 |
| 1 | 9787810776462 | 33 |
| 4 | 6911352003370 | 13.8 |
+------+---------------+-------+
5 rows in set (0.00 sec)mysql> SELECT *
-> FROM user.register u INNER JOIN product.sell s USING(uid)
-> WHERE NOT EXISTS (SELECT 1 FROM sell WHERE uid=s.uid and price>s.price);
+------+-------+-------------+---------------+-------+
| uid | name | phone | barcode | price |
+------+-------+-------------+---------------+-------+
| 4 | ma | 13456789012 | 092636248246 | 200 |
| 3 | wang | 13345678901 | 6921316905541 | 5.5 |
| 1 | zhang | 13123456789 | 9787810776462 | 33 |
+------+-------+-------------+---------------+-------+
3 rows in set (0.00 sec)
[/code]
USE user;
CREATE TABLE IF NOT EXISTS register
(
uid INT(10) UNSIGNED,
name VARCHAR(20),
phone VARCHAR(20)
);
INSERT INTO register SET uid=1,name="zhang",phone="13123456789";
INSERT INTO register SET uid=2,name="li",phone="13234567890";
INSERT INTO register SET uid=3,name="wang",phone="13345678901";
INSERT INTO register SET uid=4,name="ma",phone="13456789012";
CREATE DATABASE IF NOT EXISTS product;
USE product;
CREATE TABLE IF NOT EXISTS sell
(
uid INT(10) UNSIGNED,
barcode VARCHAR(20),
price FLOAT UNSIGNED
);
INSERT INTO sell SET uid=4,barcode="092636248246",price="200.00";
INSERT INTO sell SET uid=3,barcode="6921316905541",price="5.50";
INSERT INTO sell SET uid=3,barcode="6911989251236",price="3.50";
INSERT INTO sell SET uid=1,barcode="9787810776462",price="33.00";
INSERT INTO sell SET uid=4,barcode="6911352003370",price="13.80";
mysql> SELECT * FROM user.register;
+------+-------+-------------+
| uid | name | phone |
+------+-------+-------------+
| 1 | zhang | 13123456789 |
| 2 | li | 13234567890 |
| 3 | wang | 13345678901 |
| 4 | ma | 13456789012 |
+------+-------+-------------+
4 rows in set (0.00 sec)mysql> SELECT * FROM product.sell;
+------+---------------+-------+
| uid | barcode | price |
+------+---------------+-------+
| 4 | 092636248246 | 200 |
| 3 | 6921316905541 | 5.5 |
| 3 | 6911989251236 | 3.5 |
| 1 | 9787810776462 | 33 |
| 4 | 6911352003370 | 13.8 |
+------+---------------+-------+
5 rows in set (0.00 sec)mysql> SELECT *
-> FROM user.register u INNER JOIN product.sell s USING(uid)
-> WHERE NOT EXISTS (SELECT 1 FROM sell WHERE uid=s.uid and price>s.price);
+------+-------+-------------+---------------+-------+
| uid | name | phone | barcode | price |
+------+-------+-------------+---------------+-------+
| 4 | ma | 13456789012 | 092636248246 | 200 |
| 3 | wang | 13345678901 | 6921316905541 | 5.5 |
| 1 | zhang | 13123456789 | 9787810776462 | 33 |
+------+-------+-------------+---------------+-------+
3 rows in set (0.00 sec)mysql>