innerjoin出现重复数据,inner join重复数据
用sql语句"inner join"查询ACCESS中两个表的不用记录,老出现好多重复数据?
查到了表1中有的id号,而表2中没有的id号
这个sql文不是这么写的,不考虑性能的话,最简单
select * from 表1 where 表1.id not in (select 表2.id from 表2)
mysql多表查询出现重复数据
我也遇到这样的问题,后来发现是写错了。
错误查询语句
select * from (dede_archives inner join dede_addonshop on dede_archives.typeid=dede_addonshop.typeid) where dede_archives.channel=6 ORDER BY RAND() limit 12
结果是:
运行SQL:select * from (dede_archives inner join dede_addonshop on
dede_archives.typeid=dede_addonshop.typeid) where dede_archives.channel=6
ORDER BY RAND() limit 12,共有9条记录,最大返回100条!记录:1id:4
typeid:6
typeid2:0
sortrank:1370428324
flag:p,a
ismake:1
channel:6
arcrank:0
click:106
money:0
title:3新款 情趣内衣套装制服 女式性感透明诱惑薄纱 齐B紧身睡裙8753
shorttitle:
color:
writer:
source:未知
litpic:/uploads/allimg/130605/1-1306051S2150-L.jpg
pubdate:1370428324
senddate:1370428366
mid:1
keywords:齐,薄纱,B,紧身,8753,睡裙,诱惑,透明,套装,情趣内
lastpost:0
scores:0
goodpost:0
badpost:0
notpost:0
description:情趣内衣套装制服 女式性感透明诱惑薄
filename:
dutyadmin:1
tackid:0
mtype:0
weight:3
aid:4
body:
3情趣内衣套装制服 女式性感透明诱惑薄
price:99
trueprice:44
brand:黑爵
units:
templet:
userip:127.0.0.1
redirecturl:
iid:11
记录:2id:6
typeid:6
typeid2:0
sortrank:1370575548
flag:p
ismake:1
channel:6
arcrank:0
click:175
money:0
title:2蝶华正品精致印花蕾丝花边女式聚拢型文胸套装
shorttitle:
color:
writer:
source:未知
litpic:/uploads/allimg/130607/1-13060G126070-L.jpg
pubdate:1370575548
senddate:1370575620
mid:1
keywords:女式,聚拢型,文胸,套装,花边,蕾丝,正品,精致,印花,蝶华
lastpost:0
scores:0
goodpost:0
badpost:0
notpost:0
description:蝶华正品精致印花蕾丝花边女式聚拢型文胸套装
filename:
dutyadmin:1
tackid:0
mtype:0
weight:5
aid:7
body:
测试用的哦1
price:2022
trueprice:1002
brand:美人儿
units:
templet:
userip:127.0.0.1
redirecturl:
iid:24418612187
记录:3id:6
typeid:6
typeid2:0
sortrank:1370575548
flag:p
ismake:1
channel:6
arcrank:0
click:175
money:0
title:2蝶华正品精致印花蕾丝花边女式聚拢型文胸套装
shorttitle:
color:
writer:
source:未知
litpic:/uploads/allimg/130607/1-13060G126070-L.jpg
pubdate:1370575548
senddate:1370575620
mid:1
keywords:女式,聚拢型,文胸,套装,花边,蕾丝,正品,精致,印花,蝶华
lastpost:0
scores:0
goodpost:0
badpost:0
notpost:0
description:蝶华正品精致印花蕾丝花边女式聚拢型文胸套装
filename:
dutyadmin:1
tackid:0
mtype:0
weight:5
aid:4
body:
3情趣内衣套装制服 女式性感透明诱惑薄
price:99
trueprice:44
brand:黑爵
units:
templet:
userip:127.0.0.1
redirecturl:
iid:11
记录:4id:4
typeid:6
typeid2:0
sortrank:1370428324
flag:p,a
ismake:1
channel:6
arcrank:0
click:106
money:0
title:3新款 情趣内衣套装制服 女式性感透明诱惑薄纱 齐B紧身睡裙8753
shorttitle:
color:
writer:
source:未知
litpic:/uploads/allimg/130605/1-1306051S2150-L.jpg
pubdate:1370428324
senddate:1370428366
mid:1
keywords:齐,薄纱,B,紧身,8753,睡裙,诱惑,透明,套装,情趣内
lastpost:0
scores:0
goodpost:0
badpost:0
notpost:0
description:情趣内衣套装制服 女式性感透明诱惑薄
filename:
dutyadmin:1
tackid:0
mtype:0
weight:3
aid:7
body:
测试用的哦1
price:2022
trueprice:1002
brand:美人儿
units:
templet:
userip:127.0.0.1
redirecturl:
iid:24418612187
记录:5id:6
typeid:6
typeid2:0
sortrank:1370575548
flag:p
ismake:1
channel:6
arcrank:0
click:175
money:0
title:2蝶华正品精致印花蕾丝花边女式聚拢型文胸套装
shorttitle:
color:
writer:
source:未知
litpic:/uploads/allimg/130607/1-13060G126070-L.jpg
pubdate:1370575548
senddate:1370575620
mid:1
keywords:女式,聚拢型,文胸,套装,花边,蕾丝,正品,精致,印花,蝶华
lastpost:0
scores:0
goodpost:0
badpost:0
notpost:0
description:蝶华正品精致印花蕾丝花边女式聚拢型文胸套装
filename:
dutyadmin:1
tackid:0
mtype:0
weight:5
aid:6
body:
2蝶华正品精致印花蕾丝花边女式聚拢型文胸套装
price:102
trueprice:36
brand:美人儿
units:
templet:
userip:127.0.0.1
redirecturl:
iid:11110
记录:6id:7
typeid:6
typeid2:0
sortrank:1370575625
flag:p
ismake:1
channel:6
arcrank:0
click:157
money:0
title:1【高校直达】黑爵A5入门级游戏键盘 正品超值键盘 有线键盘 电脑
shorttitle:
color:
writer:
source:未知
litpic:/uploads/allimg/130607/1-13060G12I30-L.jpg
pubdate:1370575625
senddate:1370575694
mid:1
keywords:键盘,正品,超值,有线,电脑,游戏,级,直达,黑爵,A5,入
lastpost:0
scores:0
goodpost:0
badpost:0
notpost:0
description:测试用的哦
filename:
dutyadmin:1
tackid:0
mtype:0
weight:6
aid:6
body:
2蝶华正品精致印花蕾丝花边女式聚拢型文胸套装
price:102
trueprice:36
brand:美人儿
units:
templet:
userip:127.0.0.1
redirecturl:
iid:11110
记录:7id:7
typeid:6
typeid2:0
sortrank:1370575625
flag:p
ismake:1
channel:6
arcrank:0
click:157
money:0
title:1【高校直达】黑爵A5入门级游戏键盘 正品超值键盘 有线键盘 电脑
shorttitle:
color:
writer:
source:未知
litpic:/uploads/allimg/130607/1-13060G12I30-L.jpg
pubdate:1370575625
senddate:1370575694
mid:1
keywords:键盘,正品,超值,有线,电脑,游戏,级,直达,黑爵,A5,入
lastpost:0
scores:0
goodpost:0
badpost:0
notpost:0
description:测试用的哦
filename:
dutyadmin:1
tackid:0
mtype:0
weight:6
aid:4
body:
3情趣内衣套装制服 女式性感透明诱惑薄
price:99
trueprice:44
brand:黑爵
units:
templet:
userip:127.0.0.1
redirecturl:
iid:11
记录:8id:4
typeid:6
typeid2:0
sortrank:1370428324
flag:p,a
ismake:1
channel:6
arcrank:0
click:106
money:0
title:3新款 情趣内衣套装制服 女式性感透明诱惑薄纱 齐B紧身睡裙8753
shorttitle:
color:
writer:
source:未知
litpic:/uploads/allimg/130605/1-1306051S2150-L.jpg
pubdate:1370428324
senddate:1370428366
mid:1
keywords:齐,薄纱,B,紧身,8753,睡裙,诱惑,透明,套装,情趣内
lastpost:0
scores:0
goodpost:0
badpost:0
notpost:0
description:情趣内衣套装制服 女式性感透明诱惑薄
filename:
dutyadmin:1
tackid:0
mtype:0
weight:3
aid:6
body:
2蝶华正品精致印花蕾丝花边女式聚拢型文胸套装
price:102
trueprice:36
brand:美人儿
units:
templet:
userip:127.0.0.1
redirecturl:
iid:11110
记录:9id:7
typeid:6
typeid2:0
sortrank:1370575625
flag:p
ismake:1
channel:6
arcrank:0
click:157
money:0
title:1【高校直达】黑爵A5入门级游戏键盘 正品超值键盘 有线键盘 电脑
shorttitle:
color:
writer:
source:未知
litpic:/uploads/allimg/130607/1-13060G12I30-L.jpg
pubdate:1370575625
senddate:1370575694
mid:1
keywords:键盘,正品,超值,有线,电脑,游戏,级,直达,黑爵,A5,入
lastpost:0
scores:0
goodpost:0
badpost:0
notpost:0
description:测试用的哦
filename:
dutyadmin:1
tackid:0
mtype:0
weight:6
aid:7
body:
测试用的哦1
price:2022
trueprice:1002
brand:美人儿
units:
templet:
userip:127.0.0.1
redirecturl:
iid:24418612187
后来改认真看了下,改了下
select * from dede_archives left join dede_addonshop on dede_archives.id=dede_addonshop.aid where dede_archives.channel=6 ORDER BY RAND() limit 12
结果就正确了
运行SQL:select * from dede_archives left join dede_addonshop on
dede_archives.id=dede_addonshop.aid where dede_archives.channel=6 ORDER BY
RAND() limit 12,共有3条记录,最大返回100条!记录:1id:4
typeid:6
typeid2:0
sortrank:1370428324
flag:p,a
ismake:1
channel:6
arcrank:0
click:106
money:0
title:3新款 情趣内衣套装制服 女式性感透明诱惑薄纱 齐B紧身睡裙8753
shorttitle:
color:
writer:
source:未知
litpic:/uploads/allimg/130605/1-1306051S2150-L.jpg
pubdate:1370428324
senddate:1370428366
mid:1
keywords:齐,薄纱,B,紧身,8753,睡裙,诱惑,透明,套装,情趣内
lastpost:0
scores:0
goodpost:0
badpost:0
notpost:0
description:情趣内衣套装制服 女式性感透明诱惑薄
filename:
dutyadmin:1
tackid:0
mtype:0
weight:3
aid:4
body:
3情趣内衣套装制服 女式性感透明诱惑薄
price:99
trueprice:44
brand:黑爵
units:
templet:
userip:127.0.0.1
redirecturl:
iid:11
记录:2id:7
typeid:6
typeid2:0
sortrank:1370575625
flag:p
ismake:1
channel:6
arcrank:0
click:157
money:0
title:1【高校直达】黑爵A5入门级游戏键盘 正品超值键盘 有线键盘 电脑
shorttitle:
color:
writer:
source:未知
litpic:/uploads/allimg/130607/1-13060G12I30-L.jpg
pubdate:1370575625
senddate:1370575694
mid:1
keywords:键盘,正品,超值,有线,电脑,游戏,级,直达,黑爵,A5,入
lastpost:0
scores:0
goodpost:0
badpost:0
notpost:0
description:测试用的哦
filename:
dutyadmin:1
tackid:0
mtype:0
weight:6
aid:7
body:
测试用的哦1
price:2022
trueprice:1002
brand:美人儿
units:
templet:
userip:127.0.0.1
redirecturl:
iid:24418612187
记录:3id:6
typeid:6
typeid2:0
sortrank:1370575548
flag:p
ismake:1
channel:6
arcrank:0
click:175
money:0
title:2蝶华正品精致印花蕾丝花边女式聚拢型文胸套装
shorttitle:
color:
writer:
source:未知
litpic:/uploads/allimg/130607/1-13060G126070-L.jpg
pubdate:1370575548
senddate:1370575620
mid:1
keywords:女式,聚拢型,文胸,套装,花边,蕾丝,正品,精致,印花,蝶华
lastpost:0
scores:0
goodpost:0
badpost:0
notpost:0
description:蝶华正品精致印花蕾丝花边女式聚拢型文胸套装
filename:
dutyadmin:1
tackid:0
mtype:0
weight:5
aid:6
body:
2蝶华正品精致印花蕾丝花边女式聚拢型文胸套装
price:102
trueprice:36
brand:美人儿
units:
templet:
userip:127.0.0.1
redirecturl:
iid:11110
希望对你有用。
为什么我在sql查询语句中用inner join联合查询 会出现双倍的数据
A表 ca cb B表 cc cd
1 b 1 d
2 bb 1 dd
3 bbb 2 ddd
3 dddd
select * from a表 inner join b表
结果会是 1 b 1 d
1 b 1 dd
2 bb 2 ddd
3 bbb 3 dddd
因为你 a表与b表关联的字段,在a表或者b表中存在多条,所有会多出现好多条
mysql数据库里只有一条数据为什么查询出来有两条重复的数据
inner join 会兼顾两张表,应该是你另一个表里面那个主键的数据有两条;;;先确定主表,用left join 或者right join 就会只有一条了