如何通过sql查询出如下结果(不要用存储过程)
目前有2张表A表和B表;B表中的数据是固定不变的;但A表中的数据是可变的,但只可能出现2种情况;1.A表中数据可能有如下两种情况,为方便描述,记为A1表和A2表;
A1表中数据内容:
a 1 1
b 1 2
A2表中数据内容:
b 1 2
B表中数据内容:
a 0 0
2. 现通过A和B表进行联表查询,
(1)如果查询时,发现A表中的内容是A1时,那么希望查询结果为:
a11
b12
(2)如果查询时,发现A表中的内容是A2时,那么希望查询结果为:
a 00
b 12
如果用sql查询(不要存储过程)实现2的结果,该怎么写呢?谢谢!
目前有2张表A表和B表;B表中的数据是固定不变的;但A表中的数据是可变的,但只可能出现2种情况;
1.A表中数据可能有如下两种情况,为方便描述,记为A1表和A2表;
A1表中数据内容:
a 1 1
b 1 2
A2表中数据内容:
b 1 2
B表中数据内容:
a 0 0
2. 现通过A和B表进行联表查询,
(1)如果查询时,发现A表中的内容是A1时,那么希望查询结果为:
a11
b12
(2)如果查询时,发现A表中的内容是A2时,那么希望查询结果为:
a 00
b 12
如果用sql查询(不要存储过程)实现2的结果,该怎么写呢?谢谢! 夏寥寥 发表于 2016-05-27 17:39 static/image/common/back.gif
目前有2张表A表和B表;B表中的数据是固定不变的;但A表中的数据是可变的,但只可能出现2种情况;
1.A表中 ...
如果可以用动态语句可以试试这个:mysql> select * from a;
+----+----+----+
| c1 | c2 | c3 |
+----+----+----+
| a| 1| 1|
| b| 1| 2|
+----+----+----+
2 rows in set
mysql> select * from b;
+----+----+----+
| c1 | c2 | c3 |
+----+----+----+
| a| 0| 0|
+----+----+----+
1 row in set
mysql> select @count_a := count(*) from a;
select @str := if (@count_a = 2, 'select * from a', 'select * from b union all select * from a');
prepare st from @str;
execute st;
deallocate prepare st;
+----------------------+
| @count_a := count(*) |
+----------------------+
| 2 |
+----------------------+
1 row in set
+-------------------------------------------------------------------------------------------+
| @str := if (@count_a = 2, 'select * from a', 'select * from b union all select * from a') |
+-------------------------------------------------------------------------------------------+
| select * from a |
+-------------------------------------------------------------------------------------------+
1 row in set
Query OK, 0 rows affected
Statement prepared
+----+----+----+
| c1 | c2 | c3 |
+----+----+----+
| a| 1| 1|
| b| 1| 2|
+----+----+----+
2 rows in set
Query OK, 0 rows affected
mysql>
mysql> select * from a;
+----+----+----+
| c1 | c2 | c3 |
+----+----+----+
| b| 1| 2|
+----+----+----+
1 row in set
mysql> select * from b;
+----+----+----+
| c1 | c2 | c3 |
+----+----+----+
| a| 0| 0|
+----+----+----+
1 row in set
mysql> select @count_a := count(*) from a;
select @str := if (@count_a = 2, 'select * from a', 'select * from b union all select * from a');
prepare st from @str;
execute st;
deallocate prepare st;
+----------------------+
| @count_a := count(*) |
+----------------------+
| 1 |
+----------------------+
1 row in set
+-------------------------------------------------------------------------------------------+
| @str := if (@count_a = 2, 'select * from a', 'select * from b union all select * from a') |
+-------------------------------------------------------------------------------------------+
| select * from b union all select * from a |
+-------------------------------------------------------------------------------------------+
1 row in set
Query OK, 0 rows affected
Statement prepared
+----+----+----+
| c1 | c2 | c3 |
+----+----+----+
| a| 0| 0|
| b| 1| 2|
+----+----+----+
2 rows in set
Query OK, 0 rows affected
mysql> 不然,可以用这个试试:
mysql> select * from a;
+----+----+----+
| c1 | c2 | c3 |
+----+----+----+
| b| 1| 2|
+----+----+----+
1 row in set
mysql> select * from b;
+----+----+----+
| c1 | c2 | c3 |
+----+----+----+
| a| 0| 0|
+----+----+----+
1 row in set
mysql> select c1, c2, c3
from
(
select *, 'b' as tb, (select count(*) from a) as cnt from b
union all
select *, 'a', (select count(*) from a) from a
) as tmp
where (tb, cnt) in (('b', 1), ('a', 1), ('a', 2));
+----+----+----+
| c1 | c2 | c3 |
+----+----+----+
| a| 0| 0|
| b| 1| 2|
+----+----+----+
2 rows in set
mysql>
mysql> select * from a;
+----+----+----+
| c1 | c2 | c3 |
+----+----+----+
| a| 1| 1|
| b| 1| 2|
+----+----+----+
2 rows in set
mysql> select * from b;
+----+----+----+
| c1 | c2 | c3 |
+----+----+----+
| a| 0| 0|
+----+----+----+
1 row in set
mysql> select c1, c2, c3
from
(
select *, 'b' as tb, (select count(*) from a) as cnt from b
union all
select *, 'a', (select count(*) from a) from a
) as tmp
where (tb, cnt) in (('b', 1), ('a', 1), ('a', 2));
+----+----+----+
| c1 | c2 | c3 |
+----+----+----+
| a| 1| 1|
| b| 1| 2|
+----+----+----+
2 rows in set
mysql>
select B.column1,B.column2,B.column3from B
join A on B.colum1 = A.column1
union
select A.column1, nvl(B.column2,A.column2),nvl(B.column3,A.column3)from A
left join B on B.colum1 = A.column1 select B.column1,B.column2,B.column3from B
join A on B.colum1 = A.column1
union all
select A.column1, nvl(B.column2,A.column2),nvl(B.column3,A.column3)from A
left join B on B.colum1 = A.column1 回复 3# seesea2517
谢谢大神!两种方法都能查到相应的结果!:mrgreen:
回复 6# 夏寥寥
客气,不是什么大神,谬赞。你的方法也不错啊,nvl 在 mysql 里我改成 ifnull 来测试效果不错。我不知道你这两个表还有数据相关的,想不到用连接的这个方法了。 回复 7# seesea2517
我写错了!nvl是oracle里的,ifnull才是mysql中的!:mrgreen: 谢谢!
页:
[1]