搜索查询问题

Question

用户表（user)
+---------+-------------+----------------+
| jifeiID | username    | yongfuzu       |
+---------+-------------+----------------+
|       5 | wsz1        | 用户组一        | 
|       5 | wsz2        | 用户组二        | 
|      21 | wsz3        | 用户组三        | 
|       2 | wsz4        | 用户组四        | 
|       3 | wsz5        | 用户组五        | 
+---------+-------------+----------------+计费信息表一(info):+-----------+---------------------+-----------+
| username  | infoTime            | infoMoney |
+-----------+---------------------+-----------+
| wsz5      | 2009-06-28 01:06:48 |      2.00 | 
| wsz1      | 2009-07-27 23:06:54 |      3.00 | 
| wsz5      | 2009-06-28 03:25:39 |      4.00 | 
| wsz1      | 2009-04-27 22:53:02 |      5.08 | 
| wsz4      | 2009-06-27 16:50:15 |      1.00 | 
+-----------+---------------------+-----------+
请费信息表二(info2):+-----------+---------+------------+------------+
| username  | jifeiID | info2Time  | info2Money |
+-----------+---------+------------+------------+
| wsz1      |       1 | 2009-06-01 |       6.00 | 
| wsz2      |       5 | 2009-08-01 |      10.00 | 
| wsz4      |       2 | 2009-07-01 |       1.00 | 
| wsz1      |       21| 2009-02-01 |      20.00 | 
| wsz1      |       2 | 2009-05-01 |      20.00 | 
+-----------+---------+------------+------------+计费组表(jifei)
+---------+---------------+
| jifeiID | jifeiName     |
+---------+---------------+
|      21 | 计费试一      | 
|       5 | 计费试二      | 
|       2 | 计费试三      | 
|       3 | 计费试四      | 
|       1 | 计费试五      | 
+---------+---------------+要做这么个搜索：按 jifeiID、按日期（年，月，日）来查出所有的费用，即(infoMoney+info2Money)
例：按月搜索出的结果：
----------------------
时间　　　　费用    
2009-06     13.00
2009-07     4.00　　
2009-08     10.00
.....
-----------------
合计        27.00

Answer 1

select `时间`, sum(infoMoney) as 费用
From(
select DATE_FORMAT(infoTime,'%Y-%m') as `时间` ,infoMoney from info
union all
select date_format(info2Time,'%Y-%m'),info2Money from info
) t
Group By  `时间`

Answer 2

不好意思，补充一下，搜索的时候，这个时间也可能是一个范围：（比如：2009-07至2009-12月）我是二个信息表: info,  info2还有，如果按计费组ID怎么查：（info2表里有计费组ID，info表里没有计费组ID，只能从USER表里去关联请指教！！

Answer 3

修改如下
select `时间`, sum(infoMoney) as 费用
From(
select DATE_FORMAT(infoTime,'%Y-%m') as `时间` ,infoMoney from info
union all
select date_format(info2Time,'%Y-%m'),info2Money from info2
) t
Group By  `时间`

Answer 4

select `timea`, sum(infoMoney) as 费用
From(
select DATE_FORMAT(infoTime,'%Y-%m') as `timea` ,infoMoney from info
union all
select date_format(info2Time,'%Y-%m'),info2Money from info2
) t
where timea between '2009-07' and '2009-12'
Group By  `timea`
还有，如果按计费组ID怎么查：（info2表里有计费组ID，info表里没有计费组ID，只能从USER表里去关联
select * from info a left join `USER` b on a.username=b.username

Answer 5

wwwwb，如果把这二个条件（时间和计费组查询）合起来写，看我写的错在哪里了，请指教：select `timea`,username1,username2, sum(infoMoney) as 费用
From(
select DATE_FORMAT(infoTime,'%Y-%m') as `timea`,infoMoney,username as username1 from info
union all
select date_format(info2Time,'%Y-%m'),info2Money,username  as username2  from info2 
) tLEFT JOIN
(select * from a ) s
ON s.username=t.username1 and s.jifeiID="搜索条件传来的计费ID"LEFT JOIN
(select * from a ) z
ON z.username=t.username2 and z.jifeiID="搜索条件传来的计费ID"where timea between '2009-07' and '2009-12'
Group By `timea`

Answer 6

(select * from a ) s
A表是什么表？

Answer 7

用户表（user)
+---------+-------------+----------------+
| jifeiID | username | yongfuzu |
+---------+-------------+----------------+
| 5 | wsz1 | 用户组一 |  
| 5 | wsz2 | 用户组二 |  
| 21 | wsz3 | 用户组三 |  
| 2 | wsz4 | 用户组四 |  
| 3 | wsz5 | 用户组五 |  
+---------+-------------+----------------+
是这个表，用户表记录的用户属性

Answer 8

刚才着急，写错了是：
select * from user.

Answer 9

