如何删除重复记录 [英] how to remove repeated records

问题描述

我有两张桌子;



包含fowwing字段的音乐：

 music_id movie_name Movie_image 
  1  XXX  1  .jpg 
  2  YYY  2  .jpg 
 

music_details是第二个具有music_id

的外键引用的表，包含以下字段。

 music_id songtitle Lyricist Artist 
  1  song1 aaa bbbb 
  1  song2 bbb ccc 
  1  song3 ccc ddd 

现在我需要得到来自数据库的详细信息如：

 music_id music_name music_image Songtitle Lyricist Artist 
  1  XXX  1  .jpg song1 aaa bbbb 
 song2 bbb ccc 
 song3 ccc ddd 

i使用了连接，但它显示了movie_names和图像foreach song。

但是我需要movie_name和movie_image所有歌曲只有一次..有没有办法像这样显示。

解决方案

首先，阅读我对这个问题的评论。



其次......

如果你真的必须在T-SQL中做，你需要为每一行添加数字（ MySQL ROW_NUMBER（） [ ^ ]）然后使用 CASE ... END [ ^ ]声明，例如：

  SET   @ row  =  0 ; 
 
  SELECT  RowNo， CASE   WHEN  RowNo = 1  THEN  music_id  ELSE   NULL   END   CASE   AS  music_id ， CASE   WHEN  RowNo = 1 那么 music_name  ELSE   NULL   END   CASE   AS  music_name， CASE   WHEN  RowNo = 1 那么 music_image  ELSE   NULL   END   CASE   AS  music_image，Songtitle，Lyricist，Artist 
  FROM （
  SELECT   @ row  =  @row  +1  AS  RowNo，t1.music_id，t1.movi​​e_name  AS  music_name， t1.Movie_image  AS  music_image，t2.songtitle，t2.Lyricist，t2.Artist 
  FROM  table1  AS  t1  INNER   JOIN  table2  AS  t2  ON  t1.music_id = t2.music_id 
） AS  T 

在你的sql查询中使用distinct关键字

ex从表中选择distinct movie_name;



它将仅显示您的电影名称一次



否则，如果您使用数据库显然具有外键重复的记录。

您正在谈论的是几乎不可能，因为它会违反数据库的第一个约束规则。

在查询中使用不同

  SELECT   distinct  ColumnName  From  TableName 

i have the follwing two tables;

music containing the fowwing fields:

music_id  movie_name Movie_image
  1         XXX        1.jpg
  2        YYY        2.jpg

music_details is the second table which has foreign key reference of music_id
and contains the following fields.

music_id   songtitle   Lyricist   Artist   
1           song1       aaa        bbbb
1           song2       bbb        ccc
1           song3       ccc        ddd

now i need to get the details from data base like:

music_id  music_name   music_image   Songtitle  Lyricist    Artist
1          XXX          1.jpg          song1      aaa        bbbb
                                       song2      bbb        ccc
                                       song3      ccc        ddd

i used joins but it displaying movie_names and images foreach song.
but i need movie_name and movie_image only once for all songs.. Is there any way to display like this.

解决方案

First of all, read my comment to the question.

Secondly...
If you really MUST to do it in T-SQL, you need to add number for each row (MySQL ROW_NUMBER()[^]) and then to use CASE ... END[^] statement, for example:

SET @row = 0; 

SELECT RowNo, CASE WHEN RowNo =1 THEN music_id ELSE NULL END CASE AS music_id, CASE WHEN RowNo =1 THEN music_name ELSE NULL END CASE AS music_name,   CASE WHEN RowNo =1 THEN music_image ELSE NULL END CASE AS music_image, Songtitle, Lyricist, Artist
FROM (
    SELECT @row = @row +1 AS RowNo, t1.music_id, t1.movie_name AS music_name, t1.Movie_image AS music_image, t2.songtitle, t2.Lyricist, t2.Artist  
    FROM table1 AS t1 INNER JOIN table2 AS t2 ON t1.music_id = t2.music_id
) AS T

use distinct keyword in ur sql query
ex select distinct movie_name from table;

It will show your Movie name only once

else if you are using database obviously records with foreign key repeats.
The one you are talking is practically impossible as it will violate the first constraint rule of database.

Use Distinct in your Query

SELECT distinct ColumnName From TableName 

这篇关于如何删除重复记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！