如何使用内部连接从其他表中获取记录


how to fetch records from other table using inner join

我有一个场景,一个表返回给我多个id。现在我想从其他表中使用相同的查询获取这些id的记录。我的查询是:

SELECT `uID` AS `userID`, 
       count(uID) AS `artistTotalViews` 
  FROM `artist_view` 
GROUP BY 
       `uID` 
ORDER BY 
       `artistTotalViews` DESC

我的结果是:

userID  ArtistTotalViews
    0   2
    1   2
    5   1
    4   1
    2   1

现在我想使用这些userID,我可以从其他表中获取数据。从另一个表中使用相同的查询,但是针对其他表中的userID查询整行。

我的表结构是:

我要从中获取记录的那个:

-- ----------------------------
-- Table structure for web_user
-- ----------------------------
DROP TABLE IF EXISTS `web_user`;
CREATE TABLE `web_user` (
  `uID` int(8) NOT NULL auto_increment,
  `uFname` varchar(15) NOT NULL,
  `uLname` varchar(15) NOT NULL,
  `uEmail` varchar(100) NOT NULL,
  `userName` varchar(15) NOT NULL,
  `uPassword` varchar(255) NOT NULL,
  `uAddress` varchar(255) default NULL,
  `uCity` varchar(20) default NULL,
  `uState` varchar(5) default NULL,
  `uZipcode` int(8) default NULL,
  `uPhone` varchar(15) default NULL,
  `uImage` varchar(150) default NULL,
  `userType` tinyint(1) NOT NULL,
  `uGenre` int(8) default NULL,
  `website` varchar(255) default NULL,
  `biography` text,
  `uVerifyCode` varchar(255) NOT NULL,
  `featured` tinyint(1) NOT NULL default '0',
  `activate` tinyint(1) default '0',
  `uStatus` tinyint(1) NOT NULL,
  `uPaid` tinyint(1) NOT NULL,
  PRIMARY KEY  (`uID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

当前查询的完美工作:

-- ----------------------------
-- Table structure for artist_view
-- ----------------------------
DROP TABLE IF EXISTS `artist_view`;
CREATE TABLE `artist_view` (
  `viewID` int(8) NOT NULL auto_increment,
  `uID` int(8) NOT NULL,
  `userIP` varchar(20) NOT NULL,
  PRIMARY KEY  (`viewID`)
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of artist_view
-- ----------------------------
INSERT INTO `artist_view` VALUES ('1', '5', '203.170.68.22');
INSERT INTO `artist_view` VALUES ('2', '4', '203.170.68.22');
INSERT INTO `artist_view` VALUES ('3', '2', '203.170.68.22');
INSERT INTO `artist_view` VALUES ('4', '1', '203.170.68.22');
INSERT INTO `artist_view` VALUES ('5', '0', '203.170.68.22');
INSERT INTO `artist_view` VALUES ('6', '1', '103.224.14.2');
INSERT INTO `artist_view` VALUES ('7', '0', '103.224.14.2');

谢谢。

我没有明白你的问题,只是我想这可以帮助你,我的朋友:

SELECT t1.*,t2.* FROM
(SELECT uID AS userID, count(uID) AS artistTotalViews 
FROM artist_view GROUP BY uID ORDER BY artistTotalViews DESC)t1
INNER JOIN TABLE2 t2 on t1.userID = t2.id;
Select web_user.uID,web_user.uFname,web_user.uLname, artist_view.uID
From web_user inner join artist_view
ON web_user.uID = artist_view.uID.

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;    //tabel1.userID = table2.userID

试试这个:

SELECT T1.*,wu.*
FROM 
(SELECT `uID` AS `userID`,count(uID) AS `artistTotalViews` FROM `artist_view` GROUP BY `uID`) T1
 LEFT JOIN web_user wu  on T1.userID=wu.uID