#
# 表的结构 `tab1`
#CREATE TABLE `tab1` (
`name` varchar(20) NOT NULL,
`bumen` varchar(100) NOT NULL,
`feiyong` varchar(100) NOT NULL,
UNIQUE KEY `name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;#
# 导出表中的数据 `tab1`
#INSERT INTO `tab1` (`name`, `bumen`,`feiyong`) VALUES ('wsz1688', 'xiao1','500'),
('wsz1699', 'xiao2','200'),
('wsz1666', 'xiao1','300');
---------------------------
#
# 表的结构 `tab2`
#CREATE TABLE `tab2` (
`name` varchar(20) NOT NULL,
`money` varchar(20) NOT NULL,
`date` datetime NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;#
# 导出表中的数据 `tab2`
#INSERT INTO `tab2` (`name`, `money`, `date`) VALUES ('wsz1688', '10', '2012-10-02 00:00:00'),
('wsz1688', '12', '2012-11-03 00:00:00'),
('wsz1666', '10', '2012-10-05 00:00:00'),
('wsz1666', '2', '2012-12-03 00:00:00'),
('wsz1688', '12', '2012-09-02 00:00:00'),
('wsz1699', '10', '2012-10-02 00:00:00');
-----------------------------------------
#
# 表的结构 `tab3`
#CREATE TABLE `tab3` (
`name` varchar(20) NOT NULL,
`charge` varchar(20) NOT NULL,
`end_date` date NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;#
# 导出表中的数据 `tab3`
#INSERT INTO `tab3` (`name`, `charge`, `end_date`) VALUES ('wsz1688', '5', '2012-11-01'),
('wsz1688', '10', '2012-12-01'),
('wsz1666', '5', '2012-11-01'),
('wsz1699', '10', '2012-12-01');--------------------------------------------
想得到: tabl.feiyong 和 sum(tab2.money) 和 tab3.charge 和tab3.count(*)条件要: tab1的bumen=xiao1 且 tab2.date>=2012-10-01 且 tab3.end_date>=2012-11-01
---------------------------------------
name, feiyong, sum(tab2.money), charge, tab3.count(*)
wsz1688 500 22 5,10 2wsz1666 300 12 5 1
# 表的结构 `tab1`
#CREATE TABLE `tab1` (
`name` varchar(20) NOT NULL,
`bumen` varchar(100) NOT NULL,
`feiyong` varchar(100) NOT NULL,
UNIQUE KEY `name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;#
# 导出表中的数据 `tab1`
#INSERT INTO `tab1` (`name`, `bumen`,`feiyong`) VALUES ('wsz1688', 'xiao1','500'),
('wsz1699', 'xiao2','200'),
('wsz1666', 'xiao1','300');
---------------------------
#
# 表的结构 `tab2`
#CREATE TABLE `tab2` (
`name` varchar(20) NOT NULL,
`money` varchar(20) NOT NULL,
`date` datetime NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;#
# 导出表中的数据 `tab2`
#INSERT INTO `tab2` (`name`, `money`, `date`) VALUES ('wsz1688', '10', '2012-10-02 00:00:00'),
('wsz1688', '12', '2012-11-03 00:00:00'),
('wsz1666', '10', '2012-10-05 00:00:00'),
('wsz1666', '2', '2012-12-03 00:00:00'),
('wsz1688', '12', '2012-09-02 00:00:00'),
('wsz1699', '10', '2012-10-02 00:00:00');
-----------------------------------------
#
# 表的结构 `tab3`
#CREATE TABLE `tab3` (
`name` varchar(20) NOT NULL,
`charge` varchar(20) NOT NULL,
`end_date` date NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;#
# 导出表中的数据 `tab3`
#INSERT INTO `tab3` (`name`, `charge`, `end_date`) VALUES ('wsz1688', '5', '2012-11-01'),
('wsz1688', '10', '2012-12-01'),
('wsz1666', '5', '2012-11-01'),
('wsz1699', '10', '2012-12-01');--------------------------------------------
想得到: tabl.feiyong 和 sum(tab2.money) 和 tab3.charge 和tab3.count(*)条件要: tab1的bumen=xiao1 且 tab2.date>=2012-10-01 且 tab3.end_date>=2012-11-01
---------------------------------------
name, feiyong, sum(tab2.money), charge, tab3.count(*)
wsz1688 500 22 5,10 2wsz1666 300 12 5 1
解决方案 »
- 想问一下日期处理问题.我一列数据.保存时间,但有错误数据是日期,现在要将日期替换成00:00.0这种格式,如何处理?
- mysql数据调換
- 自学用c/c++来做mysql开发要学哪些知识?
- 求两条查询优化
- 关于mysql++的使用.
- 如何把byte[]数组存储到mysql中?
- 【在线等】怎样查找数据表中所有不为空的SQL语句!
- 跪求[怎么解决asp代码读取mysql中文数据显示成?????]
- 惊世之作!Visual DBTOOLS 3.0!
- widows 10 64系统下安装 mysql.zip ,完成后续配置步骤
- 增大innodb_log_file_size,删除两个原有日志文件后重启动却多出了一个ib_arch_log_0000000002文件
- 商品信息数据库设计
INNER JOIN (SELECT `name`,SUM(money) FROM `tab2`
WHERE `date`>='2012-10-01' GROUP BY `name`) b
ON a.`name`=b.`name`
INNER JOIN (SELECT `name`,GROUP_CONCAT(`charge`),COUNT(*) FROM `tab3`
WHERE end_date>='2012-11-01' GROUP BY `name`) c
ON a.`name`=c.`name`
WHERE a.bumen='xiao1'