te')); return $arr; } /* 遍历用户所有主题 * @param $uid 用户ID * @param int $page 页数 * @param int $pagesize 每页记录条数 * @param bool $desc 排序方式 TRUE降序 FALSE升序 * @param string $key 返回的数组用那一列的值作为 key * @param array $col 查询哪些列 */ function thread_tid_find_by_uid($uid, $page = 1, $pagesize = 1000, $desc = TRUE, $key = 'tid', $col = array()) { if (empty($uid)) return array(); $orderby = TRUE == $desc ? -1 : 1; $arr = thread_tid__find($cond = array('uid' => $uid), array('tid' => $orderby), $page, $pagesize, $key, $col); return $arr; } // 遍历栏目下tid 支持数组 $fid = array(1,2,3) function thread_tid_find_by_fid($fid, $page = 1, $pagesize = 1000, $desc = TRUE) { if (empty($fid)) return array(); $orderby = TRUE == $desc ? -1 : 1; $arr = thread_tid__find($cond = array('fid' => $fid), array('tid' => $orderby), $page, $pagesize, 'tid', array('tid', 'verify_date')); return $arr; } function thread_tid_delete($tid) { if (empty($tid)) return FALSE; $r = thread_tid__delete(array('tid' => $tid)); return $r; } function thread_tid_count() { $n = thread_tid__count(); return $n; } // 统计用户主题数 大数量下严谨使用非主键统计 function thread_uid_count($uid) { $n = thread_tid__count(array('uid' => $uid)); return $n; } // 统计栏目主题数 大数量下严谨使用非主键统计 function thread_fid_count($fid) { $n = thread_tid__count(array('fid' => $fid)); return $n; } ?>sql - Can the number of intermediate results rows be limited in a simple multi-table join? - Stack Overflow

科技改变生活-雨落星辰 - 所有的伟大,都源于一个勇敢的开始

    最新消息：雨落星辰是一个专注网站SEO优化、网站SEO诊断、搜索引擎研究、网络营销推广、网站策划运营及站长类的自媒体原创博客
    你的位置： 首页>programmer>sql - Can the number of intermediate results rows be limited in a simple multi-table join? - Stack Overflow

    sql - Can the number of intermediate results rows be limited in a simple multi-table join? - Stack Overflow

    programmeradmin3浏览0评论

    I'm trying to use a LEFT JOIN to pull information into one table from two others. It works for most instances because each table has at most one match per row of the primary table. However, in some instances the third table has more than one match but the data column from that table is the same for each match.

    A GROUP BY at the end removes the duplicates; but is there a smarter way of somehow adding something to the join with table t3 to limit it to 1 row?

    Thank you.

    create table t1 (doc_id, track_id, timestamp);
create table t2 (doc_id, timestamp, starttime);
create table t3 (track_id, chapter, title);

insert into t1 
values (1, '05009', 173), (2, '07290', 174), (3, '08002', 175); 
insert into t2 
values (1, 173, '02:05'), (2, 174, '05:17'), (3, 175, '10:35'); 
insert into t3 
values ('08002', 1, 'title 08002'), ('05009', 2, 'title 05009'),('05009', 3, 'title 05009');

select
    t1.*, t2.starttime, t3.title
from
    t1
left join
    t2 on (t1.doc_id,t1.timestamp) = (t2.doc_id,t2.timestamp)
left join
    t3 on t1.track_id = t3.track_id;

/*
doc_id  track_id  timestamp  starttime  title      
------  --------  ---------  ---------  -----------
1       05009     173        02:05      title 05009
1       05009     173        02:05      title 05009
2       07290     174        05:17      NULL       
3       08002     175        10:35      title 08002
*/

select
    t1.*, t2.starttime, t3.title
from
    t1
left join
    t2 on (t1.doc_id,t1.timestamp) = (t2.doc_id,t2.timestamp)
left join
    t3 on t1.track_id = t3.track_id
group by
    t1.track_id;

