本文介绍了选择带有JOIN的DISTINCT的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧! 问题描述 29岁程序员,3月因学历无情被辞! 大家好 有问题我会非常感谢帮助。 我有一个标准格式的3张桌子,例如 产品 类别 类别_产品 后者允许我有多个产品类别。 除了一个烦人的小东西外,它的效果很好。 当一个单独的产品(在一个以上的topcategory中)是将 添加到它显示两次的购物车中,因为在我的选择声明中我已经列出了类别。我意识到我可以从 语句中删除TopCategory,这使得我的DISTINCT按照我的意愿工作,但我更喜欢将TopCategory设为 ,因为它可以节省我以后必须另一个SQL查询(我已经 做一个允许我不在列表中列出类别....但如果我 可以克服这一个...那么我也可以删除它。 这是我的表结构(必要的位) 产品 idProduct int .... 类别 idcategory int idParentCategory int topcategory int ... categories_products idCatProd int idProduct int idCategory 当我运行查询,例如 SELECT DISTINCT a.idProduct,a.description,a.descriptionLong , a.listPrice,a.price,a.smallImageUrl,a.stock,a.fileName,a.noShipCharge, c.topcategory FROM prod使用a,categories_products b,类别c WHERE active = -1 AND homePage = -1 AND a.idProduct = b.idProduct AND c.idcategory = b.idcategory AND prodType = 1 ORDER BY a.idProduct DESC 这将按预期返回所有产品,以及任何产品产品在多个TopCategory中是。 任何想法如何克服这一点将不胜感激。 干杯 CraigHi everyoneHave a problem I would areally appreciate help with.I have 3 tables in a standard format for a Bookshop, egProductsCategoriesCategories_Productsthe latter allowing me to have products in multiple categories.Everthing works well except for one annoying little thing.When an individual product (which is in more than one topcategory) is addedto the Shopping Cart it displays twice, because in my select statement Ihave the Category listed. I realise I could remove the TopCategory from thestatement and that makes my DISTINCT work as I wanted, but Id prefer to havethe TopCategory as it saves me later having to another SQL query (Im alreadydoing one to allow me not to list category in the Statement .... but If Ican overcome this one ... then I can remove this as well).Here is my table structure (the necessary bits)productsidProduct int....categoriesidcategory intidParentCategory inttopcategory int...categories_productsidCatProd intidProduct intidCategoryWhen I run a query such asSELECT DISTINCT a.idProduct, a.description,a.descriptionLong,a.listPrice,a.price,a.smallImageUrl,a.stock, a.fileName,a.noShipCharge,c.topcategoryFROM products a, categories_products b, categories cWHERE active = -1 AND homePage = -1AND a.idProduct = b.idProductAND c.idcategory=b.idcategoryAND prodType = 1 ORDER BY a.idProduct DESCThis will return all products as expected, as well as any products which arein more than one TopCategory.Any ideas how to overcome this would be greatly appreciated.CheersCraig推荐答案 嗨克雷格, 第一个想法必须来自你。 SQL Server不会为您做出 选择。因此,您必须决定*哪个* TopCategory显示为多个TopCategory中的产品。 如果您可以描述如何选择,我(或者这个NG中的其他人) 可能能够帮助撰写查询。 Best,Hugo - - (删除_NO_和_SPAM_以获取我的电子邮件地址)Hi Craig,The first idea will have to come from you. SQL Server won''t make yourchoices for you. So you must decide *which* TopCategory to display forproducts that are in more than one TopCategory.If you can describe how to choose, I (or someone else in this NG) willprobably be able to help writing up the query.Best, Hugo--(Remove _NO_ and _SPAM_ to get my e-mail address) 这篇关于选择带有JOIN的DISTINCT的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持! 上岸,阿里云!
07-23 03:11
查看更多