重复值的原因:
一般就是连接的过程中连接条件出问题或者where语句有问题,所以最好检查一下,并且建议从简单的表开始关联,不要一次性关联太多表,会使逻辑复杂。
解决方法:
多表连接查询,且去掉重复列,使用 USING(),代码如下:
select * from (select * from school left join course USING (courseid)) e left join student USING (studentid)
select * from school left join course USING (courseid)相当于
select * from school left join course on school.courseid=course.courseid
扩展资料:
常用语句:
1、新建用户:
>CREATE USER name IDENTIFIED BY 'ssapdrow';
2、更改密码:
>SET PASSWORD FOR name=PASSWORD('fdddfd');
3、权限管理
>SHOW GRANTS FOR name;//查看name用户权限
>GRANT SELECT ON db_name.* TO name;//给name用户db_name数据库的所有权限
>REVOKE SELECT ON db_name.* TO name;//GRANT的反 *** 作,去除权限;
一、数据库 *** 作:
1、查看数据库:
>SHOW DATABASES
2、创建数据库:
>CREATE DATABASE db_name//db_name为数据库名
3、使用数据库:
>USE db_name
4、删除数据库:
>DROP DATABASE db_name
重复来自关联 条件了。 如果你用 A left join B on A.t1=B.t2 在B表中t2 有多条,那么你的结果 就会 变成多条了和你 用 form A,B where A.t1=B.t2 是一样的道理
如A t1 字段
1 2 3 4
B t2 字段
1 ,1,2,2,3
你如果全连接 A.t1=B.t2 就是 A.t1 只要和 B.t2 字段相等,满足这个条件的就连接了。
所以重复了就说明你的连接条件 下 有字段 不是唯一的值
我也遇到这样的问题,后来发现是写错了。错误查询语句
select * from (#@__archives inner join dede_addonshop on #@__archives.typeid=dede_addonshop.typeid) where #@__archives.channel=6 ORDER BY RAND() limit 12
结果是:
运行SQL:select * from (#@__archives inner join dede_addonshop on
#@__archives.typeid=dede_addonshop.typeid) where #@__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 #@__archives left join dede_addonshop on #@__archives.id=dede_addonshop.aid where #@__archives.channel=6 ORDER BY RAND() limit 12
结果就正确了
运行SQL:select * from #@__archives left join dede_addonshop on
#@__archives.id=dede_addonshop.aid where #@__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
希望对你有用。
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)