表TAB_XXX
|in_id|out_id|info_id|表TAB_IN
|id|date|表TAB_OUT
|id|date|表TAB_INFO
|id|info|如果TAB_XXX中的in_id不为0的话,则查询TAB_IN中的date,如果TAB_XXX中的out_id不为0的话,则查询TAB_OUT中的date
in_id和out_id都不为0则2个都查询
TAB_INFO中的信息是必查的这样的SQL语句如何写呢??
|in_id|out_id|info_id|表TAB_IN
|id|date|表TAB_OUT
|id|date|表TAB_INFO
|id|info|如果TAB_XXX中的in_id不为0的话,则查询TAB_IN中的date,如果TAB_XXX中的out_id不为0的话,则查询TAB_OUT中的date
in_id和out_id都不为0则2个都查询
TAB_INFO中的信息是必查的这样的SQL语句如何写呢??
建议你列出你的表结构,并提供测试数据以及基于这些测试数据的所对应正确结果。
参考一下这个贴子的提问方式http://topic.csdn.net/u/20091130/20/8343ee6a-417c-4c2d-9415-fa46604a00cf.html
1. 你的 create table xxx .. 语句
2. 你的 insert into xxx ... 语句
3. 结果是什么样,(并给以简单的算法描述)
4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL)
这样想帮你的人可以直接搭建和你相同的环境,并在给出方案前进行测试,避免文字描述理解上的误差。
表结构就是上面那样子了啊...
不过我自己解决了就是了...SELECT TAB_XXX.*,TAB_INFO.*,
IF(TAB_XXX.in_id <>0, (SELECT date from TAB_IN WHERE TAB_IN.id = TAB_XXX.in_id) ,0) as in_date,
IF(TAB_XXX.out_id<>0, (SELECT date from TAB_OUT WHERE TAB_OUT.id = TAB_XXX.out_id),0) as out_date,
FROM TAB_XXX,TAB_INFO
WHERE TAB_XXX.info_id = TAB_INFO.id AND TAB_XXX.id = 123;
Server version: 5.1.41-community MySQL Community Server (GPL)
表结构↓-- 表的结构 `tab_in`
--DROP TABLE IF EXISTS `tab_in`;
CREATE TABLE IF NOT EXISTS `tab_in` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`date` date NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk AUTO_INCREMENT=4 ;--
-- 转存表中的数据 `tab_in`
--INSERT INTO `tab_in` (`id`, `date`) VALUES
(0, '2010-03-12'),
(2, '2010-03-13'),
(3, '2010-03-19');-- ----------------------------------------------------------
-- 表的结构 `tab_info`
--DROP TABLE IF EXISTS `tab_info`;
CREATE TABLE IF NOT EXISTS `tab_info` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`info` varchar(50) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk AUTO_INCREMENT=3 ;--
-- 转存表中的数据 `tab_info`
--INSERT INTO `tab_info` (`id`, `info`) VALUES
(1, 'XXXXXOOOOOO'),
(2, 'AAAAAAAABBBBBBBB');-- ----------------------------------------------------------
-- 表的结构 `tab_out`
--DROP TABLE IF EXISTS `tab_out`;
CREATE TABLE IF NOT EXISTS `tab_out` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`date` date NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk AUTO_INCREMENT=4 ;--
-- 转存表中的数据 `tab_out`
--INSERT INTO `tab_out` (`id`, `date`) VALUES
(0, '2010-03-18'),
(2, '2010-03-26'),
(3, '2010-03-26');-- ----------------------------------------------------------
-- 表的结构 `tab_xxx`
--DROP TABLE IF EXISTS `tab_xxx`;
CREATE TABLE IF NOT EXISTS `tab_xxx` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`in_id` int(11) NOT NULL,
`out_id` int(11) NOT NULL,
`info_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `in_id` (`in_id`,`out_id`,`info_id`),
KEY `info_id` (`info_id`),
KEY `out_id` (`out_id`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk AUTO_INCREMENT=5 ;--
-- 转存表中的数据 `tab_xxx`
--INSERT INTO `tab_xxx` (`id`, `in_id`, `out_id`, `info_id`) VALUES
(1, 0, 0, 1),
(3, 0, 2, 1),
(4, 2, 3, 2),
(2, 3, 0, 2);--
-- 限制导出的表
----
-- 限制表 `tab_xxx`
--
ALTER TABLE `tab_xxx`
ADD CONSTRAINT `tab_xxx_ibfk_3` FOREIGN KEY (`info_id`) REFERENCES `tab_info` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tab_xxx_ibfk_1` FOREIGN KEY (`in_id`) REFERENCES `tab_in` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tab_xxx_ibfk_2` FOREIGN KEY (`out_id`) REFERENCES `tab_out` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
查询语句↓SELECT TAB_XXX.*,TAB_INFO.*,
IF(TAB_XXX.in_id <>0, (SELECT date from TAB_IN WHERE TAB_IN.id = TAB_XXX.in_id) ,0) as in_date,
IF(TAB_XXX.out_id<>0, (SELECT date from TAB_OUT WHERE TAB_OUT.id = TAB_XXX.out_id),0) as out_date
FROM TAB_XXX,TAB_INFO
WHERE TAB_XXX.info_id = TAB_INFO.id AND TAB_XXX.id = 2;