表一
product c
==========================================
sample_1 Line
sample_1 LLk
sample_1 LLk
sample_1 LLk
sample_1 LLk
sample_2 LLk
sample_3 LLk
sample_2 LLk
sample_3 LLk
sample_2 Line
加表二
a h r
==========================================
LLk sample_1 gw
LLk sample_2 okp
LLk sample_3 ijof
Line xe rr3
LLk sample_4 gr2
Line xx2 o3
最终会是这样。
基于表2的搜索,其中a = LLk
这是从表1和表2生成的表3
product counts
============================
sample_1 4
sample_2 1
sample_3 2
sample_4 0
此问题的详细链接
http://pastebin.com/ds0iS4GU
最佳答案
这将为您提供所需的答案select h, count(t1.product) from t2 LEFT JOIN t1 ON t1.c=t2.a and t1.product=t2.h where t2.a='LLk' group by t2.h