SELECT TOP 16 a.ID, b.UID, a.UserName, a.CorpName, a.CnArea, a.UserType, a.Points, a.CorpLip, a.CnArea AS Expr1 FROM 企业库 a INNER JOIN 产品库 b ON b.UID = a.ID WHERE (a.SorP = 's') AND (a.UserType = 10) AND (a.IsActive = 1) AND (a.CorE = 'c') OR (a.ID NOT IN (SELECT TOP 0 GP_corp.ID FROM 企业库 WHERE 1 = 1 AND sorp = 's' AND UserType = 10 AND (a.IsActive = 1 AND CorE = 'c') OR (core = 'e' AND a.t_checkflag = '1') ORDER BY UserType DESC, a.points DESC, a.Id DESC)) AND (a.CorE = 'e') AND (a.t_checkflag = '1') ORDER BY a.UserType DESC, a.Points DESC, a.ID DESC
DISTINCT 这个,和group by 这个方法都试过了,也许像“lff642 ”说的,是多对多的关系,这个企业有多少产品,那么这个企业就重复多少次。
-加一个distinct SELECT distinct TOP 16 a.ID, b.UID, a.UserName, a.CorpName, a.CnArea, a.UserType, a.Points, a.CorpLip, a.CnArea AS Expr1 FROM 企业库 a INNER JOIN 产品库 b ON b.UID = a.ID WHERE (a.SorP = 's') AND (a.UserType = 10) AND (a.IsActive = 1) AND (a.CorE = 'c') OR (a.ID NOT IN (SELECT TOP 0 GP_corp.ID FROM 企业库 WHERE 1 = 1 AND sorp = 's' AND UserType = 10 AND (a.IsActive = 1 AND CorE = 'c') OR (core = 'e' AND a.t_checkflag = '1') ORDER BY UserType DESC, a.points DESC, a.Id DESC)) AND (a.CorE = 'e') AND (a.t_checkflag = '1') ORDER BY a.UserType DESC, a.Points DESC, a.ID DESC
select * from (select col1,col2 from tb group col1,col2) a join (select col1,col2 from tb1 group col1,col2) b on xxx=xxx join (select col1,col2 from tb3 group col1,col2) c on xxx=xxx 每个表都做为子表,然后去重再关联
每个表单独用的话,没有重复的,只有当连个表连在一起的时候,就出现重复的列了,我不想要重复的列,那么我想可以用分组的方法 group by 的方法,但是就是不能分(估计是俩表连完不能用分组),还是我分错了,反正就是没行,后来我又用 DISTINCT 这个方法,放在 TOP 的前边,到是不报错,就是没效果。我总结出来,如果一个企业又多少产品,那么这个企业就重复多少次,我不想要重复的企业,想每个企业只显示一次。
这样,你应该知道怎么换表名和字段名吧 select * from 企业表 where 企业id in (select disdinct 企业id from 产品表 where 产品品质='高品质')
呵呵,这个思路,我试验过,我还是懂点SQL,就是这个问题有点复杂,也许跟俩表连有关系。
SELECT TOP 16 a.ID, b.UID, a.UserName, a.CorpName, a.CnArea, a.UserType, a.Points, a.CorpLip, a.CnArea AS Expr1 FROM 企业库 a INNER JOIN 产品库 b ON b.UID = a.ID WHERE (a.SorP = 's') AND (a.UserType = 10) AND (a.IsActive = 1) AND (a.CorE = 'c') OR (a.ID NOT IN (SELECT TOP 0 GP_corp.ID FROM 企业库 WHERE 1 = 1 AND sorp = 's' AND UserType = 10 AND (a.IsActive = 1 AND CorE = 'c') OR (core = 'e' AND a.t_checkflag = '1') ORDER BY UserType DESC, a.points DESC, a.Id DESC)) AND (a.CorE = 'e') AND (a.t_checkflag = '1') group by a.ID, b.UID, a.UserName, a.CorpName, a.CnArea, a.UserType, a.Points, a.CorpLip, a.CnArea ORDER BY a.UserType DESC, a.Points DESC, a.ID DESC
是重复行的问题吧,怎么都说重复列啊?????如果是删除重复行的话,除了DISTINCT, 可以试试这个 delete from [表名] where [列名] not in(select min([列名])as [列名] from [表名] group by [分组列名])
SELECT TOP 16 a.企业名字,b.产品名字 FROM 企业库 a INNER JOIN 产品库 b ON b.UID = a.ID现在查询出来的数据不是重复的,因为产品的名字不同,但是有些产品是属于同一个企业,那么企业的名字就有重复的,我现在想取企业的名字,并且每个企业的名字只要一个。感觉这个表述清晰多了吧。
a.CorpLip, a.CnArea AS Expr1
FROM 企业库 a INNER JOIN
产品库 b ON b.UID = a.ID
WHERE (a.SorP = 's') AND (a.UserType = 10) AND (a.IsActive = 1) AND (a.CorE = 'c') OR
(a.ID NOT IN
(SELECT TOP 0 GP_corp.ID
FROM 企业库
WHERE 1 = 1 AND sorp = 's' AND UserType = 10 AND (a.IsActive = 1 AND
CorE = 'c') OR
(core = 'e' AND a.t_checkflag = '1')
ORDER BY UserType DESC, a.points DESC, a.Id DESC)) AND (a.CorE = 'e') AND
(a.t_checkflag = '1')
ORDER BY a.UserType DESC, a.Points DESC, a.ID DESC
SELECT distinct TOP 16 a.ID, b.UID, a.UserName, a.CorpName, a.CnArea, a.UserType, a.Points,
a.CorpLip, a.CnArea AS Expr1
FROM 企业库 a INNER JOIN
产品库 b ON b.UID = a.ID
WHERE (a.SorP = 's') AND (a.UserType = 10) AND (a.IsActive = 1) AND (a.CorE = 'c') OR
(a.ID NOT IN
(SELECT TOP 0 GP_corp.ID
FROM 企业库
WHERE 1 = 1 AND sorp = 's' AND UserType = 10 AND (a.IsActive = 1 AND
CorE = 'c') OR
(core = 'e' AND a.t_checkflag = '1')
ORDER BY UserType DESC, a.points DESC, a.Id DESC)) AND (a.CorE = 'e') AND
(a.t_checkflag = '1')
ORDER BY a.UserType DESC, a.Points DESC, a.ID DESC
山东金地食品有限公司
杭州临安林佳旅游食品有限公司
杭州临安林佳旅游食品有限公司
辽宁东亚种业有限公司
老兵生态养殖
老兵生态养殖
河北阜平神农特产有限公司
河北阜平神农特产有限公司
河北阜平神农特产有限公司
河北阜平神农特产有限公司
河北阜平神农特产有限公司
河北阜平神农特产有限公司
河北阜平神农特产有限公司
河北阜平神农特产有限公司就是说,比如我公司发布了俩产品,那么我公司的名字就重复两次,帮我分析分析。
2118
2129
2129
2126
2126
2360
2311
2311
2339
2339
2339
2339
2339
2339
2339
2339
UserName
saifuddin
sdjdsp
sdjdsp
hzlalj
hzlalj
lndyzyyxgs
LAOBINGYANGZHI
LAOBINGYANGZHI
shennongjia1
shennongjia1
shennongjia1
shennongjia1
shennongjia1
shennongjia1
shennongjia1
shennongjia1
这些数据够吗?
企业库和产品库连,产品表里边有他自己企业的UID,那么俩表就连上了,现在企业有几个产品,那么企业就重复多少次。这个是怎么回事?怎么解决,分组啥的都用过了,俩表连好像不行。
(select col1,col2 from tb group col1,col2) a
join
(select col1,col2 from tb1 group col1,col2) b
on xxx=xxx
join
(select col1,col2 from tb3 group col1,col2) c
on xxx=xxx
每个表都做为子表,然后去重再关联
就相当于简单的A表和B表Jion,如果是多对多的话,肯定会重复,
select * from 企业表 where 企业id in
(select disdinct 企业id from 产品表 where 产品品质='高品质')
a.CorpLip, a.CnArea AS Expr1
FROM 企业库 a INNER JOIN
产品库 b ON b.UID = a.ID
WHERE (a.SorP = 's') AND (a.UserType = 10) AND (a.IsActive = 1) AND (a.CorE = 'c') OR
(a.ID NOT IN
(SELECT TOP 0 GP_corp.ID
FROM 企业库
WHERE 1 = 1 AND sorp = 's' AND UserType = 10 AND (a.IsActive = 1 AND
CorE = 'c') OR
(core = 'e' AND a.t_checkflag = '1')
ORDER BY UserType DESC, a.points DESC, a.Id DESC)) AND (a.CorE = 'e') AND
(a.t_checkflag = '1')
group by a.ID, b.UID, a.UserName, a.CorpName, a.CnArea, a.UserType, a.Points,
a.CorpLip, a.CnArea
ORDER BY a.UserType DESC, a.Points DESC, a.ID DESC
山楂羹
维美山楂
临佳牌核桃
烘烤核桃
东单80
虫子鸡
无菌蝇蛆
阜平大枣特类
阜平大枣一类
阜平大枣二类
阜平大枣三类
阜平大枣四类
阜平大枣五类
阜平纯正槐花蜜
阜平纯正枣花蜜
我又想到了,我查出来的记录不是重复的,虽然企业都是相同的,但是相应的产品不同,所以,我查出来的数据不是重复的,但是因为我只要企业的名称等字段的信息,所以企业的名字看起来是重复的。应该用group by 的方法,按照企业名称分组。但是没好使,这点没弄明白。这个问题我也想了很久,才来问的。
delete from [表名] where [列名] not in(select min([列名])as [列名]
from [表名] group by [分组列名])
FROM 企业库 a INNER JOIN
产品库 b ON b.UID = a.ID现在查询出来的数据不是重复的,因为产品的名字不同,但是有些产品是属于同一个企业,那么企业的名字就有重复的,我现在想取企业的名字,并且每个企业的名字只要一个。感觉这个表述清晰多了吧。
给点数据,然后给出结果,这样才清晰,
只是一段SQL,没有数据,
没有结构,怎么分析,
都挂了半天了,
来来去去的人不少,
还是没有解决.
主要是楼主描述来,描述去,
好像都不在点子上,
----------------
Group By....
那就结了吧,别老挂起.