복붙노트

[SQL] 콤마 구별 값 MySQL의 PHP 선택 횟수 데이터 (태그)를 구분

SQL

콤마 구별 값 MySQL의 PHP 선택 횟수 데이터 (태그)를 구분

어떻게 쉼표 MySQL의 값을 분리으로 저장된 데이터에서 고유 값의 수를 선택할 수 있습니다? 나는 결국 출력 MySQL에서 데이터를 PHP를 사용할 것입니다.

거기에 무엇, 각 게시물에 대한 태그입니다. 그래서 결국, 나는 길의 유래는 다음과 같이, 그것의 태그 않는 것처럼 출력 데이터를 시도하고있다 :

tag-name x 5

이 같은 테이블 외모의 데이터 (컨텐츠에 대한 죄송하지만 조리법에 대한 사이트의) 방법이다.

"postId"    "tags"                                  "category-code"
"1"         "pho,pork"                              "1"
"2"         "fried-rice,chicken"                    "1"
"3"         "fried-rice,pork"                       "1"
"4"         "chicken-calzone,chicken"               "1"
"5"         "fettuccine,chicken"                    "1"
"6"         "spaghetti,chicken"                     "1"
"7"         "spaghetti,chorizo"                     "1"
"8"         "spaghetti,meat-balls"                  "1"
"9"         "miso-soup"                             "1"
"10"        "chanko-nabe"                           "1"
"11"        "chicken-manchurian,chicken,manchurain" "1"
"12"        "pork-manchurian,pork,manchurain"       "1"
"13"        "sweet-and-sour-pork,pork"              "1"
"14"        "peking-duck,duck"                      "1"

산출

chicken             5 // occurs 5 time in the data above
pork                4 // occurs 4 time in the data above
spaghetti           3 // an so on
fried-rice          2
manchurian          2
pho                 1
chicken-calzone     1
fettuccine          1
chorizo             1
meat-balls          1
miso-soup           1
chanko-nabe         1
chicken-manchurian  1
pork-manchurian     1
sweet-n-sour-pork   1
peking-duck         1
duck                1

내가 거기에있는 모든 고유 값의 수를 선택하려고하지만, 그것의 쉼표로 데이터를 분리하기 때문에,이 작업을 수행 할 수있는 방법은없는 것처럼 보인다 있어요. 별개의 뜻이 작동하지 않을 선택합니다.

당신도 MySQL의에 좋은 방법을 생각하거나 내가했던 방식과 같은 출력을 얻기 위해 PHP를 사용 할 수 있나요?

해결법

  1. ==============================

    1.난 정말 당신이 쉼표로 구분 된 값을 가질 수 있으므로 많은 숫자로, 숫자를 포함하는 테이블을 생성하지 않고 행의 목록을 쉼표로 구분 된 값의 수평 목록을 변환하는 방법을 모르겠어요. 이 테이블을 만들 수 있습니다, 여기 내 대답입니다 :

    난 정말 당신이 쉼표로 구분 된 값을 가질 수 있으므로 많은 숫자로, 숫자를 포함하는 테이블을 생성하지 않고 행의 목록을 쉼표로 구분 된 값의 수평 목록을 변환하는 방법을 모르겠어요. 이 테이블을 만들 수 있습니다, 여기 내 대답입니다 :

    SELECT 
  SUBSTRING_INDEX(SUBSTRING_INDEX(all_tags, ',', num), ',', -1) AS one_tag,
  COUNT(*) AS cnt
FROM (
  SELECT
    GROUP_CONCAT(tags separator ',') AS all_tags,
    LENGTH(GROUP_CONCAT(tags SEPARATOR ',')) - LENGTH(REPLACE(GROUP_CONCAT(tags SEPARATOR ','), ',', '')) + 1 AS count_tags
  FROM test
) t
JOIN numbers n
ON n.num <= t.count_tags
GROUP BY one_tag
ORDER BY cnt DESC;

    보고:

    +---------------------+-----+