/*
doc_id  track_id  timestamp  starttime  title      
------  --------  ---------  ---------  -----------
1       05009     173        02:05      title 05009
2       07290     174        05:17      NULL       
3       08002     175        10:35      title 08002
*/

    I'm trying to use a LEFT JOIN to pull information into one table from two others. It works for most instances because each table has at most one match per row of the primary table. However, in some instances the third table has more than one match but the data column from that table is the same for each match.

    A GROUP BY at the end removes the duplicates; but is there a smarter way of somehow adding something to the join with table t3 to limit it to 1 row?

    Thank you.

    create table t1 (doc_id, track_id, timestamp);
create table t2 (doc_id, timestamp, starttime);
create table t3 (track_id, chapter, title);

insert into t1 
values (1, '05009', 173), (2, '07290', 174), (3, '08002', 175); 
insert into t2 
values (1, 173, '02:05'), (2, 174, '05:17'), (3, 175, '10:35'); 
insert into t3 
values ('08002', 1, 'title 08002'), ('05009', 2, 'title 05009'),('05009', 3, 'title 05009');

select
    t1.*, t2.starttime, t3.title
from
    t1
left join
    t2 on (t1.doc_id,t1.timestamp) = (t2.doc_id,t2.timestamp)
left join
    t3 on t1.track_id = t3.track_id;

/*
doc_id  track_id  timestamp  starttime  title      
------  --------  ---------  ---------  -----------
1       05009     173        02:05      title 05009
1       05009     173        02:05      title 05009
2       07290     174        05:17      NULL       
3       08002     175        10:35      title 08002
*/

select
    t1.*, t2.starttime, t3.title
from
    t1
left join
    t2 on (t1.doc_id,t1.timestamp) = (t2.doc_id,t2.timestamp)
left join
    t3 on t1.track_id = t3.track_id
group by
    t1.track_id;

/*
doc_id  track_id  timestamp  starttime  title      
------  --------  ---------  ---------  -----------
1       05009     173        02:05      title 05009
2       07290     174        05:17      NULL       
3       08002     175        10:35      title 08002
*/
    • sql
    Share Improve this question edited Feb 18 at 5:11 marc_s 755k184 gold badges1.4k silver badges1.5k bronze badges asked Feb 17 at 23:07 GaryGary 2,8712 gold badges17 silver badges41 bronze badges
    Add a comment  | 

    2 Answers 2

    Reset to default 1

    Don't join with t3, join with a subquery that gets the unique values you care about. So change

    left join t3

    to

    left join (
    SELECT DISTINCT track_id, title
    FROM t3
) AS t3

    If the title is always the same regardless of the chapter, this also suggests that your database is not properly normalized. t3 should be split into two tables:

    CREATE TABLE t3a (track_id, title, PRIMARY KEY (track_id));
CREATE TABLE t3b (track_id, chapter, 
    PRIMARY KEY (track_id, chapter), 
    FOREIGN KEY (track_id) REFERENCES t3a (track_id));

    Then you would just join with t3a to get your desired result.

    After some additional thought, a CTE might be a good approach; because, even though a multiple-table join may look concise, the joins appear to still be happening in steps; and it appears that this CTE selects the distinct only from among those that match rather than selecitng distinct from all of t3 to start.

    with 
t1j3 as
(
select
  distinct t3.track_id, t3.title
from 
  t3 
inner join
  t1
on
  t3.track_id = t1.track_id
)
select
  t1.*, t2.starttime, t1j3.title
from
   t1
left join
   t2
on
   (t1.doc_id,t1.timestamp) = (t2.doc_id,t2.timestamp)
left join
   t1j3
on 
   t1.track_id = t1j3.track_id
;
/*
doc_id  track_id  timestamp  starttime  title      
------  --------  ---------  ---------  -----------
1       05009     173        02:05      title 05009
2       07290     174        05:17      NULL       
3       08002     175        10:35      title 08002
*/

    与本文相关的文章

    发布评论

    评论列表(0)

    1. 暂无评论