如何从多个重复行值中获取唯一行 [英] How to Get unique row from the multiple duplicate row value

问题描述

 Id Cat_Id P_Name Image_Path 
  1040   1010  aaaaaaa / images / products / NewImages / Gamo-Whisper- 
  1040   1010  aaaaaaa / images / products /NewImages/IMG_0401.png 
  1040   1010  aaaaaaa / images / products / NewImages / IMG_0402。 png 
  1040   1010  aaaaaaa / images / products / NewImages / gamo-silent- 
  1040   1010  aaaaaaa /images/products/NewImages/SMALL_IMG_0135a.jpg 
  1041   1010  bbbbbbbbbbb /images/products/NewImages/SMALL_IMG_0135a.jpg 
  1041   1010  bbbbbbbbbbb /images/products/NewImages/SMALL_IMG_0140a.jpg 
  1041   1010  bbbbbbbbbbb / images / products /NewImages/SMALL_IMG_0135a.jpg 
  1041   1010  bbbbbbbbbbb / images / products / NewImages / SMALL_IMG_0140a。 jpg 
  1042   1011  ccccccccccc /images/products/NewImages/IMG_0413.png 
 
 
 
从这张表我想按照Id列获取数据那些有独特的ID像
 
 1040,1041,1042只有
 
 Image_Path是另一个名为ImagePathTable的表。我正在使用Inner Join来获取这个值
 
 Table2-ImagePathTable 
 
 
 Id P_Id ImagePath 
  1041   1040  / images / products / NewImages / IMG_0401.png 
  1042   1040  /images/products/NewImages/IMG_0402.png 
  1043   1040  /images/products/NewImages/detail_whisper_g2.jpg 
  1044   1040  / images / products / NewImages / gamo-silent-stalker-whisper-igt-22-cal-air- 
  1045   1041  /images/products/NewImages/SMALL_IMG_0135a.jpg 
  1046   1041  /images/products/NewImages/SMALL_IMG_0140a.jpg 
  1047   1041  / images / products / NewImages /SMALL_IMG_0135a.jpg 
  1048   1041  /images/products/NewImages/SMALL_IMG_0140a.jpg 
  1049   1040  / images / products / NewImages / Gamo-Whisper-CFR-Fixed-Barrel_GA- 
  1050   1042  /images/products/NewImages/IMG_0413.png 
 
 
 
 
 
 
表1  - 产品
 
 
 Id P_Name Cat_Id 
  1040  aaaaaaaaaa  1010  
  1041  aaaaaaaaaa  1010  
  1042  aaaaaaaaaa  1010  
  1043  aaaaaaaaaa  1010  
  1044  aaaaaaaaaa  1010  
  1045  bbbbbbbbbb  1010  
  1046  bbbbbbbbbb  1010  
  1047  CCCC cccccc  1011  
  1048  cccccccccc  1011  
  1049  cccccccccc  1011  
 
 
 
 
 
 
根据产品表中的产品名称，在表格中添加了多个图像2ImagePathTable
解决方案
 
在你的情况下DISTINCT将无效。
 
根据你的要求，我认为这对你有用。
 
 
 
 
  SELECT  A.Id，A.P_Name，B.ImagePath 
  FROM 产品A 
  INNER   JOIN （ SELECT  P_Id，MIN（ImagePath）ImagePath  FROM  img  GROUP   BY  P_Id） AS  B 
  ON  B.P_Id = A.ID 
 
 
 
试试这个，
 
 
  SELECT  t1.P_ID，P.CAT_ID，P.P_NAME，IPT.IMAGEPATH 
  FROM  
（ SELECT  P_ID，MAX（ID） AS  MAX_ID 
  FROM  ImagePathTable 
  GROUP   BY  P_ID 
）T1  INNER   JOIN  ImagePathTable IPT  on  T1.MAX_ID = IPT.ID 
  INNER   JOIN  PRODUCT P  ON  T1.P_ID = P.ID 
 
 
 
Id	Cat_Id	P_Name	                         Image_Path
1040	1010	aaaaaaa        	     /images/products/NewImages/Gamo-Whisper-
1040	1010	aaaaaaa        	     /images/products/NewImages/IMG_0401.png
1040	1010	aaaaaaa        	     /images/products/NewImages/IMG_0402.png
1040	1010	aaaaaaa        	     /images/products/NewImages/gamo-silent-
1040	1010	aaaaaaa        	     /images/products/NewImages/SMALL_IMG_0135a.jpg
1041	1010	bbbbbbbbbbb	     /images/products/NewImages/SMALL_IMG_0135a.jpg
1041	1010	bbbbbbbbbbb	     /images/products/NewImages/SMALL_IMG_0140a.jpg
1041	1010	bbbbbbbbbbb	     /images/products/NewImages/SMALL_IMG_0135a.jpg
1041	1010	bbbbbbbbbbb	     /images/products/NewImages/SMALL_IMG_0140a.jpg
1042	1011	ccccccccccc	     /images/products/NewImages/IMG_0413.png




From this table i want to fetch data as per the Id Column Those have unique Id like

1040,1041,1042  only

The Image_Path is filed of another table named as ImagePathTable.I am using Inner Join to get this values

Table2-ImagePathTable

Id	P_Id	ImagePath
1041	1040	/images/products/NewImages/IMG_0401.png
1042	1040	/images/products/NewImages/IMG_0402.png
1043	1040	/images/products/NewImages/detail_whisper_g2.jpg
1044	1040	/images/products/NewImages/gamo-silent-stalker-whisper-igt-22-cal-air-
1045	1041	/images/products/NewImages/SMALL_IMG_0135a.jpg
1046	1041	/images/products/NewImages/SMALL_IMG_0140a.jpg
1047	1041	/images/products/NewImages/SMALL_IMG_0135a.jpg
1048	1041	/images/products/NewImages/SMALL_IMG_0140a.jpg
1049	1040	/images/products/NewImages/Gamo-Whisper-CFR-Fixed-Barrel_GA-
1050	1042	/images/products/NewImages/IMG_0413.png 






Table1-Products

Id	P_Name	                       Cat_Id
1040	aaaaaaaaaa	                1010
1041	aaaaaaaaaa		          1010
1042	aaaaaaaaaa		        1010
1043	aaaaaaaaaa		        1010
1044	aaaaaaaaaa	                1010
1045	bbbbbbbbbb	                1010
1046	bbbbbbbbbb	                1010
1047	cccccccccc	                1011
1048	cccccccccc	        	1011
1049	cccccccccc	                1011  






According to the name of the Product in product table there is multiple images uploaded in the table 2 "ImagePathTable"
解决方案
in your case DISTINCT will  not work.

According to your requirement i think this will work for you.



 
SELECT A.Id, A.P_Name, B.ImagePath
FROM Product A
INNER JOIN (SELECT P_Id,MIN(ImagePath)  ImagePath FROM img GROUP BY P_Id) AS B
ON B.P_Id = A.ID

Try this,

SELECT t1.P_ID, P.CAT_ID, P.P_NAME, IPT.IMAGEPATH
FROM 
(SELECT P_ID, MAX(ID) AS MAX_ID
FROM   ImagePathTable
GROUP BY P_ID
) T1 INNER JOIN ImagePathTable IPT on T1.MAX_ID = IPT.ID
INNER JOIN PRODUCT P ON T1.P_ID = P.ID


                        
这篇关于如何从多个重复行值中获取唯一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！