| one_tag             | cnt |
+---------------------+-----+
| chicken             |   5 |
| pork                |   4 |
| spaghetti           |   3 |
| fried-rice          |   2 |
| manchurain          |   2 |
| pho                 |   1 |
| chicken-calzone     |   1 |
| fettuccine          |   1 |
| chorizo             |   1 |
| meat-balls          |   1 |
| miso-soup           |   1 |
| chanko-nabe         |   1 |
| chicken-manchurian  |   1 |
| pork-manchurian     |   1 |
| sweet-and-sour-pork |   1 |
| peking-duck         |   1 |
| duck                |   1 |
+---------------------+-----+
17 rows in set (0.01 sec)

    의는 스키마를 구축하자

    CREATE TABLE test (
    id INT PRIMARY KEY,
    tags VARCHAR(255)
);

INSERT INTO test VALUES
    ("1",         "pho,pork"),
    ("2",         "fried-rice,chicken"),
    ("3",         "fried-rice,pork"),
    ("4",         "chicken-calzone,chicken"),
    ("5",         "fettuccine,chicken"),
    ("6",         "spaghetti,chicken"),
    ("7",         "spaghetti,chorizo"),
    ("8",         "spaghetti,meat-balls"),
    ("9",         "miso-soup"),
    ("10",        "chanko-nabe"),
    ("11",        "chicken-manchurian,chicken,manchurain"),
    ("12",        "pork-manchurian,pork,manchurain"),
    ("13",        "sweet-and-sour-pork,pork"),
    ("14",        "peking-duck,duck");

    우리가 일을 GROUP_CONCAT를 사용, 그래서 우리는 한 줄에 모든 태그와 함께 작동합니다 :

    SELECT GROUP_CONCAT(tags SEPARATOR ',') FROM test;

    모든 태그는 쉼표로 구분하여 반환합니다 :

    모든 태그를 계산하기 위해, 우리는 태그의 전체 목록의 길이를 얻을, 우리는 아무것도함으로써를 교체 한 후 태그의 전체 목록의 길이를 제거합니다. 세퍼레이터 두 값 사이 따라 우리는 1을 추가한다.

    SELECT LENGTH(GROUP_CONCAT(tags SEPARATOR ',')) - LENGTH(REPLACE(GROUP_CONCAT(tags SEPARATOR ','), ',', '')) + 1 AS count_tags
FROM test;

    보고:

    +------------+
| count_tags |
+------------+
|         28 |
+------------+
1 row in set (0.00 sec)

    우리가 얻을 수있는 SUBSTRING_INDEX 기능을 사용

    -- returns the string until the 2nd delimiter\'s occurrence from left to right: a,b
SELECT SUBSTRING_INDEX('a,b,c', ',', 2);

-- return the string until the 1st delimiter, from right to left: c
SELECT SUBSTRING_INDEX('a,b,c', ',', -1);

-- we need both to get: b (with 2 being the tag number)
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('a,b,c', ',', 2), ',', -1);

    같은 논리로, 우리의 목록에 3 태그를 얻기 위해, 우리는 사용

    SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(tags SEPARATOR ','), ',', 3), ',', -1)
FROM test;

    보고:

    +-------------------------------------------------------------------------------------+
| SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(tags SEPARATOR ','), ',', 3), ',', -1) |
+-------------------------------------------------------------------------------------+
| fried-rice                                                                          |
+-------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

    내 생각은 조금 까다 롭습니다 :

    우리는 태그의 최대 수 1까지의 모든 수를 포함하는 테이블을 생성합니다 그래서 당신은 당신의 목록에있을 수 있습니다. 당신은 1M의 값을 가질 수 있다면, 1 ~ 1,000,000 1M 항목을 만들 수 있습니다. 100 개 태그의 경우이 될 것입니다 :

    CREATE TABLE numbers (
  num INT PRIMARY KEY
);

