MariaDB [turqus]> show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`x` varchar(20) DEFAULT NULL,
`y` varchar(20) DEFAULT NULL,
`z` varchar(20) DEFAULT NULL,
`xy` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1
MariaDB [turqus]> show create table t2\G
*************************** 1. row ***************************
Table: t2
Create Table: CREATE TABLE `t2` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`col` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1
SELECT full FROM (select concat(x,y) as full from t1) as tx inner join
(SELECT col2 from (SELECT col as col2 from t2) as ty) as ty2 where col2 = full;
Solution 1:
select distinctrow z from t1 as xx1
inner join
(SELECT full FROM (
select concat(x,y) as full from t1 as xxxxxxxx
) as tx
inner join
(SELECT col2 from (SELECT col as col2 from t2) as ty2) as ttt444 where col2 = full) as xxxx33
inner join
(select concat(x,y) from t1) as xwr
inner join
(SELECT col from t2) as brwer
where col = full and full=concat(x,y);
To samo zapytanie, ale napisane na widoku (view):
MariaDB [turqus]> show create view view1\G
*************************** 1. row ***************************
View: view1
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=``@`localhost` SQL SECURITY DEFINER VIEW `view1` AS (select concat(`t1`.`x`,`t1`.`y`) AS `full` from `t1`)
character_set_client: utf8
collation_connection: utf8_unicode_ci
MariaDB [turqus]> show create view view2\G
*************************** 1. row ***************************
View: view2
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=``@`localhost` SQL SECURITY DEFINER VIEW `view2` AS (select `t2`.`col` AS `col` from `t2`)
character_set_client: utf8
collation_connection: utf8_unicode_ci
MariaDB [turqus]> show create view view3\G
*************************** 1. row ***************************
View: view3
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=``@`localhost` SQL SECURITY DEFINER VIEW `view3` AS (select `view1`.`full` AS `full` from (`view1` join `view2`) where (`view2`.`col` = `view1`.`full`))
character_set_client: utf8
collation_connection: utf8_unicode_ci
select full from view1 inner join (view2)
where (view2.col = view1.full);
Solution 2:
select z from t1 inner join (view3) where (view3.full)=concat(x,y);
Dla takich danych w tabelach:
MariaDB [turqus]> select * from t1;
+----+-----------+-----------+------------+-----------+
| id | x | y | z | xy |
+----+-----------+-----------+------------+-----------+
| 1 | pear | apple | strawberry | salat |
| 2 | tomato | potato | blueberry | juice |
| 3 | cherry | lemon | nut | breakfast |
| 4 | calendula | dandelion | leaf | decoction |
+----+-----------+-----------+------------+-----------+
MariaDB [turqus]> select * from t2;
+----+--------------------+
| id | col |
+----+--------------------+
| 1 | pearapple |
| 2 | tomatopotato |
| 3 | cherry |
| 4 | calenduladandelion |
+----+--------------------+
rezultat to:
+------------+
| z |
+------------+
| strawberry |
| blueberry |
| leaf |
+------------+
Jeśli kod jest źle pofarmatowany, to pofarmatuj jak chcesz.
Link1, link2, link3, link4, link5, link6, link7, link8.
Helpful: link1, link2.