我有一个场景,一个表返回给我多个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