INSERT INTO numbers VALUES
    ( 1 ), ( 2 ), ( 3 ), ( 4 ), ( 5 ), ( 6 ), ( 7 ), ( 8 ), ( 9 ), ( 10 ), 
    ( 11 ), ( 12 ), ( 13 ), ( 14 ), ( 15 ), ( 16 ), ( 17 ), ( 18 ), ( 19 ), ( 20 ), 
    ( 21 ), ( 22 ), ( 23 ), ( 24 ), ( 25 ), ( 26 ), ( 27 ), ( 28 ), ( 29 ), ( 30 ), 
    ( 31 ), ( 32 ), ( 33 ), ( 34 ), ( 35 ), ( 36 ), ( 37 ), ( 38 ), ( 39 ), ( 40 ), 
    ( 41 ), ( 42 ), ( 43 ), ( 44 ), ( 45 ), ( 46 ), ( 47 ), ( 48 ), ( 49 ), ( 50 ), 
    ( 51 ), ( 52 ), ( 53 ), ( 54 ), ( 55 ), ( 56 ), ( 57 ), ( 58 ), ( 59 ), ( 60 ), 
    ( 61 ), ( 62 ), ( 63 ), ( 64 ), ( 65 ), ( 66 ), ( 67 ), ( 68 ), ( 69 ), ( 70 ), 
    ( 71 ), ( 72 ), ( 73 ), ( 74 ), ( 75 ), ( 76 ), ( 77 ), ( 78 ), ( 79 ), ( 80 ), 
    ( 81 ), ( 82 ), ( 83 ), ( 84 ), ( 85 ), ( 86 ), ( 87 ), ( 88 ), ( 89 ), ( 90 ), 
    ( 91 ), ( 92 ), ( 93 ), ( 94 ), ( 95 ), ( 96 ), ( 97 ), ( 98 ), ( 99 ), ( 100 );

    이제, 우리는 다음과 같은 쿼리를 사용하여 (NUM은 수의 행되는)가 numth를 얻을 :

    SELECT n.num, SUBSTRING_INDEX(SUBSTRING_INDEX(all_tags, ',', num), ',', -1) as one_tag
FROM (
  SELECT
    GROUP_CONCAT(tags SEPARATOR ',') AS all_tags,
    LENGTH(GROUP_CONCAT(tags SEPARATOR ',')) - LENGTH(REPLACE(GROUP_CONCAT(tags SEPARATOR ','), ',', '')) + 1 AS count_tags
  FROM test
) t
JOIN numbers n
ON n.num <= t.count_tags

    보고:

    +-----+---------------------+
| num | one_tag             |
+-----+---------------------+
|   1 | pho                 |
|   2 | pork                |
|   3 | fried-rice          |
|   4 | chicken             |
|   5 | fried-rice          |
|   6 | pork                |
|   7 | chicken-calzone     |
|   8 | chicken             |
|   9 | fettuccine          |
|  10 | chicken             |
|  11 | spaghetti           |
|  12 | chicken             |
|  13 | spaghetti           |
|  14 | chorizo             |
|  15 | spaghetti           |
|  16 | meat-balls          |
|  17 | miso-soup           |
|  18 | chanko-nabe         |
|  19 | chicken-manchurian  |
|  20 | chicken             |
|  21 | manchurain          |
|  22 | pork-manchurian     |
|  23 | pork                |
|  24 | manchurain          |
|  25 | sweet-and-sour-pork |
|  26 | pork                |
|  27 | peking-duck         |
|  28 | duck                |
+-----+---------------------+
28 rows in set (0.01 sec)

    즉시 우리가 지금 고전 행을 가지고, 우리는 쉽게 각 태그의 발생을 셀 수 있습니다.

    요청을보기 위해이 답변의 상단을 참조하십시오.

  2. ==============================

    2.알랭 Tiembo는 아래의 역학을 많이 설명하는 좋은 답을 가지고있다. 그러나 그의 해결책은 문제를 해결하기 위해 임시 테이블 (번호)가 필요합니다. 후속 답변을, 나는 (원래 테이블 TABLENAME를 사용하여) 하나 개의 질의에 그의 모든 단계를 결합하고있다 :

    알랭 Tiembo는 아래의 역학을 많이 설명하는 좋은 답을 가지고있다. 그러나 그의 해결책은 문제를 해결하기 위해 임시 테이블 (번호)가 필요합니다. 후속 답변을, 나는 (원래 테이블 TABLENAME를 사용하여) 하나 개의 질의에 그의 모든 단계를 결합하고있다 :

        SELECT t.tags, count(*) AS occurence FROM
    (SELECT
      tablename.id,
      SUBSTRING_INDEX(SUBSTRING_INDEX(tablename.tags, ',', numbers.n), ',', -1) tags
    FROM
      (SELECT 1 n UNION ALL SELECT 2
       UNION ALL SELECT 3 UNION ALL SELECT 4) numbers INNER JOIN tablename
      ON CHAR_LENGTH(tablename.tags)
         -CHAR_LENGTH(REPLACE(tablename.tags, ',', ''))>=numbers.n-1
    ORDER BY
      id, n) t
    GROUP BY t.tags
    ORDER BY occurence DESC, t.tags ASC

    데모 목적을 위해 SQLFiddle를 참조하십시오.

  3. ==============================

    3.첫째, 당신은이 포스트와 태그 당 하나의 행으로, 접합 테이블을 사용하여 저장해야합니다. 그러나 때때로 우리는 우리가 작업하는 데이터의 구조를 제어 할 수 없습니다.

    첫째, 당신은이 포스트와 태그 당 하나의 행으로, 접합 테이블을 사용하여 저장해야합니다. 그러나 때때로 우리는 우리가 작업하는 데이터의 구조를 제어 할 수 없습니다.

    당신은 당신이 유효한 태그 목록을 가정하고 싶은 것을 할 수 있습니다 :

    select vt.tag, count(t.postid) as cnt