SELECT `timea`,username1,username2, SUM(infoMoney) AS 费用
FROM(
SELECT DATE_FORMAT(infoTime,'%Y-%m') AS `timea`,infoMoney,username AS username1,'' AS username2 FROM info
UNION ALL
SELECT DATE_FORMAT(info2Time,'%Y-%m'),info2Money,'',username AS username2 FROM info2  
) tLEFT JOIN `user` s ON s.username=t.username1 LEFT JOIN `user` z ON z.username=t.username2 WHERE timea BETWEEN '2009-07' AND '2009-12'
GROUP BY `timea`

Answer 10

or
SELECT `timea`,IF(LENGTH(username1)=0,username2,username1), SUM(infoMoney) AS 费用
FROM(
SELECT DATE_FORMAT(infoTime,'%Y-%m') AS `timea`,infoMoney,username AS username1,'' AS username2 FROM info
UNION ALL
SELECT DATE_FORMAT(info2Time,'%Y-%m'),info2Money,'',username AS username2 FROM info2  
) tLEFT JOIN `user` s ON s.username=t.username1 LEFT JOIN `user` z ON z.username=t.username2 WHERE timea BETWEEN '2009-07' AND '2009-12'
GROUP BY `timea`不是标准的SQL语句

Answer 11

感谢！感谢wwwwb，测试通过了！太崇拜你了感谢ACMAIN_CHM

Answer 12

11楼的已测试成功了！这几天在 wwwwb 的影响下，学了不少！

Answer 13

LEFT JOIN `user` s ON s.username=t.username1 and s.jifeiID="搜索条件传来的计费ID"LEFT JOIN `user` z ON z.username=t.username2 and z.jifeiID="搜索条件传来的计费ID"这儿我加了个条件，搜出来的值不是正确

Answer 14

USER：
+-----------------+---------------+-----------------+
| username        | jifeiID       | user_zone_group |
+-----------------+---------------+-----------------+
| a               |             1 | student         | 
| b               |             1 | student         | 
| c               |             2 | gonghui         | 
| d               |             2 | gonghui         | 
| e               |             3 | teacher         | 
+-----------------+---------------+-----------------+
info:
+-----------------+---------------------+-------------+
| username        | infoTime            |  infoMoney  |
+-----------------+---------------------+-------------+
| a               | 2010-11-01 23:00:00 | 1           | 
| b               | 2010-11-02 11:00:00 | 2           | 
| a               | 2010-11-03 23:00:00 | 5           | 
+-----------------+---------------------+-------------+info2:
+-----------------+-------------+---------------------+
| username        | info2Money  | info2Time           |
+-----------------+-------------+---------------------+
| a               | 2           | 0000-00-00 00:00:00 | 
| b               | 2           | 2010-11-01 11:00:00 | 
| c               | 3           | 2010-11-01 09:00:00 | 
| d               | 2           | 2010-11-02 08:00:00 | 
| e               | 2           | 2010-11-02 00:00:00 | 
+-----------------+-------------+---------------------+搜索当ID=2的时候
+------------+-----------+-----------+------------+
| timea      | username1 | username2 | infoMoney  |
+------------+-----------+-----------+------------+
| 2010-11-01 | a         |           |          6 | 
| 2010-11-02 | b         |           |          6 | 
+------------+-----------+-----------+------------+
2 rows in set (0.00 sec)

Answer 15

    -> LEFT JOIN `user` s ON s.usename=t.username1  and s.jifeiID='2'
    -> 
    -> LEFT JOIN `user` z ON z.username=t.username2  and s.jifeiID='2' 
    -> 
    -> WHERE t.timea BETWEEN '2010-11-01' AND '2010-11-02'
    -> GROUP BY t.timea;

Answer 16

从user表中看出：jifeiID=2的只有二个用户：c 和 d 

Answer 17

 (不要高估你的汉语表达能力或者我的汉语理解能力)
   建议你列出你的表结构，并提供测试数据以及基于这些测试数据的所对应正确结果。
   参考一下这个贴子的提问方式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)
   
   这样想帮你的人可以直接搭建和你相同的环境，并在给出方案前进行测试，避免文字描述理解上的误差。   

Answer 18

