mysql多表复杂业务查询(如下),应该怎样写查询语句?

WBOY
Release: 2016-06-06 20:46:47
Original
1090 people have browsed it

需要查询A、B两张表:

表A table_a:涉及到两个字段 a_id , a_date

<code>| a_id      |    a_date  | ...  | ...  |
| :-------- | --------:  | :--: | :--: |
| 1         | 2014-03-12 | ...  | ...  |
| 2         | 2014-03-15 | ...  | ...  |
| 3         | 2013-08-06 | ...  | ...  |
| 4         | 2013-10-18 | ...  | ...  |
| 5         | 2012-04-15 | ...  | ...  |
| 6         | 2012-06-22 | ...  | ...  |
| ...       | ...        | ...  | ...  |
</code>
Copy after login
Copy after login

表B table_b:涉及到两个字段 b_id , b_category

<code>| b_id      |  b_category | ...  | ...  |
| :-------- | ----------: | :--: | :--: |
| 1         | tom         | ...  | ...  |
| 2         | jerry       | ...  | ...  |
| 3         | tom         | ...  | ...  |
| 4         | jerry       | ...  | ...  |
| 5         | tom         | ...  | ...  |
| 6         | tom         | ...  | ...  |
| ...       | ...         | ...  | ...  |
</code>
Copy after login
Copy after login

需求:
1.从 table_b 查询字段名 b_category 等于 tom 的字段 b_id 的值
2.将查得得值关联到 table_a 查询该 a_id 对应的 a_date 的值
3.对 a_date 截取前4位(年份),然后去重,并且按照倒序排列,输出。

回复内容:

需要查询A、B两张表:

表A table_a:涉及到两个字段 a_id , a_date

<code>| a_id      |    a_date  | ...  | ...  |
| :-------- | --------:  | :--: | :--: |
| 1         | 2014-03-12 | ...  | ...  |
| 2         | 2014-03-15 | ...  | ...  |
| 3         | 2013-08-06 | ...  | ...  |
| 4         | 2013-10-18 | ...  | ...  |
| 5         | 2012-04-15 | ...  | ...  |
| 6         | 2012-06-22 | ...  | ...  |
| ...       | ...        | ...  | ...  |
</code>
Copy after login
Copy after login

表B table_b:涉及到两个字段 b_id , b_category

<code>| b_id      |  b_category | ...  | ...  |
| :-------- | ----------: | :--: | :--: |
| 1         | tom         | ...  | ...  |
| 2         | jerry       | ...  | ...  |
| 3         | tom         | ...  | ...  |
| 4         | jerry       | ...  | ...  |
| 5         | tom         | ...  | ...  |
| 6         | tom         | ...  | ...  |
| ...       | ...         | ...  | ...  |
</code>
Copy after login
Copy after login

需求:
1.从 table_b 查询字段名 b_category 等于 tom 的字段 b_id 的值
2.将查得得值关联到 table_a 查询该 a_id 对应的 a_date 的值
3.对 a_date 截取前4位(年份),然后去重,并且按照倒序排列,输出。

<code class="lang-sql">select distinct substr(a_date,1,4) result_date
from table_a inner join table_b on a_id = b_id
where b_category = "tom"
order by result_date desc
</code>
Copy after login

应该用join吧

Related labels:
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template