from validtags vt left join
     table t
     on find_in_set(vt.tag, t.tags) > 0
group by vt.tag
order by cnt desc;
  4. ==============================

    4.이 일을 권장 방법은 하나의 열에서 여러 값을 저장하지만, 교차 테이블을 생성하지 않는 것입니다.

    이 일을 권장 방법은 하나의 열에서 여러 값을 저장하지만, 교차 테이블을 생성하지 않는 것입니다.

    그래서, 당신의 테이블은 이러한 열을 것이다 :  1. 태그 : tag_id, 이름  2. 게시물 : post_id를, category_code  3. int_tags_to_posts : post_id를, tag_id

    카운트를 얻으려면 : 태그 t, 소식 피, int_tags_to_posts t.name에서, 수 (*)를 선택할 수 여기서 I (*)에 의해 내림차순 i.tag_id ORDER BY i.post_id = p.post_id 및 i.tag_id = t.tag_id 기;

  5. ==============================

    5.이 작업을해야합니다 :

    이 작업을해야합니다 :

    SELECT tag, count(0) count FROM (
    SELECT tOut.*, REPLACE(SUBSTRING(SUBSTRING_INDEX(tags, ',', ocur_rank), LENGTH(SUBSTRING_INDEX(tags, ',', ocur_rank - 1)) + 1), ',', '') tag
      FROM (
        SELECT @num_type := if(@id_check = tY.id, @num_type + 1, 1) AS ocur_rank, @id_check := tY.id as id_check, tY.*
          FROM (
            SELECT LENGTH(tags) - LENGTH(REPLACE(tags, ',', '')) AS num_ocur, id, tags FROM tablename
          ) tX
          INNER JOIN (SELECT LENGTH(tags) - LENGTH(REPLACE(tags, ',', '')) AS num_ocur, id, tags FROM tablename) tY
          INNER JOIN (SELECT @num_type := 0, @id_check := 'some_id') tZ
       ) tOut
     WHERE ocur_rank <= num_ocur + 1
) tempTable GROUP BY tag ORDER BY count DESC;

    테이블의 이름으로 "TABLENAME"를 교체합니다.

    이 답변이 페이지에 게시 제시 페링에 의해 용액으로부터 파생 된 :

    http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#c12113

    6. from https://stackoverflow.com/questions/26215324/mysql-php-select-count-of-distinct-values-from-comma-separated-data-tags by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] SQL 서버 - 자연의 부족 / X Y가 사용 JOIN (필드) 가입  (0) 2020.05.28
[SQL] 다른 데이터베이스에서 쿼리의 결과에 따라 데이터베이스 쿼리  (0) 2020.05.28
[SQL] SQL Server 2008의 리드 지연 기능의 대체  (0) 2020.05.28
[SQL] SQL에서 HEX에 문자열 변환  (0) 2020.05.27
[SQL] 어떻게 SSIS에서 동적 입력 및 출력 열을 매핑하는?  (0) 2020.05.27
,

티스토리툴바