我有一个场景，我需要根据一个用户ID从两个表中获取记录。假设我的id是31，那么我想从两个表中获取id为31的记录。我尝试了很多解决方案，但都不适用。请看一看，帮我解决问题。
视频表的表结构：
CREATE TABLE `web_videos` (
`vID` int(8) NOT NULL auto_increment,
`userID` int(8) NOT NULL,
`v_filename` varchar(255) NOT NULL,
`v_uploadname` varchar(25) NOT NULL,
`v_short_desc` varchar(255) NOT NULL,
`v_singerName` varchar(25) NOT NULL,
`v_approve` tinyint(4) NOT NULL,
`v_price` decimal(20,2) NOT NULL default '0.00',
PRIMARY KEY (`vID`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;
音频表的表结构：
CREATE TABLE `web_audio` (
`aID` int(8) NOT NULL auto_increment,
`userID` int(8) NOT NULL,
`a_filename` varchar(30) NOT NULL,
`a_uploadname` varchar(25) NOT NULL,
`a_genre` int(8) NOT NULL,
`a_singerName` varchar(25) NOT NULL,
`a_approve` tinyint(1) NOT NULL,
`a_price` decimal(20,2) NOT NULL default '0.00',
PRIMARY KEY (`aID`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;
我的记录是：
3 31 test mohsin_781.mp3 3 test singer 0 2.50 2 31 asd _639.flv asdasdd asd sadasd 0 6.33
4 31 asd mohsin_180.mp3 10 asd 0 7.34 2 31 asd _639.flv asdasdd asd sadasd 0 6.33
5 31 mn _977.mp3 5 lkj 0 8.77 2 31 asd _639.flv asdasdd asd sadasd 0 6.33
3 31 test mohsin_781.mp3 3 test singer 0 2.50 3 31 asdasd _922.flv ad asdada da das adsad 0 6.33
4 31 asd mohsin_180.mp3 10 asd 0 7.34 3 31 asdasd _922.flv ad asdada da das adsad 0 6.33
5 31 mn _977.mp3 5 lkj 0 8.77 3 31 asdasd _922.flv ad asdada da das adsad 0 6.33
3 31 test mohsin_781.mp3 3 test singer 0 2.50 4 31 fdgdfg _745.flv fdgfdgfdgg dfgdf gd gdf gdf gdf dfg fgd dgdfg gdfg 0 88.55
4 31 asd mohsin_180.mp3 10 asd 0 7.34 4 31 fdgdfg _745.flv fdgfdgfdgg dfgdf gd gdf gdf gdf dfg fgd dgdfg gdfg 0 88.55
5 31 mn _977.mp3 5 lkj 0 8.77 4 31 fdgdfg _745.flv fdgfdgfdgg dfgdf gd gdf gdf gdf dfg fgd dgdfg gdfg 0 88.55
我的SQL查询：
SELECT
web_audio.aID,
web_audio.userID,
web_audio.a_filename,
web_audio.a_uploadname,
web_audio.a_genre,
web_audio.a_singerName,
web_audio.a_approve,
web_audio.a_price
FROM
web_audio
INNER JOIN web_videos ON web_videos.userID = web_audio.userID
WHERE
web_audio.userID = 31 AND web_videos.userID = 31
你可以看到它在复制记录。所以请让它像我只有用户ID为31的记录没有重复。
谢谢。
SELECT distinct web_audio.aID,
web_audio.userID,
web_audio.a_filename,
web_audio.a_uploadname,
web_audio.a_genre,
web_audio.a_singerName,
web_audio.a_approve,
web_audio.a_price
FROM web_audio
INNER JOIN web_videos ON web_videos.userID = web_audio.userID
WHERE
web_audio.userID = 31 AND web_videos.userID = 31
使用distinct以避免重复。
SELECT
DISTINCT web_audio.aID,
--the rest of yout query
试试这个：
SELECT web_audio.*
FROM web_audio INNER JOIN
web_videos ON web_videos.userID = web_audio.userID
WHERE
web_audio.userID = 31 AND web_videos.userID = 31
GROUP BY web_audio.aid
请参阅SQL Fiddle中的示例。
由于您仅从web_audio中选择值，请使用以下选项：
SELECT * FROM web_audio WHERE userID=31
他们两个都会给出相同的结果。