# phpMyAdmin SQL Dump
# version 2.5.6
# http://www.phpmyadmin.net
#
# 主机: localhost
# 生成日期: 2010 年 11 月 13 日 14:33
# 服务器版本: 5.0.22
# PHP 版本: 5.1.6
# 
# 数据库 : `wuszh`
# # --------------------------------------------------------#
# 表的结构 `user`
#CREATE TABLE `user` (
  `username` varchar(20) default NULL,
  `jifeiID` int(11) default NULL,
  `yongfuzu` varchar(20) default NULL,
  KEY `username` (`username`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;#
# 导出表中的数据 `user`
#INSERT INTO `user` (`username`, `jifeiID`, `yongfuzu`) VALUES ('a', 1, 'student'),
('b', 1, 'student'),
('c', 2, 'gonghui'),
('d', 2, 'gonghui'),
('e', 3, 'teacher');# --------------------------------------------------------#
# 表的结构 `info2`
#CREATE TABLE `info2` (
  `username` varchar(10) default NULL,
  `jifeiID` int(11) NOT NULL,
  `info2Money` varchar(5) default NULL,
  `info2Time` datetime default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;#
# 导出表中的数据 `info2``
#INSERT INTO `info2` (`username`, `jifeiID`, `info2Money`, `info2Time`) VALUES ('a', 1, '2', '2010-11-01 12:00:00'),
('b', 1, '2', '2010-11-01 11:00:00'),
('c', 2, '3', '2010-11-01 09:00:00'),
('d', 2, '2', '2010-11-02 08:00:00'),
('e', 3, '2', '2010-11-02 00:00:00');# --------------------------------------------------------#
# 表的结构 `info`
#CREATE TABLE `info` (
  `username` varchar(20) default NULL,
  `infoTime` datetime default '0000-00-00 00:00:00',
  `infoMoney` varchar(5) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;#
# 导出表中的数据 `info`
#INSERT INTO `info (`username`, `infoTime`, `infoMoney`) VALUES ('a', '2010-11-01 23:00:00', '1'),
('b', '2010-11-02 11:00:00', '2'),
('a', '2010-11-03 23:00:00', '5');

Answer 19

重整下info表数据：INSERT INTO `info` (`username`, `infoTime`, `infoMoney`) VALUES 
('a', '2010-11-01 23:00:00', '1'),
('b', '2010-11-02 11:00:00', '2'),
('a', '2010-11-03 23:00:00', '5'),
('c', '2010-11-01 23:00:00', '4');------------------
求出当时间为：2010-11-01至2010-11-02的并且jifieID=2的（info和info2）的费用，并合计：日期               费用
2010-11-01        6 
2010-11-02        2
----------------------
合计                 8

Answer 20

纠正24楼，刚算错了，应该是这样：求出当时间为：2010-11-01至2010-11-02的并且jifieID=2的（info和info2）的费用，并合计：日期 费用
2010-11-01 7  
2010-11-02 2
----------------------
合计 9

Answer 21

mysql> select * from user;
+----------+---------+----------+
| username | jifeiID | yongfuzu |
+----------+---------+----------+
| a        |       1 | student  |
| b        |       1 | student  |
| c        |       2 | gonghui  |
| d        |       2 | gonghui  |
| e        |       3 | teacher  |
+----------+---------+----------+
5 rows in set (0.00 sec)mysql> select * from info;
+----------+---------------------+-----------+
| username | infoTime            | infoMoney |
+----------+---------------------+-----------+
| a        | 2010-11-01 23:00:00 | 1         |
| b        | 2010-11-02 11:00:00 | 2         |
| a        | 2010-11-03 23:00:00 | 5         |
| c        | 2010-11-01 23:00:00 | 4         |
+----------+---------------------+-----------+
4 rows in set (0.00 sec)mysql> select * from info2;
+----------+---------+------------+---------------------+
| username | jifeiID | info2Money | info2Time           |
+----------+---------+------------+---------------------+
| a        |       1 | 2          | 2010-11-01 12:00:00 |
| b        |       1 | 2          | 2010-11-01 11:00:00 |
| c        |       2 | 3          | 2010-11-01 09:00:00 |
| d        |       2 | 2          | 2010-11-02 08:00:00 |
| e        |       3 | 2          | 2010-11-02 00:00:00 |
+----------+---------+------------+---------------------+
5 rows in set (0.00 sec)mysql> select date(infoTime) as `日期`,sum(infoMoney) as `费用`
    -> from (
    ->  Select infoTime,infoMoney from info inner join user using(username) Where jifeiID=2 and infoTime between '2010-11-01 00:00:00' and '2010-11-02 23:59:59'
    ->  union all
    ->  select info2Time,info2Money From info2 where jifeiID=2 and info2Time Between '2010-11-01 00:00:00' And '2010-11-02 23:59:59'
    -> ) t
    -> group by date(infoTime);
+------------+------+
| 日期       | 费用 |
+------------+------+
| 2010-11-01 |    7 |
| 2010-11-02 |    2 |
+------------+------+
2 rows in set (0.08 sec)mysql>

Answer 22

非常感谢ACMAIN_CHM，测试正常！！

Answer 23

狼头大哥，请问下，想给每行加个顺序号，怎么加？1   
2
3
....mysql> select NULL as `序号`,date(infoTime) as `日期`,sum(infoMoney) as `费用`

结果出不来数字。

Answer 24

自己参考 http://blog.csdn.net/ACMAIN_CHM/archive/2009/04/20/4095531.aspx
MySQL中的ROWNUM的实现这个贴子中的问题对我来说已经解决了。

Answer 25

SET @num=0;
SELECT @num:=@num+1,IF(LENGTH(username1)=0,username2,username1) AS newuser,`timea`, SUM(infoMoney) AS 费用
FROM(
SELECT DATE_FORMAT(infoTime,'%Y-%m-%d') AS `timea`,infoMoney,username AS username1,'' AS username2 FROM info
UNION ALL
SELECT DATE_FORMAT(info2Time,'%Y-%m-%d'),info2Money,'',username AS username2 FROM info2   
) tLEFT JOIN `user` s ON s.username=t.username1  LEFT JOIN `user` z ON z.username=t.username2  
WHERE s.jifeiID=2 OR z.jifeiID=2
GROUP BY `timea`