代码之家  ›  专栏  ›  技术社区  ›  Florian

在同一张桌子上数

  •  1
  • Florian  · 技术社区  · 16 年前

    我的SQL语句有问题:

    表结构:

    CREATE TABLE "tags" (
      "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
      "page_id" INTEGER NOT NULL,
      "title" TEXT NOT NULL
    );
    

    现在我想从一个页面中选择所有标签,以及这个标签在整个数据库中的频率。我有以下SQL语句:

    SELECT title, title as t,
    (SELECT COUNT(*) FROM tags WHERE title=t) as count
    FROM tags WHERE page_id=42 ORDER BY count DESC, title
    COLLATE NOCASE ASC;
    

    但是我从PHP中的SQLite3驱动程序得到的错误消息是:

    no such column: t
    

    3 回复  |  直到 16 年前
        1
  •  2
  •   Bharat    16 年前

    试试这个

    SELECT title, 
    (SELECT COUNT(*) FROM tags WHERE title=t.title) as count
    FROM tags t 
    WHERE page_id=42 
    ORDER BY count DESC, title
    COLLATE NOCASE ASC;
    
        2
  •  1
  •   santa    16 年前

    SELECT title, COUNT(*) as freq
    FROM tags
    WHERE page_id=42
    GROUP BY title
    ORDER BY freq DESC, title
    
        3
  •  0
  •   Kashif    16 年前
    select title, count(*) as frequency
    from tags
    where page_id = 42
    group by title
    order by count(*) desc
    

    如果您希望标签出现在多个

    select title, count(*) as frequency
    from tags
    where page_id = 42
    group by title
    having count(*) > 1
    order by count(*) desc