tab1是交费表,tab2是开发票的,bj=1的是已开的
这是二个表的详细情况:mysql> select * from tab1 order by name;
+----+------+--------+---------------------+----+
| id | name | blance | date | bj |
+----+------+--------+---------------------+----+
| 1 | wang | 10.00 | 2012-06-01 00:00:00 | 1 |
| 2 | wang | 15.00 | 2012-06-05 00:00:00 | 1 |
| 3 | wu | 5.00 | 2012-06-05 00:00:00 | 1 |
| 4 | wu | 50.00 | 2012-06-05 00:00:00 | 1 |
| 5 | wang | 10.00 | 2012-06-12 00:00:00 | 0 |
| 6 | wang | 20.00 | 2012-06-16 00:00:00 | 0 |
| 7 | wu | 5.00 | 2012-06-14 00:00:00 | 0 |
+----+------+--------+---------------------+----+7 rows in set (0.00 sec)mysql> select * from tab2;
+----+------+---------+------------+--------+
| id | name | balance | date | enter |
+----+------+---------+------------+--------+
| 1 | wang | 15.00 | 2012-06-07 | admin |
| 2 | wu | 55.00 | 2012-06-05 | admin2 |
+----+------+---------+------------+--------+
2 rows in set (0.00 sec)
现在要得到如下结果:
用户名 费用 日期 操作员
-------------------------------------------------------
交费:
wang | 10.00 | 2012-06-01 00:00:00 |
wang | 15.00 | 2012-06-05 00:00:00 |
-------------------------------------------------------
开发票: wang | 25.00 | 2012-06-07 | admin |
-------------------------------------------------------
交费: wang | 10.00 | 2012-06-12 00:00:00 |
wang | 20.00 | 2012-06-16 00:00:00 | wu | 5.00 | 2012-06-05 00:00:00 |
wu | 50.00 | 2012-06-05 00:00:00 |
-------------------------------------------------------
wu | 55.00 | 2012-06-05 | admin2 |
------------------------------------------------------
wu | 5.00 | 2012-06-14 00:00:00 |
怎么得出的?
union all
select * from tb2;
+----+------+--------+---------------------+----+
| id | name | blance | date | bj |
+----+------+--------+---------------------+----+
| 1 | wang | 10.00 | 2012-06-01 00:00:00 | 1 |
| 2 | wang | 15.00 | 2012-06-05 00:00:00 | 1 |
| 3 | wu | 5.00 | 2012-06-05 00:00:00 | 1 |
| 4 | wu | 50.00 | 2012-06-05 00:00:00 | 1 |
| 5 | wang | 10.00 | 2012-06-12 00:00:00 | 0 |
| 6 | wang | 20.00 | 2012-06-16 00:00:00 | 0 |
| 7 | wu | 5.00 | 2012-06-14 00:00:00 | 0 |
+----+------+--------+---------------------+----+
7 rows in set (0.00 sec)mysql> mysql> select * from tab2;
+----+------+---------+------------+--------+-----+
| id | name | balance | date | enter | tid |
+----+------+---------+------------+--------+-----+
| 1 | wang | 25.00 | 2012-06-07 | admin | 1,2 |
| 2 | wu | 55.00 | 2012-06-05 | admin2 | 3,4 |
+----+------+---------+------------+--------+-----+
2 rows in set (0.00 sec)
-----------------------------------
开发票
姓名 金额 日期
wang 25 2012-06-07明细
wang 10 2012-06-01
wang 15 2012-06-05
---------------------------------------
开发票
wu 55 2012-06-05
明细:
wu 5 2012-06-05
wu 50 2012-06-05
--------------------------------------
wang 10 2012-06-12
wang 20 2012-06-16
wu 5 2012-06-14
select name,balance,date,3 as bz1,bz from (
SELECT name,balance,date,2 as bz from tab2
union all
select name,blance,date,bj from tab1 where bj=1
order by name,bz desc)
union all
select name,blance,date,bj,-1 from tab1 where bj=0order by bz1 desc,name,bz desc
SELECT name,balance,date,2 as bz from tab2
union all
select name,blance,date,bj from tab1 where bj=1
order by name,bz desc) aa
union all
select name,blance,date,bj,-1 from tab1 where bj=0order by bz1 desc,name,bz desc
#1248 - Every derived table must have its own alias
追加:
select name,blance,date,bj,-1 from tab1 where bj=0//这句想全取出来,字段不一一列出来,