文章表
CREATE TABLE `article` (
`ArticleID` int(11) unsigned NOT NULL auto_increment,
`ArticleUserID` int(11) unsigned default NULL,
`ArticleCategoryID` int(11) unsigned default NULL,
`ArticleCategoryParentID` int(11) unsigned default NULL,
`ArticleTitle` varchar(255) collate utf8_bin default NULL,
`ArticleContent` text collate utf8_bin,
所在地信息表
CREATE TABLE `systeminfo` (
`SystemID` int(11) unsigned NOT NULL auto_increment,
`Type` tinyint(1) unsigned default NULL,
`NodeID` int(11) unsigned default NULL,
`ParentID` int(11) unsigned default NULL,
`Content` varchar(255) collate utf8_bin default NULL,
USER表
CREATE TABLE `user` (
`UserID` int(11) unsigned NOT NULL auto_increment,
`Password` varchar(255) collate utf8_bin default NULL,
`Nickname` varchar(255) collate utf8_bin default NULL,
`Username` varchar(255) collate utf8_bin default NULL,
`Email` varchar(255) collate utf8_bin default NULL,
`TrueName` varchar(255) collate utf8_bin default NULL,
`Gender` tinyint(1) unsigned default NULL,
`Birthday` date default NULL,
`City` varchar(255) collate utf8_bin default NULL,
`Address` varchar(255) collate utf8_bin default NULL,
`PostCode` varchar(255) collate utf8_bin default NULL,
现在要做的是,根据文章ID,在Article表里找出文章信息和作者ID,根据作者ID到User表里找用户信息,其中用户所在城市的ID,然后根据城市ID到systeminfo表里找出该城市所属省份,再在systeminfo表里找出省份的名称Content
我是用两条语句实现的如下
SELECT `ArticleID`, `ArticleUserID`, `ArticleTitle`, user.UserID,user.Username,user.City
FROM article as art, user as user WHERE `ArticleID` = 129747 AND art.ArticleUserID = user.UserID 如果user.City不空,则
SELECT s1.Content FROM systeminfo AS s1,systeminfo AS s2 WHERE s2.NodeID='$city' AND s1.NodeID=s2.ParentID 请问大家,这能用一句SQL实现吗,得用外连接,user.City有可能为空,可是我怎么也写不出来,请教了
CREATE TABLE `article` (
`ArticleID` int(11) unsigned NOT NULL auto_increment,
`ArticleUserID` int(11) unsigned default NULL,
`ArticleCategoryID` int(11) unsigned default NULL,
`ArticleCategoryParentID` int(11) unsigned default NULL,
`ArticleTitle` varchar(255) collate utf8_bin default NULL,
`ArticleContent` text collate utf8_bin,
所在地信息表
CREATE TABLE `systeminfo` (
`SystemID` int(11) unsigned NOT NULL auto_increment,
`Type` tinyint(1) unsigned default NULL,
`NodeID` int(11) unsigned default NULL,
`ParentID` int(11) unsigned default NULL,
`Content` varchar(255) collate utf8_bin default NULL,
USER表
CREATE TABLE `user` (
`UserID` int(11) unsigned NOT NULL auto_increment,
`Password` varchar(255) collate utf8_bin default NULL,
`Nickname` varchar(255) collate utf8_bin default NULL,
`Username` varchar(255) collate utf8_bin default NULL,
`Email` varchar(255) collate utf8_bin default NULL,
`TrueName` varchar(255) collate utf8_bin default NULL,
`Gender` tinyint(1) unsigned default NULL,
`Birthday` date default NULL,
`City` varchar(255) collate utf8_bin default NULL,
`Address` varchar(255) collate utf8_bin default NULL,
`PostCode` varchar(255) collate utf8_bin default NULL,
现在要做的是,根据文章ID,在Article表里找出文章信息和作者ID,根据作者ID到User表里找用户信息,其中用户所在城市的ID,然后根据城市ID到systeminfo表里找出该城市所属省份,再在systeminfo表里找出省份的名称Content
我是用两条语句实现的如下
SELECT `ArticleID`, `ArticleUserID`, `ArticleTitle`, user.UserID,user.Username,user.City
FROM article as art, user as user WHERE `ArticleID` = 129747 AND art.ArticleUserID = user.UserID 如果user.City不空,则
SELECT s1.Content FROM systeminfo AS s1,systeminfo AS s2 WHERE s2.NodeID='$city' AND s1.NodeID=s2.ParentID 请问大家,这能用一句SQL实现吗,得用外连接,user.City有可能为空,可是我怎么也写不出来,请教了
SELECT `ArticleID`, `ArticleUserID`, `ArticleTitle`, user.UserID,user.Username,user.City
FROM article as art, user as user WHERE `ArticleID` = 129747 AND art.ArticleUserID = user.UserID
)
SELECT `ArticleID`, `ArticleUserID`, `ArticleTitle`, user.UserID,user.Username,user.City
FROM article as art, user as user WHERE `ArticleID` = 129747 AND art.ArticleUserID = user.UserID
)
$city 是应该是SELECT `ArticleID`, `ArticleUserID`, `ArticleTitle`, user.UserID,user.Username,user.City
FROM article as art, user as user WHERE `ArticleID` = 129747 AND art.ArticleUserID = user.UserID 查出来的user.City这句话怎么写
`art`.`ArticleUserID`,
`art`.`ArticleTitle`,
`user`.`UserID`,
`user`.`Username`,
(
select si.Content
FROM systeminfo AS si
where si.NodeID in
(
select ParentID
from systeminfo
where NodeID = `user`.`City`
)
) as user_city
from user as user left join article as art
on `art`.`ArticleUserID` = `user`.`UserID`
where `ArticleID` = 129747
学号 课程 成绩
1001 数学 90
1001 语文 100
1001 物理 95
1002 数学 60
1002 语文 95
1002 物理 75
想得到每个学生所有课程里的成绩最好的那条记录 即要得到1001 语文 100
1002 语文 95
这样,用一条语句查出来,该怎么写呢